Summary stats and balance table

[Scott Rick]

Hi. I am trying to create a descriptive and balance table in a excel document that looks like the image below, with the p-values starred as needed. However, so far, I've only managed the code below and am at a loss on how to create the table in the image in stata. Any pointers here will be much appreciated. Thanks

 eststo control: quietly estpost summarize ///
>    pop_perkm2_2019 povertyperct test_avg7day if stepone == 0

. eststo treatment: quietly estpost summarize ///
>     pop_perkm2_2019 povertyperct test_avg7day if stepone == 1

. eststo diff: quietly estpost ttest ///
>     pop_perkm2_2019 povertyperct test_avg7day, by(stepone) unequal

estout control treatment 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 fm
> t(2))") ///
> label

-----------------------------------------------------------------------------------------------------
                          control                 treatment                      diff                
                             mean           sd         mean           sd            b               t
-----------------------------------------------------------------------------------------------------
(max) pop_perkm~2019      1053.20      2352.69      1397.43      3397.37      -344.23         (-0.33)
(max) povertyperct          15.08        10.15        23.19        14.35        -8.11         (-1.84)
(sum) test_avg7day       1.01e+08     1.09e+08  90342730.46  94012943.52  10737483.63          (0.30)
-----------------------------------------------------------------------------------------------------

[Muhammad Saleh]

see help asdoc. That will hopefully be addressing what you intend to do

[Rich Goldstein]

h dtable

assuming you are using the current version of Stata (as the FAQ requests, you need to tell us if you are using an older version)

[Scott Rick]

Rich Goldstein: Thanks, Rich. dtable worked perfectly with the code below.

I have two quick questions. As in the screenshot above, is it possible to have the standard deviations in the second line for each variable? And is there any way for me to remove the percentages between the Treatment/Control columns, and include (N=.). I've included sample data with a few variables at the very end in case it helps.

dtable $demo $health $healthsys $econ, by(stepone, tests) sample(, place(seplabels)) ///
> nformat(%16.2fc mean sd test) title(Table 1: Descriptive Statistics and Balance)  ///
> note(Mean (Standard deviation): p-value from a pooled t-test.)  ///
> note(Significance levels are: *p < 0.1, **p < 0.05, ***p < 0.01)
note: using test regress across levels of stepone for pop_perkm2_2019, per60_2011, per85_2011, deathrate_2019,
      povertyperct, malariacase2018, denguecase2018, typoidtot_case2018, acuteresp_totcase2018, pneumoniatot_case2018,
      tb2018, influenzacase_2018, hepcases_2018, diabetes20181k, hypertension20181k, htnanddm20181k, cvds20181k,
      stroke20181k, hepcases_20181k, commoncancers20181k, mentalillness_20111k, hospital_total1k,
      hospital_beds_total1k, icu_beds_total1k, ventilators_total1k, and exphealth_percap1516.

. 
. * add variable labels 
. collect label levels var ///
>     pop_perkm2_2019 "Population per km. square" ///
>    
Note: collect is ignoring label "Population per km. square" for level pop_perkm2_2019 of dimension var.

. * hide by variable title
. collect style header stepone, title(hide)

. 
. * make Total the first column
. collect levelsof stepone

Collection: DTable
 Dimension: stepone
    Levels: .m 0 1 _dtable_test

. collect style autolevels stepone .m `s(levels)', clear

. 
. 
. * change the label for the p-values
. collect levelsof stepone

Collection: DTable
 Dimension: stepone
    Levels: .m 0 1 _dtable_test

. collect label levels stepone _dtable_test "p-value", modify

. collect label levels stepone _dtable_0 "Control", modify

. collect label levels stepone _dtable_1 "Treatment", modify

. 
. collect preview

Table 1: Descriptive Statistics and Balance
-------------------------------------------------------------------------------------------------------------------------
                                                             Total              Control            Treatment      p-value
                                                          33 (100.0%)          18 (54.5%)          15 (45.5%)            
-------------------------------------------------------------------------------------------------------------------------
Population per km. sq.                                1,209.67 (2,832.05) 1,053.20 (2,352.69) 1,397.43 (3,397.37)   0.734
Population over 60 (% of population)                          8.01 (1.94)         8.32 (2.03)         7.63 (1.83)   0.322
Population over 85 (% of population)                          0.40 (0.12)         0.42 (0.13)         0.38 (0.10)   0.272
Death rate                                                    5.61 (1.10)         5.56 (1.02)         5.68 (1.23)   0.752
Poverty percentage                                          18.77 (12.71)       15.08 (10.15)       23.19 (14.35)   0.067
Malaria cases (% of population)                               0.58 (1.05)         0.54 (1.15)         0.62 (0.96)   0.817
Dengue cases (% of population)                                0.15 (0.20)         0.17 (0.23)         0.12 (0.15)   0.519
Typhoid cases (% of population)                               2.28 (2.42)         2.73 (3.15)         1.74 (0.88)   0.246
Acute respiratory disease cases (% of population)          69.67 (102.83)       76.01 (65.11)      62.07 (137.51)   0.705
Pneumonia cases (% of population)                             1.22 (2.31)         1.76 (3.03)         0.57 (0.45)   0.143
TB cases (% of population)                                    1.80 (0.73)         1.78 (0.72)         1.82 (0.77)   0.865
Influenza cases (% of population)                             0.01 (0.03)         0.01 (0.01)         0.02 (0.05)   0.663
Hepatitis cases (% of population)                             0.19 (0.21)         0.19 (0.19)         0.19 (0.24)   0.922
Diabetes cases at NCD clinics (% of population)               0.01 (0.02)         0.02 (0.02)         0.01 (0.01)   0.496
Hypertension cases at NCD clinics (% of population)           0.02 (0.03)         0.02 (0.03)         0.02 (0.02)   0.433
HTN & DM cases at NCD clinics (% of population)               0.01 (0.02)         0.01 (0.02)         0.01 (0.01)   0.420
CVDs cases at NCD clinics (% of population)                   0.00 (0.01)         0.00 (0.00)         0.00 (0.01)   0.391
Stroke cases at NCD clinics (% of population)                 0.00 (0.00)         0.00 (0.00)         0.00 (0.00)   0.314
Hepatitis cases (% of population)                         188.88 (210.35)     185.53 (186.37)     192.89 (242.75)   0.922
Common cancers cases at NCD clinics (% of population)         0.00 (0.00)         0.00 (0.00)         0.00 (0.00)   0.032
Mental Illness (% of population)                              0.70 (0.32)         0.78 (0.39)         0.61 (0.18)   0.143
Hospitals (% of population)                                   0.06 (0.04)         0.06 (0.04)         0.05 (0.05)   0.819
Hospitals beds (% of population)                              1.77 (1.06)         1.93 (1.05)         1.57 (1.08)   0.344
ICU beds (% of population)                                    0.09 (0.05)         0.10 (0.05)         0.08 (0.05)   0.340
Ventilators (% of population)                                 0.04 (0.03)         0.05 (0.03)         0.04 (0.03)   0.344
Per Capita Health Expenditure                         1,962.97 (1,334.00) 2,117.11 (1,441.70) 1,778.00 (1,215.10)   0.476
-------------------------------------------------------------------------------------------------------------------------
Mean (Standard deviation): p-value from a pooled t-test.
Significance levels are: *p < 0.1, **p < 0.05, ***p < 0.01


***********Sample data***********

dataex state stepone malariacase2018 povertyperct per85_2011 per60_2011

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str50 state byte stepone float(malariacase2018 povertyperct per85_2011 per60_2011)
"Haryana"                                  0    .1113864 12.28  .4819998  8.7
"Jammu and Kashmir"                        0    .0127122 12.58  .4357921  7.4
"Uttar Pradesh"                            0   .29102564 37.79  .4453819  7.7
"Assam"                                    0   .11505759 32.67   .345935  6.7
"Tripura"                                  0    3.348438 16.65  .5334361  7.9
"Andhra Pradesh"                           0   .06727542 12.31  .3636027 10.1
"Chandigarh"                               0  .022668727  5.97  .3244114  6.4
"Gujarat"                                  0   .33208245  18.6  .4189846  7.9
"West Bengal"                              0    .7650726 21.43  .4795975  8.5
"Sikkim"                                   0 .0045454544  3.82  .3403338  6.7
"Kerala"                                   0   .02517886   .71  .7166544 12.6
"Tamil Nadu"                               0    .0537068 13.74  .3954023 10.4
"Telangana"                                0   .04847811 13.74  .3636027  9.2
"Himachal Pradesh"                         0  .013599778  7.62  .7087665 10.2
"Mizoram"                                  0    3.959447   9.8   .308602  6.3
"Goa"                                      0   .18230174  3.76 .44804925 11.2
"Rajasthan"                                0   .04676566 29.46  .3493121  7.5
"Dadra and Nagar Haveli and Daman and Diu" 0      .24291 18.51 .17497052  4.3
"Madhya Pradesh"                           1   .26624772 36.65 .41156015  7.9
"Meghalaya"                                1   2.2577684 32.67 .25818288  4.7
"Chhattisgarh"                             1    2.912262 29.91  .3161768  7.8
"Uttarakhand"                              1   .03756774 17.72 .46568155  8.9
"Jharkhand"                                1    1.560131 42.16 .24966857  7.1
"Manipur"                                  1 .0045351475 17.89  .3943212    7
"Delhi"                                    1  .021000754  4.79  .2978507  6.8
"Arunachal Pradesh"                        1    .4660701 24.27  .2721635  4.6
"Odisha"                                   1    1.537165 29.35  .3839952  9.5
"Maharashtra"                              1   .08725575 14.85  .4836896  9.9
"Nagaland"                                 1    .0473251 25.23  .3428857  5.2
"Punjab"                                   1   .02170464  5.59  .6241715 10.3
"Bihar"                                    1  .011281452 51.91  .2980746  7.4