Combining summary statistics into one column using esttab

[Steffen Mauch]

I'm trying to compute summary statistics for the following variable into one column in Stata.

eststo: quietly estpost sum q58_amount_1 if wave == 1 & q56_save1 == 1
eststo: quietly estpost sum q58_amount_2 if wave == 1 & q56_save2 == 1
eststo: quietly estpost sum q58_amount_3 if wave == 1 & q56_save3 == 1
eststo: quietly estpost sum q58_amount_4 if wave == 1 & q56_save4 == 1
esttab, replace cells("mean(fmt(2)) sd(fmt(2))") label title(Savings II) noobs
eststo clear

q58_amount_1 - 4 are saving amounts for different option (e.g. using bank accounts, putting the money underneath a mattress, etc.). q56_save1 == 1 indicates that there are indeed positive savings for the given option. wave indicates the time period.

Using the above command I get the following output:

. esttab, replace cells("mean(fmt(2)) sd(fmt(2))") label title(Savings II) noobs

Savings II
--------------------------------------------------------------------------------------------------------------------
> --------
                              (1)                       (2)                       (3)                       (4)    
>        
                                                                                                                    
>        
                             mean           sd         mean           sd         mean           sd         mean    
>       sd
--------------------------------------------------------------------------------------------------------------------
> --------
q58_amount_1            288669.40    987614.42                                                                      
>        
q58_amount_2                                      664938.81   1609076.48                                            
>        
q58_amount_3                                                               2448495.47   5439977.76                  
>        
q58_amount_4                                                                                          243138.35    5
> 33569.69
--------------------------------------------------------------------------------------------------------------------
> --------

. eststo clear

.
end of do-file

.

What I would like to have are two columns: One for the mean and one for the sd, i.e. not 8 separate columns. I'm not familiar with esttab so this might seem obvious for regular Stata users. I`d be grateful for some advice.

[Nick Cox]

Cross-posted at https://stackoverflow.com/questions/...n-using-esttab

Please note our policy on cross-posting, which is that you should tell us about it https://www.statalist.org/forums/help#crossposting

[Chen Samulsion]

If you provide data example, people will provide more correct answer, but try this:

estpost tabstat q58_amount_1 q58_amount_2 q58_amount_3 q58_amount_4 if wave=1, by(q56_save1) stat(mean sd) nototal columns(stat)

[Steffen Mauch]

Apologies for not providing data in the first place. This might help answering my question.

     | id   wave   q58_~t_1   q58_~t_2   q58_~t_3   q58_~t_4   q56_sa~1   q56_sa~2   q56_sa~3   q56_sa~4 |
     |---------------------------------------------------------------------------------------------------|
  1. |  1      1          0    1020000          0          0          0          1          0          0 |
  2. |  1      2      60000     380000          0          0          1          1          0          0 |
  3. |  2      1          0      50000          0          0          0          1          0          0 |
  4. |  2      2          0    1500000          0      15000          0          1          0          1 |
  5. |  3      1          0          0          0     150000          0          0          0          1 |
     |---------------------------------------------------------------------------------------------------|
  6. |  3      2     200000          0      30000      11000          1          0          1          1 |
  7. |  4      1          0          0          0          0          0          0          0          0 |
  8. |  4      2          0     220000      40000      20000          0          1          1          1 |
  9. |  5      1     200000          0          0          0          1          0          0          0 |
 10. |  5      2          0          0          0          0          0          0          0          0 |
     |---------------------------------------------------------------------------------------------------|
 11. |  6      1          0     100000          0      10000          0          1          0          1 |
 12. |  6      2          0          0          0     300000          0          0          0          1 |
 13. |  7      1          0    3000000     700000          0          0          1          1          0 |
 14. |  7      2     500000          0    1500000          0          1          0          1          0 |
 15. |  8      1      60000     320000          0          0          1          1          0          0 |
     |---------------------------------------------------------------------------------------------------|
 16. |  8      2          0     350000          0          0          0          1          0          0 |
 17. |  9      1          0     250000          0          0          0          1          0          0 |
 18. |  9      2          .          .          .          .          .          .          .          . |
 19. | 10      1          0     200000          0          0          0          1          0          0 |
 20. | 10      2     130000     800000    1000000          0          1          1          1          0 |

Thank you for your answer Chen. The tricky thing is that there are four different dummies for each savings alternative. I believe your code does not respect that (again my fault, sorry for not providing reproducable data).

[Chen Samulsion]

Hi Steffen Mauch here is what I get: perhaps you could firstly change form of you data using -stack- command, and then run -estpost-. Please use -preserve- before running -stack- to avoid undesired damage done to your data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id wave q58_amount_1 q58_amount_2 q58_amount_3 q58_amount_4 q56_save1 q56_save2 q56_save3 q56_save4)
 1 1      0 1020000       0      0 0 1 0 0
 1 2  60000  380000       0      0 1 1 0 0
 2 1      0   50000       0      0 0 1 0 0
 2 2      0 1500000       0  15000 0 1 0 1
 3 1      0       0       0 150000 0 0 0 1
 3 2 200000       0   30000  11000 1 0 1 1
 4 1      0       0       0      0 0 0 0 0
 4 2      0  220000   40000  20000 0 1 1 1
 5 1 200000       0       0      0 1 0 0 0
 5 2      0       0       0      0 0 0 0 0
 6 1      0  100000       0  10000 0 1 0 1
 6 2      0       0       0 300000 0 0 0 1
 7 1      0 3000000  700000      0 0 1 1 0
 7 2 500000       0 1500000      0 1 0 1 0
 8 1  60000  320000       0      0 1 1 0 0
 8 2      0  350000       0      0 0 1 0 0
 9 1      0  250000       0      0 0 1 0 0
 9 2      .       .       .      . . . . .
10 1      0  200000       0      0 0 1 0 0
10 2 130000  800000 1000000      0 1 1 1 0
end

preserve
stack q58_amount_1 q56_save1 q58_amount_2 q56_save2 q58_amount_3 q56_save3 q58_amount_4 q56_save4, into(q58 q56) clear
egen wave=seq(), from(1) to(2)
label define q58_cat 1 q58_amount_1 2 q58_amount_2 3 q58_amount_3 4 q58_amount_4
label values _stack q58_cat
eststo: quietly estpost tabstat q58 if q56 == 1, by(_stack) stat(mean sd)
esttab, replace cells("mean(fmt(2)) sd(fmt(2))") label title(Savings II) noobs wide
restore

Savings II
----------------------------------------------
                              (1)             
                                              
                             mean           sd
----------------------------------------------
q58_amount_1            191666.67    163513.51
q58_amount_2            682500.00    846029.50
q58_amount_3            654000.00    633229.82
q58_amount_4             84333.33    118889.30
Total                   452275.86    647550.21
----------------------------------------------

[Steffen Mauch]

That seems to do the trick! Thanks for the help.