Exporting summary statistics by year/industry to Word/Excel

[Ralph Klijn]

Hello everyone,

I have a dataset that is ordened by year and industry. I would like to create some summary statistics in Excel/Word for my variables (mean and standard deviation) by year and industry. However, I seem unable to find a tool that does this.

Normally I use -outreg2-. Does anyone know whether I can use outreg2 export summary statistics by year? Or anyone has another tool that I can use?

Thank you in advance.

[William Lisowski]

Given the limited description of your problem, the lack of sample data, and the assumption (since you don't indicate otherwise) that you are working with Stata 15, it is possible that you can use collapse to produce a dataset of summary statistics by year and industry, and then use export excel to output that dataset to an Excel worksheet.

[Attaullah Shah]

You can use asdoc for creating a table of summary statistics, correlations, tabulations, regressions, and with many more Stata commands. Here are three examples to make summary statistics using year as a grouping variable.


1. One Statistics, many variables using year as the grouping variable

* Install asdoc
ssc install asdoc

* Download example data set
webuse grunfeld, clear

* Find mean for invest, kstock, and mvalue
bys year: asdoc tabstat invest mvalue kstock,  replace stat(mean)

* Similarly, if you want to find the standard deviation, then
bys year: asdoc tabstat invest mvalue kstock,  stat(sd) replace

2. Find many statistics for one variable over a grouping variable

To find many statistics, we shall add them to the option stat(). The following commands will report the number of obs. mean, standard deviation, min, max, median, kurtosis, and skewness for the variable invest using year as a grouping variable

bys year: asdoc tabstat invest ,  replace stat(N mean sd min max p50 kurtosis skewness)

3. Find many statistics for many variables over a grouping variable

To find the number of obs. mean, standard deviation, min, max, median, and t-statistics for variables invest, kstock, and mvalue using year as a grouping variable

bys year: asdoc tabstat invest kstock mvalue, replace stat(N mean sd min max p50 tstat)

* Please note there is a program bug that would not allow multiple statistics unless you use the tstat inside the stat() option. I shall correct this in the new version of asdoc.

[Arne Kristian Aas]

You can store the summary statistics as a dataset, then make the dataset into a matrix, and then export the matrix to Excel using putexcel. It is easier than it sounds:

putexcel set yourfile, replace

* Two alternatives
* 1) table, replace // replaces data in memory with the output from the table command

sysuse census, clear

preserve
table region, c(count pop sum popurban mean death) replace
mkmat region table*, matrix(tab)
matrix list tab

putexcel a4=matrix(tab)
restore

* 2) collapse

sysuse census, clear

preserve
collapse (count) pop (sum) popurban (mean) death, by(region)
mkmat region pop popurban death, matrix(tab2)
matrix list tab

putexcel f4=matrix(tab)
restore

[Yilmaz Yildirim]

Dear Attaullah if we add a country variable to your first example. how can we use your formula at country and year level? (columns represent years rows represent countries)

Thank you.

[Attaullah Shah]

Well, some manual tweaking would be needed.

webuse grunfeld

* Create some dummy data for the example
clear
gen country = "Pakistan"
expand 2
bys year: replace country = "US" if _n == 1

* Estimate statistics for the first country
bys year: asdoc tabstat invest mvalue kstock if country == "Pakistan",  replace stat(mean) title(Pakistan)

* Write the results to a matrix
mat A = C

* For the second country
bys year: asdoc tabstat invest mvalue kstock if country == "US",   stat(mean) title(US)

* Combine the two matrices
mat A = A,C

* Write the combined table
asdoc wmat, mat(A) replace

[Yilmaz Yildirim]

Great thank u Professor. Another question, how to do the table in the example. Thank u again.

[Attaullah Shah]

Seems to me you can use the table command for this. In the following example, I am reporting mean of the invest variable and report it by company and year. In your case, the company can be considered as a country.

webuse grunfeld
asdoc table company year, contents(mean invest ) replace

[Ayub UOM]

Attaullah Shah sir AOA,
sir thank you for your nice sharing of knowledge.i have just two questions, sir in #3 method #2 yearsise statistics,it display total in stata, but when i outreg it in word it doesn't show column total in table,sir how can i get total (N mean sd) etc column wise.
(2) sir can we calculate percentage of row total and column total ? let suppose i want to calculate row and column total of industry ,year distribution, as well as total perecentage year and industry wise of firm year observation,or i will calculate total and percentage manually?
Ayub

[Attaullah Shah]

To report more statistics, just add them to the stat() option

For the first query

* Download example data set
webuse grunfeld, clear

* Find mean for invest, kstock, and mvalue
bys year: asdoc tabstat invest mvalue kstock,  replace stat(N mean sd )

For the second query

webuse grunfeld, clear
keep if year <1937
 asdoc table company year , c(mean invest) row col replace

[Ayub UOM]

Thank you sir,
sir ijust want to request that the send commond is not according to what i expect,because one of my industry is not properly presented in word table,because in this industry there are more firm per year so it appers in form 1.1e+03 in stata and actually it is (1,130 when i am using tabulate industry year),therefore it is missing in word table, and second i want to get a sample distribution by year and industry table such like

tabulate industry year

but i need one extra column and row for % (rows total/Total firm year observation) and (column total/total firm year observation),and then to outreg it.
sir could you please guide me,how can i do it.