esttab - how to combine columns(variables) and columns(statistics) tables?

[Maiia Sleptcova]

Hi Statalisters,

I am trying to create a table of means of three variables (char1, char2, char3), with a single column of observation count on the left, very much similar to below, but without empty columns. How can I achieve that?

char1 in the first estpost is chosen arbitrarily, I'd just like to count the number of observations per year, so it can be any of the other chars.

. eststo: qui estpost tabstat char1 , statistics(count) by(year) columns(statistics) 
(est1 stored)

. eststo: qui estpost tabstat char1 char2 char3 , statistics(mean) by(year) columns(variables) 
(est2 stored)

. esttab , cells("count char1(fmt(%9.1f)) char2( fmt(%9.2f)) char3( fmt(%9.0f))") noobs nonumber 

--------------------------------------------------------------------------------------------------------------------
                                                                                                                    
                    count        char1        char2        char3        count        char1        char2        char3
--------------------------------------------------------------------------------------------------------------------
1                     192                                                             12.0         0.58          215
2                     180                                                              8.9         0.54          278
3                     113                                                             10.0         0.54          274
4                      96                                                             10.5         0.52          319
5                      69                                                              9.3         0.47          303
6                      52                                                              5.8         0.49          355
7                      29                                                              6.0         0.44          392
8                      23                                                              7.9         0.49          356
9                      20                                                              4.7         0.49          347
10                      9                                                              9.8         0.48          298
11                      4                                                             16.3         0.55          408
12                      4                                                             11.5         0.46          542
Total                 791                                                              9.6         0.53          285
--------------------------------------------------------------------------------------------------------------------

Many thanks in advance.

Maiia

[Andrew Musau]

estout is from SSC, as you are asked to explain (FAQ Advice #12).

webuse grunfeld, clear
bys year: egen count=total(!missing(invest))
eststo: qui estpost tabstat count invest mvalue kstock , statistics(mean) by(year) columns(variables)
esttab, cells("count invest(fmt(%9.1f)) mvalue( fmt(%9.2f)) kstock( fmt(%9.0f))") noobs nonumber

Res.:

. 
. esttab, cells("count invest(fmt(%9.1f)) mvalue( fmt(%9.2f)) kstock( fmt(%9.0f))") noobs nonumber

----------------------------------------------------------------
                                                                
                    count       invest       mvalue       kstock
----------------------------------------------------------------
1935                   10         72.7       707.47           62
1936                   10        101.6      1079.57           74
1937                   10        122.5      1352.29          103
1938                   10         77.6       847.14          139
1939                   10         80.5      1080.25          156
1940                   10        113.3      1134.02          159
1941                   10        139.7      1087.95          177
1942                   10        122.7       880.85          204
1943                   10        117.8       995.16          214
1944                   10        120.9      1027.77          212
1945                   10        124.2      1140.10          219
1946                   10        161.4      1203.96          234
1947                   10        147.1       927.30          308
1948                   10        153.9       896.98          349
1949                   10        139.2       917.55          384
1950                   10        151.1       977.06          405
1951                   10        199.6      1208.08          427
1952                   10        224.0      1254.38          486
1953                   10        275.6      1477.78          565
1954                   10        273.8      1437.94          645
Total                  10        146.0      1081.68          276
----------------------------------------------------------------

.

[Maiia Sleptcova]

Thanks Andrew, that's great! Sorry, my bad about the SSC mention.

Is there a way to make the bottom row (total) of count to actually reflect the sum of all observations, not the mean?

Many thanks
Maiia

[Andrew Musau]

Here is one way, but I am sure the code can be more efficient.

webuse grunfeld, clear
bys year: egen count=total(!missing(invest))
preserve
collapse count invest mvalue kstock, by(year)
collapse (sum) count invest mvalue kstock
gen year=99999
tempfile total
save `total'
restore, preserve
append using `total'
qui estpost tabstat count invest mvalue kstock, statistics(mean) by(year) columns(variables)
esttab, cells("count invest(fmt(%9.1f)) mvalue( fmt(%9.2f)) kstock( fmt(%9.0f))") drop(Total) coeflab(99999 "Total") noobs nonumber
restore

Res.:

. esttab, cells("count invest(fmt(%9.1f)) mvalue( fmt(%9.2f)) kstock( fmt(%9.0f))") drop(Total) coeflab(99999 "Total") noobs nonumber

----------------------------------------------------------------
                                                                
                    count       invest       mvalue       kstock
----------------------------------------------------------------
1935                   10         72.7       707.47           62
1936                   10        101.6      1079.57           74
1937                   10        122.5      1352.29          103
1938                   10         77.6       847.14          139
1939                   10         80.5      1080.25          156
1940                   10        113.3      1134.02          159
1941                   10        139.7      1087.95          177
1942                   10        122.7       880.85          204
1943                   10        117.8       995.16          214
1944                   10        120.9      1027.77          212
1945                   10        124.2      1140.10          219
1946                   10        161.4      1203.96          234
1947                   10        147.1       927.30          308
1948                   10        153.9       896.98          349
1949                   10        139.2       917.55          384
1950                   10        151.1       977.06          405
1951                   10        199.6      1208.08          427
1952                   10        224.0      1254.38          486
1953                   10        275.6      1477.78          565
1954                   10        273.8      1437.94          645
Total                 200       2919.2     21633.62         5520
----------------------------------------------------------------

[Maiia Sleptcova]

Thanks again Andrew, this works, I just had to make this little modification to achieve the desired result. Thanks so much!

webuse grunfeld, clear
bys year: egen count=total(!missing(invest))
preserve
collapse count invest mvalue kstock, by(year)
collapse (sum) count (mean) invest mvalue kstock
gen year=99999
tempfile total
save `total'
restore, preserve
append using `total'
qui estpost tabstat count invest mvalue kstock, statistics(mean) by(year) columns(variables)
esttab, cells("count invest(fmt(%9.1f)) mvalue( fmt(%9.2f)) kstock( fmt(%9.0f))") drop(Total) coeflab(99999 "Total") noobs nonumber
restore

Result:

----------------------------------------------------------------
                                                                
                    count       invest       mvalue       kstock
----------------------------------------------------------------
1935                   10         72.7       707.47           62
1936                   10        101.6      1079.57           74
1937                   10        122.5      1352.29          103
1938                   10         77.6       847.14          139
1939                   10         80.5      1080.25          156
1940                   10        113.3      1134.02          159
1941                   10        139.7      1087.95          177
1942                   10        122.7       880.85          204
1943                   10        117.8       995.16          214
1944                   10        120.9      1027.77          212
1945                   10        124.2      1140.10          219
1946                   10        161.4      1203.96          234
1947                   10        147.1       927.30          308
1948                   10        153.9       896.98          349
1949                   10        139.2       917.55          384
1950                   10        151.1       977.06          405
1951                   10        199.6      1208.08          427
1952                   10        224.0      1254.38          486
1953                   10        275.6      1477.78          565
1954                   10        273.8      1437.94          645
Total                 200        146.0      1081.68          276
----------------------------------------------------------------