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).
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