1

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

daedsidog
  • 1,732
  • 2
  • 17
  • 36
aelhak
  • 441
  • 4
  • 14
  • I need to be able to know the variable that the beta is related to. E.g the beta in row 5 is for mpg. Your solution currently does not show which variable the beta is related to – aelhak Dec 05 '18 at 11:52

2 Answers2

2

If you change exp to adjusted in post temp you will get what you want:

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 ("`adjusted'") ("`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. |                                            price        1   74           0 |
  2. |                                            price        1   74    312.2587 |
  3. | mpg weight length displacement i.trunk     price        2   74           0 |
  4. | mpg weight length displacement i.trunk     price        2   74    3152.553 |
  5. | mpg weight length displacement i.trunk     price        2   74   -9.723515 |
  6. | mpg weight length displacement i.trunk     price        2   74    4.613294 |
  7. | mpg weight length displacement i.trunk     price        2   74   -92.95226 |
  8. | mpg weight length displacement i.trunk     price        2   74    10.30914 |
  9. | mpg weight length displacement i.trunk     price        2   74           0 |
 10. | mpg weight length displacement i.trunk     price        2   74    530.6144 |
 11. | mpg weight length displacement i.trunk     price        2   74   -245.4009 |
 12. | mpg weight length displacement i.trunk     price        2   74    1722.497 |
 13. | mpg weight length displacement i.trunk     price        2   74    368.6347 |
 14. | mpg weight length displacement i.trunk     price        2   74     355.778 |
 15. | mpg weight length displacement i.trunk     price        2   74   -229.7306 |
 16. | mpg weight length displacement i.trunk     price        2   74    2002.943 |
 17. | mpg weight length displacement i.trunk     price        2   74    47.29906 |
 18. | mpg weight length displacement i.trunk     price        2   74    1746.247 |
 19. | mpg weight length displacement i.trunk     price        2   74    1473.953 |
 20. | mpg weight length displacement i.trunk     price        2   74    115.0414 |
 21. | mpg weight length displacement i.trunk     price        2   74    319.3028 |
 22. | mpg weight length displacement i.trunk     price        2   74    2780.235 |
 23. | mpg weight length displacement i.trunk     price        2   74    142.0096 |
 24. | mpg weight length displacement i.trunk     price        2   74    737.9046 |
 25. | mpg weight length displacement i.trunk     price        2   74    408.4962 |
 26. | mpg weight length displacement i.trunk     price        2   74   -669.1454 |
     +----------------------------------------------------------------------------+

EDIT:

You also need to change the str length in exp to 100 or longer to save all covariates in the string:

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

Then if you also change post temp to also include the exposure:

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

You will get the desired output:

list exp outcome adjust N beta, separator(0)

     +--------------------------------------------------------------------------------------+
     |                                              exp   outcome   adjust    N        beta |
     |--------------------------------------------------------------------------------------|
  1. |                                        i.foreign     price        1   74           0 |
  2. |                                        i.foreign     price        1   74    312.2587 |
  3. | i.foreign mpg weight length displacement i.trunk     price        2   74           0 |
  4. | i.foreign mpg weight length displacement i.trunk     price        2   74    3152.553 |
  5. | i.foreign mpg weight length displacement i.trunk     price        2   74   -9.723515 |
  6. | i.foreign mpg weight length displacement i.trunk     price        2   74    4.613294 |
  7. | i.foreign mpg weight length displacement i.trunk     price        2   74   -92.95226 |
  8. | i.foreign mpg weight length displacement i.trunk     price        2   74    10.30914 |
  9. | i.foreign mpg weight length displacement i.trunk     price        2   74           0 |
 10. | i.foreign mpg weight length displacement i.trunk     price        2   74    530.6144 |
 11. | i.foreign mpg weight length displacement i.trunk     price        2   74   -245.4009 |
 12. | i.foreign mpg weight length displacement i.trunk     price        2   74    1722.497 |
 13. | i.foreign mpg weight length displacement i.trunk     price        2   74    368.6347 |
 14. | i.foreign mpg weight length displacement i.trunk     price        2   74     355.778 |
 15. | i.foreign mpg weight length displacement i.trunk     price        2   74   -229.7306 |
 16. | i.foreign mpg weight length displacement i.trunk     price        2   74    2002.943 |
 17. | i.foreign mpg weight length displacement i.trunk     price        2   74    47.29906 |
 18. | i.foreign mpg weight length displacement i.trunk     price        2   74    1746.247 |
 19. | i.foreign mpg weight length displacement i.trunk     price        2   74    1473.953 |
 20. | i.foreign mpg weight length displacement i.trunk     price        2   74    115.0414 |
 21. | i.foreign mpg weight length displacement i.trunk     price        2   74    319.3028 |
 22. | i.foreign mpg weight length displacement i.trunk     price        2   74    2780.235 |
 23. | i.foreign mpg weight length displacement i.trunk     price        2   74    142.0096 |
 24. | i.foreign mpg weight length displacement i.trunk     price        2   74    737.9046 |
 25. | i.foreign mpg weight length displacement i.trunk     price        2   74    408.4962 |
 26. | i.foreign mpg weight length displacement i.trunk     price        2   74   -669.1454 |
     +--------------------------------------------------------------------------------------+
  • This is very close but still not quite right. exp in row 3 and 4 should say i.foreign. Row 5 should be mpg, Row 6 should be weight. Row 7 should be length. Row 8 should be displacement – aelhak Dec 05 '18 at 11:56
  • 1
    See my other answer. –  Dec 05 '18 at 12:02
2

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 |
     +--------------------------------------------------+