Extract summary statistic to Excel file

[FitzGerald Blindman]

Hey everyone, I'm sorry I'm asking such a stupid question.

I'm looking for the simplest way to extract to excel the results of summary statistics that I found by the command "sum", I want that it will be as similar as possible to the way STATA present when it print the results.

Thank you very much.

Fitz

[Andrew Musau]

The highlighted do not change in your modification of the code below.

sysuse auto, clear
frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max)
foreach var in mpg weight trunk disp turn{
    sum `var'
    frame post sumstats ("`var'") (r(N)) (r(mean)) (r(sd)) (r(min)) (r(max))
}
frame sumstats: export excel using myfile, replace firstrow(var) keepcellfmt
frame drop sumstats

Res.:

[FitzGerald Blindman]

The highlighted do not change in your modification of the code below.

sysuse auto, clear
frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max)
foreach var in mpg weight trunk disp turn{
sum `var'
frame post sumstats ("`var'") (r(N)) (r(mean)) (r(sd)) (r(min)) (r(max))
}
frame sumstats: export excel using myfile, replace firstrow(var) keepcellfmt
frame drop sumstats

Res.:

[ATTACH=CONFIG]n1764362[/ATTACH]

Hey Andrew.

Thank you, first of all. I'm not sure I understand what are the blue-font lines and in what are they different from the black-font lines.
Can you please explain it to me?

Sorry for bothering you with this questions..

Thank you, again

Fitzgerald

[Andrew Musau]

The blue font lines specify the structure of the table produced by summarize. I create such a table in a separate frame and use export excel to transfer this to Excel. The black font lines load a Stata dataset and summarize a list of variables. So the exported table would correspond to the one below.

sysuse auto
summarize mpg weight trunk disp turn

Res.:

. summarize mpg weight trunk disp turn

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
         mpg |         74     21.2973    5.785503         12         41
      weight |         74    3019.459    777.1936       1760       4840
       trunk |         74    13.75676    4.277404          5         23
displacement |         74    197.2973    91.83722         79        425
        turn |         74    39.64865    4.399354         31         51

The documentation should provide a more detailed explanation of the commands that I have used. See

help frames
help export excel

[FitzGerald Blindman]

The blue font lines specify the structure of the table produced by summarize. I create such a table in a separate frame and use export excel to transfer this to Excel. The black font lines load a Stata dataset and summarize a list of variables. So the exported table would correspond to the one below.

sysuse auto
summarize mpg weight trunk disp turn

Res.:

. summarize mpg weight trunk disp turn

Variable | Obs Mean Std. dev. Min Max
-------------+---------------------------------------------------------
mpg | 74 21.2973 5.785503 12 41
weight | 74 3019.459 777.1936 1760 4840
trunk | 74 13.75676 4.277404 5 23
displacement | 74 197.2973 91.83722 79 425
turn | 74 39.64865 4.399354 31 51

The documentation should provide a more detailed explanation of the commands that I have used. See

help frames
help export excel

Wow, Thank you Andrew it worked perfectly!

If I want to create another table, but for other variables and that it will appear in the same file, next to the former table - how can I do that?
And how to I put another table in the same file but in different sheet?

Thanks,

Fitzgerald

[Andrew Musau]

You will see a number of options in the export excel documentation.

help export excel

export_excel_options Description
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Main
sheet("sheetname"[, modify|replace]) save to Excel worksheet
cell(start) start (upper-left) cell in Excel to begin saving to
firstrow(variables|varlabels) save variable names or variable labels to first row
nolabel export values instead of value labels
keepcellfmt when writing data, preserve the cell style and format of existing worksheet
replace overwrite Excel file

Advanced
datestring("datetime_format") save dates as strings with a datetime_format
missing("repval") save missing values as repval

locale("locale") specify the locale used by the workbook; has no effect on Microsoft Windows

So you can specify the cell to include additional tables within the same sheet using the -cell()- option. The -sheet()- option allows you to specify different worksheets.

[FitzGerald Blindman]

You will see a number of options in the export excel documentation.

help export excel

So you can specify the cell to include additional tables within the same sheet using the -cell()- option. The -sheet()- option allows you to specify different worksheets.

Sorry for disturbing you again Andrew, but I've got some troubles with the syntax:

1. I tried to put the new table in another sheet but I failed with the syntax - I tried

frame sumstats: export excel using "XYZ", replace firstrow(var) keepcellfmt sheet("ABC", [modify])l

and STATA told me "invalid argument in option sheet()", so I tried to change the comma and the square parenthesis but nothing worked.

2. When I try just to give a new name to the sheet when I ran the second table, it changed the name of the former sheet and just append the new variables on the older ones, which is exactly what I try to avoid from, otherwise i would just do it in the same table from the beginning.

3. When I tun the next tables, should I use

frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max

again everytime? and to use

frame drop sumstats

as well after every loop that creates new table?

4. I tried

frame sumstats: export excel using "XYZ", replace firstrow(var) keepcellfmt cell(H1)l

to put the new table next to the former one, but again, it just created one new big table in H1. What shall I do now?

Thank you so much for helping me.

Fitz

[Andrew Musau]

sysuse auto, clear
frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max)
foreach var in mpg weight trunk disp turn{
    sum `var'
    frame post sumstats ("`var'") (r(N)) (r(mean)) (r(sd)) (r(min)) (r(max))
}
frame sumstats: export excel using myfile, replace firstrow(var) sheet(results) keepcellfmt
frame drop sumstats

frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max)
foreach var in mpg weight trunk disp turn{
    sum `var'
    frame post sumstats ("`var'") (r(N)) (r(mean)) (r(sd)) (r(min)) (r(max))
}
frame sumstats: export excel using myfile, cell(H1) sheet(results, modify)  firstrow(var) keepcellfmt
frame drop sumstats

produces:

[FitzGerald Blindman]

sysuse auto, clear
frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max)
foreach var in mpg weight trunk disp turn{
sum `var'
frame post sumstats ("`var'") (r(N)) (r(mean)) (r(sd)) (r(min)) (r(max))
}
frame sumstats: export excel using myfile, replace firstrow(var) sheet(results) keepcellfmt
frame drop sumstats

frame create sumstats str38(Variable) float(Obs) double(Mean SD Min Max)
foreach var in mpg weight trunk disp turn{
sum `var'
frame post sumstats ("`var'") (r(N)) (r(mean)) (r(sd)) (r(min)) (r(max))
}
frame sumstats: export excel using myfile, cell(H1) sheet(results, modify) firstrow(var) keepcellfmt
frame drop sumstats

produces:

[ATTACH=CONFIG]n1764398[/ATTACH]

Andrew thank you so much! it worked perfectly.

This is the first time I get such useful help in this forum. You are wonderful.

Fitz