How to export multiple set of regressions into one Excel workbook using either outreg2 or esttab?

Question

I have multiple sets of regressions that need to be presented in different tables. I wonder if there is a way to export multiple set of regressions into one Excel workbook using either outreg2 or esttab or some other package?

For example, I run 100 regressions using esttab; then I want to present them in 25 different tables with four regressions in each table. The following format of code allows me to export to 25 different csv files:

esttab using "$output\output1.csv", se stats(N ymean r2_a) replace

However, I want to have all the 25 tables in one workbook with 25 tabs. It is possible to copy-paste the tables if the number of output files is not big, but that's not the case for me.

Gal

Is there a reason for wanting (e.g.) 25 different tabs with 4 regressions in each tab, rather than a single tab with 100 regressions? Have you looked into the ctitle() option of outreg2 to differentiate your models programmatically? Putting this aside, so far as I know, outreg2 does not allow one to specify the sheet name(s) in the Excel output file directly (as outreg2 seems to be based on outsheet). However, it is likely possible with the dta option and Stata's newer export excel command. – Sonja

@brendan Because I want to create 25 separate tables. It would be easy to do so if I can export them into 25 tabs, because then I can use an excel macro to format the tables in the way I want. If there is a way in stata that can create 25 tabs, that will be much easier for me to create tables. – Gal

Fair enough. See the answer below for outreg2. (I haven't used esttab.) – Sonja

Answer 1

With outreg2, you'll need to use the dta option to save the results as individual datasets, and then use the export excel command to export each dataset to an individual sheet in the same tab. E.g.:

clear all
sysuse auto

regress price mpg
outreg2 using "price" , replace dta

regress price mpg headroom
outreg2 using "price" , dta

regress mpg weight length
outreg2 using "mpg" , replace dta

regress mpg weight length foreign
outreg2 using "mpg" , dta

use price_dta
export excel using "results" , sheet("price")

use mpg_dta
export excel using "results" , sheet("mpg")

Obviously running this as a loop will make more sense, and you may want to add the replace option to the first use of outreg2. Further use of outreg2's options will help clean up the output further.

Sonja

Answer 2

regsave might be another alternative (info here and here)

You could easily combine four regressions into one dta file and then export them to xls.

Some rudimentary code, borrowing from help file, to get you up and running:

sysuse auto.dta, clear

regress price mpg trunk headroom length
regsave mpg trunk using results, table(OLS_stderr, order(regvars r2)) replace

regress price mpg trunk headroom length, robust
regsave mpg trunk using results, table(Robust_stderr, order(regvars r2)) append

use results, clear

outsheet using table.txt, replace
export excel using "excell", firstrow(variables) replace

If you want to manipulate the Excel output even more, you might also try to harness the power of xml_tab package (Check here and here)

Ulna