Export regression results with categorical exposure variables

Question

I would like to run several linear regressions using categorical exposure variables and to output the results to an excel sheet.

The code below works fine when the exposure is continuous. However, for categorical exposures the code only outputs the first row of results rather for ever level of the exposure.

*Code which works for continuous exposures

sysuse auto.dta
describe 
summ

postfile temp str40 exp str40 outcome adjusted N beta se lci uci pval using ///
"\test.dta", replace

foreach out in price weight {
    foreach exp in i.rep78 {
        foreach adjust in 1 {

            if `adjust'==1 local adjusted "mpg" 

            xi: reg `out' `exp' `adjusted'  
            local N = e(N) 
            matrix table=r(table)
            local beta = table[1,1]
            local se = table[2,1]
            local lci = table[5,1]
            local uci = table[6,1]
            local pval=table[4,1]
            post temp ("`out'") ("`exp'") (`adjusted') (`N') (`beta') (`se') /// 
                      (`lci') (`uci') (`pval')
        }
    }
}

postclose temp 

use "\test.dta", clear
export excel using "\test.xlsx", firstrow(variables)

The above code only produces one row with estimates for the first level of rep78 when it should produce 4 rows (rep78 is a 5-level categorical variable).

Please note: 1. It's not standard here on SO to put software names in titles That is the role of tags. 2. STATA is incorrect spelling in any case, one reason why it was edited out earlier. Spelling has been Stata for >30 years. 3. Personal notes such as "Thank you" are not needed. Strange though it may seem, it is more polite here to ask a direct technical question without extra personal remarks. I don't want to pull rank, except that it is fair to ask that you consider that people with greater experience know more about what is customary on Stack Exchange. — Nick Cox, May 18 '18 at 17:10

score 2 · Accepted Answer · 2018-05-18T11:07:38.037

You need to modify your code to save the results from all the relevant columns of r(table):

. reg price i.rep78

. matrix list r(table)

r(table)[9,6]
                1b.          2.          3.          4.          5.            
             rep78       rep78       rep78       rep78       rep78       _cons
     b           0    1403.125   1864.7333        1507      1348.5      4564.5
    se           .   2356.0851   2176.4582   2221.3383   2290.9272   2107.3466
     t           .    .5955324   .85677426   .67841985   .58862629    2.165994
pvalue           .   .55358783   .39476643   .49995129   .55818378   .03404352
    ll           .   -3303.696  -2483.2417  -2930.6334  -3228.1533    354.5913
    ul           .    6109.946   6212.7083   5944.6334   5925.1533   8774.4087
    df          64          64          64          64          64          64
  crit   1.9977297   1.9977297   1.9977297   1.9977297   1.9977297   1.9977297
 eform           0           0           0           0           0           0

So, in your code, after matrix table=r(table) you need to have something like:

forvalues i = 1 / `= colsof(r(table)) - 1' {    

    local beta = table[1,`i']
    local se = table[2,`i']
    local lci = table[5,`i']
    local uci = table[6,`i']
    local pval=table[4,`i']

    post temp ("`out'") ("`exp'") (`adjusted') (`N') (`beta') (`se') ///
              (`lci') (`uci') (`pval')

}

The following works for me:

sysuse auto.dta, clear
describe 
summ

postfile temp str40 exp str40 outcome adjusted N beta se lci uci pval using ///
"test.dta", replace

foreach out in price weight {
    foreach exp in i.rep78 {
        foreach adjust in 1 {

            if `adjust'==1 local adjusted "mpg" 

            reg `out' `exp' `adjusted' 

            local N = e(N) 
            matrix table=r(table)

            forvalues i = 1 / `= colsof(r(table))-1' {

                local beta = table[1,`i']
                local se = table[2,`i']
                local lci = table[5,`i']
                local uci = table[6,`i']
                local pval=table[4,`i']                         

                post temp ("`out'") ("`exp'") (`adjusted') (`N') (`beta') ///
                          (`se') (`lci') (`uci') (`pval')

            }                           
        }
    }
}

postclose temp 

use "test.dta", clear

I had a typo, just corrected it. You need `forvalues` instead of `for`. — , May 18 '18 at 10:58

Export regression results with categorical exposure variables

1 Answers1