How to export multiple summarise results into same excel spreadsheet?

[Titir Bhattacharya]

Hi statalist,

I'm trying to export multiple results of summarise into the same excel spreadsheet.
The codes I'm using are

estpost sum health if round==2
esttab using "$sum\3_descriptives_2.csv",cells("mean(fmt(2)) sd(fmt(2)) sum(fmt(2))")

estpost sum health if round==3&hi==1
estpost sum health if round==4&hi==1
estpost sum health if round==5&hi==1

estpost sum health if round==3&hi==0
estpost sum health if round==4&hi==0
estpost sum health if round==5&hi==0

But when I try esttab after the first time, I get an error that the file already exists.

I'm trying to make the data look something like this

variable	mean	sd	sum	hi	round
health	some value	some value	some value		2
health	some value	some value	some value	1	3
health	some value	some value	some value	1	4
health	some value	some value	some value	1	5
health	some value	some value	some value	0	3
health	some value	some value	some value	0	4
health	some value	some value	some value	o	5

Is it possible to obtain this kind of structure with some code or it can only be done manually?
Please help!

[Yonah Drazen]

Hello Titir,
To resolve the error that the file already exists, add the replace option to your esttab line:

esttab using "$sum\3_descriptives_2.csv",cells("mean(fmt(2) ) sd(fmt(2)) sum(fmt(2))") replace

[Titir Bhattacharya]

Hello Titir,
To resolve the error that the file already exists, add the replace option to your esttab line:

esttab using "$sum\3_descriptives_2.csv",cells("mean(fmt(2) ) sd(fmt(2)) sum(fmt(2))") replace

Thanks Yonah. But I think this won't let me put hi and round along with summary stats? In particular, I'm trying to make the output look something like the table given in the question. Is it possible to achieve that kind of format with some code?

[Yonah Drazen]

If you want to be really granular about where all the columns and rows end up, you can use putexcel with a loop, something like:

putexcel set filename.xls, replace
local row 2
forval i = 0/1 {
    forval j = 2/5 {
        sum health if round == `j' & hi == `i'
        putexcel A`row' = "health"
        putexcel B`row' = `r(mean)'
        putexcel B`row' = `r(sd)'
        putexcel C`row' = "`i'"
        putexcel D`row' = "`j'"
        

local row = `row' + 1
    }
}
putexcel save

For something faster, you could check out the SSC program asdoc, which produces tables quite easily.

[Attaullah Shah]

Adding to Yonah suggestion, you can also consider asdocx, that exports all types of tables to Excel, Word, LaTeX, and HTML. One example is here. See the output file attached to this post.

sysuse auto, clear
asdocx sum, replace save(myfile.xlsx)
asdocx sum, detail
asdocx cor

[Titir Bhattacharya]

If you want to be really granular about where all the columns and rows end up, you can use putexcel with a loop, something like:

putexcel set filename.xls, replace
local row 2
forval i = 0/1 {
forval j = 2/5 {
sum health if round == `j' & hi == `i'
putexcel A`row' = "health"
putexcel B`row' = `r(mean)'
putexcel B`row' = `r(sd)'
putexcel C`row' = "`i'"
putexcel D`row' = "`j'"


local row = `row' + 1
}
}
putexcel save

For something faster, you could check out the SSC program asdoc, which produces tables quite easily.

Thanks Yonah. This seemed to work. But at the end, I'm getting the following error:

. putexcel save
SAVE: invalid cell name
r(198);

Could you suggest what might be going on here? I modified your code slightly to this

putexcel set "$sum\descriptives.xls", replace
local row 2

    forval j = 3/5 {
        sum good_health if round == `j' & hi == 1
        putexcel A`row' = "health"
        putexcel B`row' = `r(mean)'
        putexcel C`row' = `r(sd)'
        putexcel D`row' = "`j'"
        

local row = `row' + 1
    }
.
putexcel save

Also, is there any way to put the column headings based on the variable name? This code presents the means and SDs and round numbers in columns but doesn't give column headings.

[Titir Bhattacharya]

Adding to Yonah suggestion, you can also consider asdocx, that exports all types of tables to Excel, Word, LaTeX, and HTML. One example is here. See the output file attached to this post.

sysuse auto, clear
asdocx sum, replace save(myfile.xlsx)
asdocx sum, detail
asdocx cor

Thanks Attaullah.. this will be helpful

[Titir Bhattacharya]

If you want to be really granular about where all the columns and rows end up, you can use putexcel with a loop, something like:

putexcel set filename.xls, replace
local row 2
forval i = 0/1 {
forval j = 2/5 {
sum health if round == `j' & hi == `i'
putexcel A`row' = "health"
putexcel B`row' = `r(mean)'
putexcel B`row' = `r(sd)'
putexcel C`row' = "`i'"
putexcel D`row' = "`j'"


local row = `row' + 1
}
}
putexcel save

For something faster, you could check out the SSC program asdoc, which produces tables quite easily.

Also, any way to put multiple sum results on the same sheet? I repeated the code for another variable and now the results on "health" are gone. I tried with append and it says the option is not allowed.