Extracting results of table to Excel after using the command summarize

[FitzGerald Blindman]

Hello everyone!

I know it might be a beginners question - but I haven't found a way by myself... I Just want to command Stata to extract the results of the command "summarize" on some variables to Excel table. Until now I had to copy and paste by hand and of course must be a quicker way.

Hope you will be able to help me

FitzGerald

[Emil Alnor]

When you use the command 'summarize' the results are stored in r(). See 'Stored results' at the bottom of 'help summarize'.

Asumming you are interested in exporting the means of var1, var2 and var3 to an excel-sheet this is what you could do:

putexcel set "your_excel_file"

local k = 1
foreach x in var1 var2 var3{
   sum `x'
   putexcel a`k' = `r(mean)'
   local k = `k' + 1

[Jeff Pitblado (StataCorp)]

There is also the new dtable command in Stata 18, see [R] dtable.

[FitzGerald Blindman]

There is also the new dtable command in Stata 18, see [R] dtable.

Thank you, but my Uni still didn't bought Stata 18...

[FitzGerald Blindman]

When you use the command 'summarize' the results are stored in r(). See 'Stored results' at the bottom of 'help summarize'.

Asumming you are interested in exporting the means of var1, var2 and var3 to an excel-sheet this is what you could do:

putexcel set "your_excel_file"

local k = 1
foreach x in var1 var2 var3{
sum `x'
putexcel a`k' = `r(mean)'
local k = `k' + 1

Thank you!

It indeed worked, but it just created to me a column with the means, How can I make it also add the Std. Dev the number of observations, the max and the min, and also use the variables names and the statistics names?

Fitz

[Emil Alnor]

FitzGerald Blindman See 'Stored results' at the bottom of 'help summarize'. replace 'mean' in `r(mean)' with the statistic you want. With regard to variable names add a

putexcel b`k' = "`x'"

. You can also use this method to give names to columns (mean, std.dev, and so on)

[Jeff Pitblado (StataCorp)]

Emil gives simple (easy to follow) examples using a Stata loop and
putexcel.

If you have Stata 17, you can also do this using the re-worked
table command. There is also dtable if you have
Stata 18.

The following uses table with the auto data. If you are already
working with putexcel, you can use it with collections instead of
calling collect export, search for collect in the
help/documentation for putexcel.

sysuse auto, clear

table (var) (result), ///
    statistic(mean mpg turn trunk displ) ///
    statistic(sd mpg turn trunk displ) ///
    nformat(%9.2f)

collect export mytable.xlsx, replace

Here is the resulting table

-----------------------------------------------------
                       |    Mean   Standard deviation
-----------------------+-----------------------------
Mileage (mpg)          |   21.30                 5.79
Turn circle (ft.)      |   39.65                 4.40
Trunk space (cu. ft.)  |   13.76                 4.28
Displacement (cu. in.) |  197.30                91.84
-----------------------------------------------------

Here is a screen shot taken from LibreOffice on my Mac.

[Jeff Pitblado (StataCorp)]

Here is the above reworked to show the same statistics as summarize.

sysuse auto, clear

table (var) (result), ///
    statistic(count mpg turn trunk displ) ///
    statistic(mean mpg turn trunk displ) ///
    statistic(sd mpg turn trunk displ) ///
    statistic(min mpg turn trunk displ) ///
    statistic(max mpg turn trunk displ) ///
    nformat(%9.2f mean sd)

* use -summarize- labels
collect label levels result ///
    count "Obs" ///
    sd "Std. dev." ///
    min "Min" ///
    max "Max" ///
    , modify
collect preview

collect export mytable.xlsx, replace

Here is the resulting table

. collect preview

--------------------------------------------------------------
                       |  Obs     Mean   Std. dev.   Min   Max
-----------------------+--------------------------------------
Mileage (mpg)          |   74    21.30        5.79    12    41
Turn circle (ft.)      |   74    39.65        4.40    31    51
Trunk space (cu. ft.)  |   74    13.76        4.28     5    23
Displacement (cu. in.) |   74   197.30       91.84    79   425
--------------------------------------------------------------