Store regression coefficients from looping over multiple y variables

[Sebastian Sun]

Hi everyone, I am new to the forum and hoping to get some help on storing regression results.

I would like to store the regression coefficients and standard errors (_b and _se) from regressing multiple y variables on the same x variable.

For example, after generating 5 lags for the variable "change" named L1 - L5:

sysuse sp500, clear
foreach t of numlist 1/5 {
gen L`t' = change[_n-`t']
}

I would like to regress each lag on the same variable, and store the 5 b's and 5 se's (so that b1 is from "reg L1 volume", b2 is from "reg L2 volume" and so on).

foreach t of numlist 1/5 {
reg L`t' volume
}

I've looked into -statsby- and generating b=. and then replacing it as suggested here and here, but can't quite apply them to what I am trying to do. Any thoughts or suggestions are greatly appreciated!

[Clyde Schechter]

No, -statsby- won't help you here: that's for a series of regressions (or other commands) over values of a variable or group of variables, not over different variables.

forvalues t = 1/5 {
    regress L`t' volulme
    gen b`t' = _b[volume]
    gen se`t' = _se[volume]
}

That said, it is usually not helpful to create variables for the purpose of holding constants. The "variables" b* and se* created by this code will contain the same number in every observation, namely the coefficient of volume and its standard error in the corresponding regression. I don't know what you intend to do with those once you have them, but for most purposes it would be a better idea to store them either as scalars or as local macros:

forvalues i = 1/5 {
     regress L`i' volume
     local b`i' = _b[volume]
     local se`i' = _se[volume]

    // OR

    scalar b`i' = _b[volume]
    scalar se`i' = _se[volume]
}

[Sebastian Sun]

Thanks a lot!! You are right that I only need the scalar value of the coefficients.

In case there is future interest, based on Clyde's suggestion, I completed the code by exporting the regression coefficients and standard errors into Excel.

putexcel A1=("b") B1=("se") using "results.xlsx", replace
forvalues i = 1/5 {
    regress L`i' volume
    scalar b`i' = _b[volume]
    scalar se`i' = _se[volume]
    
    local row = `i'+1
    putexcel A`row'=(b`t') B`row'=(se`t') using "results.xlsx", modify
}

[Attaullah Shah]

Version 2.0 of asdoc has a new option wide to report regression results in wide format. Specifically, wide format is great for regressions where the independent variables remain the same in several regression models. The example provided by Sebastian Sun aligns well with option wide. So

* Install asdoc from SSC
ssc install asdoc, replace

* See help file
help asdoc

* Send the regression output to MS Word / RTF file in wide format
foreach t of numlist 1/5 {
asdoc reg L`t' volume, wide se(below)
}

The standard errors can also be reported side-ways. So

* Send the regression output to MS Word / RTF file in wide format
foreach t of numlist 1/5 {
asdoc reg L`t' volume, wide se(side)

[Anne Dam]

This is amazing! Thanks for sharing the final code Sebatian.

Is there a way to ALSO get e(fd_m), e(df_r), e(F), and e(r2) into the same excel file?

It works for me (stata 14.2) with B and SEs if modified as shown below, but I can't figure out how to also get the dfs, F and R2 to columns C-F.

Thanks a lot!! You are right that I only need the scalar value of the coefficients.

In case there is future interest, based on Clyde's suggestion, I completed the code by exporting the regression coefficients and standard errors into Excel.

putexcel set test_putexcel, replace
putexcel A1=("b") B1=("se")

forvalues i = 1/14 {
regress t_met burnout if conc==`i' & KP_09==1

scalar b`i' = _b[burnout]
scalar se`i' = _se[burnout]

local row = `i'+1
putexcel A`row'=(b`t') B`row'=(se`t')
}

Thanks a lot

[Anne Dam]

Okay, I figured it out, nevermind my question

putexcel set "Pathname/goes/here/test", sheet("test2") modify
putexcel A1=("b") B1=("se") C1=("df1") D1=("df2") E1=("F") F1=("R2") G1=("N")
            forvalues i = 1/14 {
                regress        t_met burnout if conc==`i' & KP_09==1 & BIO_09==1
                scalar a`i' = _b[burnout]
                scalar b`i' = _se[burnout]
                scalar c`i' = e(df_m)
                scalar d`i' = e(df_r)
                scalar e`i' = e(F)
                scalar f`i' = e(r2)
                scalar g`i' = e(N)
                local row = `i'+1
                putexcel A`row'=(a`i') B`row'=(b`i') C`row'=(c`i') D`row'=(d`i') E`row'=(e`i') F`row'=(f`i') G`row'=(g`i')
                }

This is SUCH a nice feature!