Extracting ttest results into excel file

[FitzGerald Blindman]

Hello everyone,

I use the next code to do t-tests on the means of variables between the treatment group and the control group in my sample:

local variables var1 var2 var3 var4 var 5 var6 var7 var8 var9 var10 var11 var12 var13 var14 var15 var16 var17 var18 var19 var20 var21 vr22 var23 var24 var25 var 26 var27 var28 var29 var30 var31 var32 vae33 var34 var35 var36 var37 var38 var39 var40 var41 var42 var43 var44 var45 var46 var47

foreach i in `variables' {
   disp "`i'"
   ttest `i', by(treatment)
}

Now I want to extract in into Excel file in the customary way: column A for the variables name, column B for the means and SE of the control group (treatment == 0) which are the first row of the ttest command output, column C for the means and SE of the treatment group (treatment == 1), column D for the difference. Of curse I would like that row 1 will be headlines, asterisks for the statistical significance level, SE's in parenthesis in a row below the means and row gap between variables.

I would really love of if you can tell me what I should add to the code, it's the first time I extract outputs from Stata to Excel.

Thank you very much,

Fitzgerald

[Carlo Lazzaro]

Fitzgerald:
see -help table hypothesis tests-.

[FitzGerald Blindman]

Fitzgerald:
see -help table hypothesis tests-.

Hi Calro, first of all - thank you.

I read it but I didn't really got how it can help me with the ttest command.
Maybe I'm not familiar with Stata enough, but isn't there a simple way to extract this result as I asked?

Thanks again,

Fitz.

[Hemanshu Kumar]

The following achieves most of what you want, except the significance stars. Perhaps others can pitch in to help add that bit.
This assumes you have Stata 17 or later, which is when the collect suite of commands was introduced.

collect clear
sysuse auto, clear
local testvars price mpg weight length

foreach v of local testvars {
   qui collect r(mu_1) r(se_1) r(mu_2) r(se_2) r(mu_diff) r(se) r(p), tags(TestVar[`v']): ttest `v', by(foreign)
}

collect addtags var[Control], fortags(result[mu_1 se_1])
collect addtags var[Treatment], fortags(result[mu_2 se_2])
collect addtags var[Difference], fortags(result[mu_diff se p])
collect addtags stats[mean], fortags(result[mu_diff mu_1 mu_2])
collect addtags stats[se], fortags(result[se_1 se_2 se])

collect style cell stats[mean se], nformat(%5.1f)
collect style cell stats[se], sformat("(%s)")

collect style header stats, level(hide)
collect style row stack, spacer

collect layout (TestVar#stats) (var)

which produces:

. collect preview

-------------------------------------
       | Control Treatment Difference
-------+-----------------------------
price  |  6072.4    6384.7     -312.3
       | (429.5)   (559.0)    (754.4)
       |                            
mpg    |    19.8      24.8       -4.9
       |   (0.7)     (1.4)      (1.4)
       |                            
weight |  3317.1    2315.9     1001.2
       |  (96.4)    (92.3)    (160.3)
       |                            
length |   196.1     168.5       27.6
       |   (2.8)     (2.9)      (4.7)
-------------------------------------

You can then use the collect export command to export the results to Excel.

[Hemanshu Kumar]

Frankly, the easiest solution for you might be to use the community-contributed balancetable command, whose default table layout is very close to what you want. See

net describe balancetable, from(http://fmwww.bc.edu/RePEc/bocode/b)

[Hemanshu Kumar]

Actually, #5 is not really true, in one critical respect. For the individual groups, balancetable reports the corresponding standard deviations instead of standard errors of the means. There does not appear to be an option to change this.

Yet another inbuilt alternative is dtable (assuming Stata 18 or later), but that has at least two issues: a) it also reports standard deviations instead of standard errors, and b) it does not report the value of the difference of means -- it only reports the p-value of the hypothesis test of equality of those means.