Export results of various regressions to one Excel file

[FitzGerald Blindman]

Hi everyone,

I use the next commands to run regression of every variable in the global list of $questionnaire on every variable in the global list of $measures.

global questionnaire locality_q25_5_6_share locality_q26_5_6_share locality_q41_5_6_share locality_q42_5_6_share locality_q43_5_6_share locality_q44_5_6_share locality_q45_5_6_share locality_q40_1_2_share locality_mean_share_5_6 lclty_mean_q25_26_43_44share_5_6

global measures hhi_school_zip X_1_zip_share_unique school_family_name_homogeneity X_4_family hhi_elementary_school_2

foreach i in $questionnaire {
    foreach j in $measures {
        reg `i' `j' locality_mean_fathers_education locality_mean_mothers_education
    }
}

Now I want to export to result to Excel in the next way: Every variable in $questionnaire will be a column and every variable in $measures will be a row, so that the estimators of all the regressions will appear in one Excel table (I don't need the coefficients of "mean_fathers_education" and "locality_mean_mothers_education"). Of curse that I wan the standard errors to appear in parenthesis in a row below the estimators, and asterisks to represent the significance level.

What Should I add to my code?

Thank you!

Fitz

[Hemanshu Kumar]

Assuming you are on Stata 17 or up, the following example should work.

sysuse auto, clear
collect clear

global questionnaire price mpg headroom
global measures trunk weight
global others turn length

foreach i in $questionnaire {
    foreach j in $measures {
        collect: reg `i' `j' $others
    }
}

collect stars _r_p .01 "***" .05 "**" .1 "*", attach(_r_b) shownote dimension
collect style cell stars[label], halign(left)
collect style column, dups(center)

collect style cell result[_r_b _r_se], nformat(%7.3f)
collect style cell result[_r_se], sformat("(%s)")
collect style header result , level(hide)
collect layout (colname[$measures]#result[_r_b _r_se]) (coleq#stars)

which produces:

. collect preview

------------------------------------------------------------------
                      |     Price     Mileage (mpg) Headroom (in.)
----------------------+-------------------------------------------
Trunk space (cu. ft.) |    -9.109        -0.019         0.120 *** 
                      | (107.447)       (0.142)       (0.026)     
Weight (lbs.)         |     5.382 ***    -0.004 **     -0.000     
                      |   (1.117)       (0.002)       (0.000)     
------------------------------------------------------------------
*** p<.01, ** p<.05, * p<.1

You can then use the collect export command to send this to an Excel file.

[FitzGerald Blindman]

Assuming you are on Stata 17 or up, the following example should work.

sysuse auto, clear
collect clear

global questionnaire price mpg headroom
global measures trunk weight
global others turn length

foreach i in $questionnaire {
foreach j in $measures {
collect: reg `i' `j' $others
}
}

collect stars _r_p .01 "***" .05 "**" .1 "*", attach(_r_b) shownote dimension
collect style cell stars[label], halign(left)
collect style column, dups(center)

collect style cell result[_r_b _r_se], nformat(%7.3f)
collect style cell result[_r_se], sformat("(%s)")
collect style header result , level(hide)
collect layout (colname[$measures]#result[_r_b _r_se]) (coleq#stars)

which produces:

. collect preview

------------------------------------------------------------------
| Price Mileage (mpg) Headroom (in.)
----------------------+-------------------------------------------
Trunk space (cu. ft.) | -9.109 -0.019 0.120 ***
| (107.447) (0.142) (0.026)
Weight (lbs.) | 5.382 *** -0.004 ** -0.000
| (1.117) (0.002) (0.000)
------------------------------------------------------------------
*** p<.01, ** p<.05, * p<.1

You can then use the collect export command to send this to an Excel file.

Thank you,

But I don't know the collect export commands.

Fitzgerald

[Hemanshu Kumar]

See

help collect export

[Seoul NY]

try tabler-4jb.pages.dev . I used it for exporting stata regression table to ms word doc. if it works for word, should work for excel as well.

[Simon Pfaff]

Fitzgerald, you could also use putexcel to generate an excel file containing your results.
Here is a short addition/example how to do this.

* insert the tables using putexcel
************************************
putexcel set result.xlsx, sheet("1") replace
putexcel A1 = collect, name(default)

* there are a lot of possibilities to format the tables with putexcel
* you can also use xl() to change the excel file

* change col width
************************************
mata: b = xl()
mata: b.load_book("result.xlsx")
mata: b.set_sheet("1")
mata: b.set_column_width(1,1,30)