Export*coefficient and standard error of factor variable

[Asaf Yancu]

Hi!
Let's say this is my data

* Example generated by -dataex-. For more info, type help dataex
clear
input int price float headroom int trunk
 4099 2.5 11
 5189   2 16
 3299 2.5  9
 5104   2 16
 3667   2  7
 3984   2  8
 4389 1.5  9
 4187   2 10
13594 2.5 18
 5172   2 16
 4195   2 10
 4647   2 11
 4425 2.5 11
 6486 1.5  8
 4934 1.5  7
 5222   2 16
 4172   2  7
 6295 2.5 11
 9735 2.5 12
 6229 1.5  6
 4589   2  8
 5079 2.5  8
 8129 2.5  8
 4296 2.5 16
 4499 2.5  5
 3798 2.5 11
 5899 2.5 14
 5719   2 11
 7140 2.5 12
 6850   2 16
11995 2.5 14
end

I run two regressions:

1. reg price headroom i.trunk

2. reg price headroom i.trunk if trunk<8

I would like to export to excel the coefficient and standard error of each variable, including the factor variable of trunk.
Here's an example of what I am looking for:

	headroom_coef	headroom_se	6.trunk_coef	6.trunk_se	7.trunk_coef	7.trunk_se	8.trunk_coef	8.trunk_se	...	18.trunk_coef	18.trunk_se	number of obs
model 1	-632.0262	1074.751	1097.974	2358.746	-662.6841	1858.073	901.5895	1682.252		9095	2099.665	31
model 2	-2029	874.6857	-299	1010	-1594	714.1778						5

Many Thanks!

[Carlo Lazzaro]

Asaf:
you may want to try something along the following lines:

. statsby _b _se, verbose nodots: regress price headroom i.trunk

      command:  regress price headroom i.trunk
  _b_headroom:  _b[headroom]
      _stat_2:  _b[5b.trunk]
      _stat_3:  _b[6.trunk]
      _stat_4:  _b[7.trunk]
      _stat_5:  _b[8.trunk]
      _stat_6:  _b[9.trunk]
      _stat_7:  _b[10.trunk]
      _stat_8:  _b[11.trunk]
      _stat_9:  _b[12.trunk]
     _stat_10:  _b[14.trunk]
     _stat_11:  _b[16.trunk]
     _stat_12:  _b[18.trunk]
      _b_cons:  _b[_cons]
 _se_headroom:  _se[headroom]
     _stat_15:  _se[5b.trunk]
     _stat_16:  _se[6.trunk]
     _stat_17:  _se[7.trunk]
     _stat_18:  _se[8.trunk]
     _stat_19:  _se[9.trunk]
     _stat_20:  _se[10.trunk]
     _stat_21:  _se[11.trunk]
     _stat_22:  _se[12.trunk]
     _stat_23:  _se[14.trunk]
     _stat_24:  _se[16.trunk]
     _stat_25:  _se[18.trunk]
     _se_cons:  _se[_cons]
           by:  <none>


. list

     +--------------------------------------------------------------------------------------------------------------------------+
  1. | _b_head~m | _stat_2 |  _stat_3 |   _stat_4 |  _stat_5 |   _stat_6 |   _stat_7 |  _stat_8 | _stat_9 | _stat_10 | _stat_11 |
     | -632.0262 |       0 | 1097.974 | -662.6841 | 901.5895 | -971.0131 | -624.0131 | 226.1623 |  3938.5 |     4448 | 543.1558 |
     |---------------------+----------+-----------------------------------------------------------------------------------------|
     | _stat_12 |  _b_cons | _se_he~m | _stat_15 | _stat_16 | _stat_17 | _stat_18 | _stat_19 | _stat_20 | _stat_21  | _stat_22  |
     |     9095 | 6079.065 | 1074.751 |        0 | 2358.746 | 1858.073 | 1682.252 | 1896.106 | 1896.106 | 1613.619  | 1818.363  |
     |--------------------------------------------------------------------------------------------------------------------------|
     |          _stat_23           |           _stat_24           |           _stat_25           |           _se_cons           |
     |          1818.363           |           1664.997           |           2099.665           |            3069.79           |
     +--------------------------------------------------------------------------------------------------------------------------+

.

[Maarten Buis]

We often say to use dataex to give us an example of your data. However, you don't have to do that for datasets we all have access to, like the auto dataset you just gave us. Anyhow, you did not say which version of Stata you are using, and on this site that means you have the latest version (17). If you have an older version, then this solution won't work.

sysuse auto, clear
collect clear
collect _r_b _r_se , tag(model[(1)]): reg price weight i.rep78
collect _r_b _r_se , tag(model[(2)]): reg price weight i.rep78 i.foreign
collect layout (model) (colname#result result[N])
collect style header result[N], level(label)
collect preview
collect export "c:\temp\foo.xlsx", replace

[Asaf Yancu]

Asaf:
you may want to try something along the following lines:

. statsby _b _se, verbose nodots: regress price headroom i.trunk

command: regress price headroom i.trunk
_b_headroom: _b[headroom]
_stat_2: _b[5b.trunk]
_stat_3: _b[6.trunk]
_stat_4: _b[7.trunk]
_stat_5: _b[8.trunk]
_stat_6: _b[9.trunk]
_stat_7: _b[10.trunk]
_stat_8: _b[11.trunk]
_stat_9: _b[12.trunk]
_stat_10: _b[14.trunk]
_stat_11: _b[16.trunk]
_stat_12: _b[18.trunk]
_b_cons: _b[_cons]
_se_headroom: _se[headroom]
_stat_15: _se[5b.trunk]
_stat_16: _se[6.trunk]
_stat_17: _se[7.trunk]
_stat_18: _se[8.trunk]
_stat_19: _se[9.trunk]
_stat_20: _se[10.trunk]
_stat_21: _se[11.trunk]
_stat_22: _se[12.trunk]
_stat_23: _se[14.trunk]
_stat_24: _se[16.trunk]
_stat_25: _se[18.trunk]
_se_cons: _se[_cons]
by: <none>


. list

+--------------------------------------------------------------------------------------------------------------------------+
1. | _b_head~m | _stat_2 | _stat_3 | _stat_4 | _stat_5 | _stat_6 | _stat_7 | _stat_8 | _stat_9 | _stat_10 | _stat_11 |
| -632.0262 | 0 | 1097.974 | -662.6841 | 901.5895 | -971.0131 | -624.0131 | 226.1623 | 3938.5 | 4448 | 543.1558 |
|---------------------+----------+-----------------------------------------------------------------------------------------|
| _stat_12 | _b_cons | _se_he~m | _stat_15 | _stat_16 | _stat_17 | _stat_18 | _stat_19 | _stat_20 | _stat_21 | _stat_22 |
| 9095 | 6079.065 | 1074.751 | 0 | 2358.746 | 1858.073 | 1682.252 | 1896.106 | 1896.106 | 1613.619 | 1818.363 |
|--------------------------------------------------------------------------------------------------------------------------|
| _stat_23 | _stat_24 | _stat_25 | _se_cons |
| 1818.363 | 1664.997 | 2099.665 | 3069.79 |
+--------------------------------------------------------------------------------------------------------------------------+

.

Thank you Carlo. How can I export this list to excel?

[Asaf Yancu]

We often say to use dataex to give us an example of your data. However, you don't have to do that for datasets we all have access to, like the auto dataset you just gave us. Anyhow, you did not say which version of Stata you are using, and on this site that means you have the latest version (17). If you have an older version, then this solution won't work.

sysuse auto, clear
collect clear
collect _r_b _r_se , tag(model[(1)]): reg price weight i.rep78
collect _r_b _r_se , tag(model[(2)]): reg price weight i.rep78 i.foreign
collect layout (model) (colname#result result[N])
collect style header result[N], level(label)
collect preview
collect export "c:\temp\foo.xlsx", replace

Thank you Maarten. I am using Stata 16.1 SE. Can I export to excel using 16.1 version commands?

[Maarten Buis]

This is why it is important to tell what version of Stata you are using.

You can just use Carlo's solution. You know the key words to search for, so type in Stata search export excel and you will be able to find how to get a stata dataset to excel.

[Carlo Lazzaro]

Thanks Maarten.
I was late to the party.