I would like to run linear regressions using a categorical exposure variable and to output the results to an excel sheet with the names of each covariate included next to their results.
The Stata code below works fine to export the results:
sysuse auto.dta, clear
describe
summ
postfile temp str40 exp str40 outcome adjust N beta se lci uci pval using ///
"test.dta", replace
foreach out in price {
foreach exp in i.foreign {
foreach adjust in 1 2 {
if `adjust'==1 local adjusted ""
if `adjust'==2 local adjusted "mpg weight length displacement i.trunk"
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 ("`exp'") ("`out'") (`adjust') (`N') (`beta') ///
(`se') (`lci') (`uci') (`pval')
}
}
}
}
postclose temp
use "test.dta", clear
However, all the rows are labelled as i.foreign and so it is difficult to know which results correspond to the other covariates.
Ideally i would like a a column with rows showing the names corresponding to the results i.e. mpg, weight, length, displacement, i.trunk
If you want to know the matrix column name that corresponds to each beta:
sysuse auto.dta, clear
describe
summ
postfile temp str100 exp str40 outcome adjust N beta se lci uci pval using ///
"test.dta", replace
foreach out in price {
foreach exp in i.foreign {
foreach adjust in 1 2 {
if `adjust'==1 local adjusted ""
if `adjust'==2 local adjusted "mpg weight length displacement i.trunk"
reg `out' `exp' `adjusted'
local N = e(N)
matrix table=r(table)
local matnames: colnames table
tokenize `matnames'
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 ("``i''") ("`out'") (`adjust') (`N') (`beta') ///
(`se') (`lci') (`uci') (`pval')
}
}
}
}
postclose temp
use "test.dta", clear
list exp outcome adjust N beta, separator(0)
+--------------------------------------------------+
| exp outcome adjust N beta |
|--------------------------------------------------|
1. | 0b.foreign price 1 74 0 |
2. | 1.foreign price 1 74 312.2587 |
3. | 0b.foreign price 2 74 0 |
4. | 1.foreign price 2 74 3152.553 |
5. | mpg price 2 74 -9.723515 |
6. | weight price 2 74 4.613294 |
7. | length price 2 74 -92.95226 |
8. | displacement price 2 74 10.30914 |
9. | 5b.trunk price 2 74 0 |
10. | 6.trunk price 2 74 530.6144 |
11. | 7.trunk price 2 74 -245.4009 |
12. | 8.trunk price 2 74 1722.497 |
13. | 9.trunk price 2 74 368.6347 |
14. | 10.trunk price 2 74 355.778 |
15. | 11.trunk price 2 74 -229.7306 |
16. | 12.trunk price 2 74 2002.943 |
17. | 13.trunk price 2 74 47.29906 |
18. | 14.trunk price 2 74 1746.247 |
19. | 15.trunk price 2 74 1473.953 |
20. | 16.trunk price 2 74 115.0414 |
21. | 17.trunk price 2 74 319.3028 |
22. | 18.trunk price 2 74 2780.235 |
23. | 20.trunk price 2 74 142.0096 |
24. | 21.trunk price 2 74 737.9046 |
25. | 22.trunk price 2 74 408.4962 |
26. | 23.trunk price 2 74 -669.1454 |
+--------------------------------------------------+