Use esttab to generate summary statistics by group with columns for mean difference and significance

Question

I would like to use esttab (ssc install estout) to generate summary statistics by group with columns for the mean difference and significance. It is easy enough to generate these as two separate tables with estpost, summarize, and ttest, and combine manually, but I would like to automate the whole process.

The following code generates the two components of the desired table.

sysuse auto, clear

* summary statistics by group
eststo clear
by foreign: eststo: quietly estpost summarize ///
    price mpg weight headroom trunk
esttab, cells("mean sd") label nodepvar   

* difference in means
eststo: estpost ttest price mpg weight headroom trunk, ///
    by(foreign) unequal 
esttab ., wide label   

And I can print the two tables and cut-an-paste into one table.

* can generate similar tables and append horizontally
esttab, cells("mean sd") label
esttab, wide label


* manual, cut-and-paste solution
-------------------------------------------------------------------------------------------------------
                              (1)                       (2)                         (3)                

                             mean           sd         mean           sd         
-------------------------------------------------------------------------------------------------------
Price                    6072.423     3097.104     6384.682     2621.915         -312.3         (-0.44)
Mileage (mpg)            19.82692     4.743297     24.77273     6.611187         -4.946**       (-3.18)
Weight (lbs.)            3317.115     695.3637     2315.909     433.0035         1001.2***       (7.50)
Headroom (in.)           3.153846     .9157578     2.613636     .4862837          0.540**        (3.30)
Trunk space (.. ft.)        14.75     4.306288     11.40909     3.216906          3.341***       (3.67)
-------------------------------------------------------------------------------------------------------
Observations                   52                        22                          74                
-------------------------------------------------------------------------------------------------------
t statistics in parentheses
* p<0.05, ** p<0.01, *** p<0.001

It seems that I should be able to get the desired table with one esttab call and without cutting-and-pasting, but I can't figure it out. Is there a way to generate the desired table without manually cutting-and-pasting?

I would prefer to output a LaTeX table, but anything that eliminates the cutting-and-pasting is a big step, even passing through a delimited text file.

Preindicate

Answer 1

If you still want to use esttab, you can play around using cells and pattern. The table in the original post can be replicated with the following code:

sysuse auto, clear

eststo domestic: quietly estpost summarize ///
    price mpg weight headroom trunk if foreign == 0
eststo foreign: quietly estpost summarize ///
    price mpg weight headroom trunk if foreign == 1
eststo diff: quietly estpost ttest ///
    price mpg weight headroom trunk, by(foreign) unequal

esttab domestic foreign diff, ///
cells("mean(pattern(1 1 0) fmt(2)) sd(pattern(1 1 0)) b(star pattern(0 0 1) fmt(2)) t(pattern(0 0 1) par fmt(2))") ///
label

which yields

-----------------------------------------------------------------------------------------------------
                              (1)                       (2)                       (3)                

                             mean           sd         mean           sd            b               t
-----------------------------------------------------------------------------------------------------
Price                     6072.42      3097.10      6384.68      2621.92      -312.26         (-0.44)
Mileage (mpg)               19.83         4.74        24.77         6.61        -4.95**       (-3.18)
Weight (lbs.)             3317.12       695.36      2315.91       433.00      1001.21***       (7.50)
Headroom (in.)               3.15         0.92         2.61         0.49         0.54**        (3.30)
Trunk space (.. ft.)        14.75         4.31        11.41         3.22         3.34***       (3.67)
-----------------------------------------------------------------------------------------------------
Observations                   52                        22                        74                
-----------------------------------------------------------------------------------------------------

Rorqual

Thanks! I haven't looked that closely at the cells options. This will come in handy everywhere I use esttab. Which is everywhere. Ben Jann should get a Nobel for esttab. – Preindicate

Answer 2

I don't think there's a way to do this with esttab (estout package from ssc), but I have a solution with listtab (also ssc) and postfile. The table here is a little different than the one I propose above, but the approach is general enough that you can modify it to fit your needs.

This solution also use LaTeX's booktabs package.

/* data and variables */
sysuse auto, clear
local vars price mpg weight headroom trunk

/* means */
tempname postMeans
tempfile means
postfile `postMeans' ///
    str100 varname domesticMeans foreignMeans pMeans using "`means'", replace
foreach v of local vars {
    local name: variable label `v'
    ttest `v', by(foreign)
    post `postMeans' ("`name'") (r(mu_1)) (r(mu_2)) (r(p))
}
postclose `postMeans'

/* medians */
tempname postMedians
tempfile medians
postfile `postMedians' ///
    domesticMedians foreignMedians pMedians using `medians', replace
foreach v of local vars {
    summarize `v' if !foreign, detail
    local med1 = r(p50)
    summarize `v' if foreign, detail
    local med2 = r(p50)
    ranksum `v', by(foreign)
    local pval = 2 * (1 - normal(abs(r(z))))
    post `postMedians' (`med1') (`med2') (`pval')
}
postclose `postMedians'

/* combine */
use `means'
merge 1:1 _n using `medians', nogenerate
format *Means *Medians %9.3gc
list

/* make latex table */
/* requires LaTeX package `booktabs` */
listtab * using "Table.tex", ///
    rstyle(tabular) replace ///
    head("\begin{tabular}{lcccccc}" ///
    "\toprule" ///
    "& \multicolumn{3}{c}{Means} & \multicolumn{3}{c}{Medians} \\" ///
    "\cmidrule(lr){2-4} \cmidrule(lr){5-7}" ///
    "& Domestic & Foreign & \emph{p} & Domestic & Foreign & \emph{p}\\" ///
    "\midrule") ///
    foot("\bottomrule" "\end{tabular}")

This yields the following.

Preindicate

Answer 3

The answer chosen is nice but a bit redudant. You can achieve the same result with only estpost ttest.

sysuse auto, clear
estpost ttest price mpg weight headroom trunk, by(foreign)
esttab, cells("mu_1 mu_2 b(star)"

The output looks like this:

             mu_1          mu_2         b   

c_score     43.33858    42.034       1.30458***
nc_a4_17    4.007524    3.924623    .0829008*  

Synapse

Nice! One advantage of the chosen answer is that you can include moments not reported by estpost ttest. – Preindicate