Can I use Dtable to export mean/N and sd/% to separate columns in Excel?

[Mark Hawes]

I just upgraded to Stata 18 and am trying to create my Table 1. I have successfully exported the table to Excel, but the mean and sd are in the same Excel column, and the n and % are in the same Excel column. I need the mean/N in one column and the sd/% in one column (see screenshot of what I want below). I haven't found a way to do this using Dtable, but I am sure there must be a way. Does anyone know how to do this?

I pasted the code I am using and a sample of my dataset below. I also attached a screenshot from Excel showing the format I am trying to achieve.

dtable c.age i.gender i.education, by(amphetamines_past30m, nototals tests) sample(, statistics(freq) place(seplabels)) sformat("(N=%s)" frequency) note(Total sample: N = 344) nformat(%7.2f mean sd) title(Table 1. Demographics) export(table1.xlsx, replace)

* Example generated by -dataex-. For more info, type help dataex
clear
input int participant_ID byte(age gender education) float amphetamines_past30m
180 52 2 3 0
 38 67 2 4 0
266 63 1 4 1
184 55 2 2 0
 32 65 1 1 0
190 52 2 1 1
244 68 2 3 0
303 55 1 2 0
211 62 2 1 0
196 60 1 2 0
174 46 2 4 0
 51 57 2 3 0
 52 35 2 2 0
309 36 1 2 1
127 53 1 3 0
 59 60 2 3 0
 94 60 2 3 0
274 58 1 4 0
246 43 2 2 1
 67 65 2 1 1
  6 59 2 2 0
187 60 1 1 1
258 73 2 3 0
 42 59 2 2 0
 96 59 1 1 0
 69 67 2 2 0
end

This is an example of the column format I would like to achieve for the mean/sd and n/%.




Thanks in advance,
Mark

[Jeff Pitblado (StataCorp)]

Thanks for providing some data and your call to dtable.

You will need to use collect to define a composite result for
each pair of statistics you want to stack in a column. Here is how I
accomplished this.

clear all

* Example generated by -dataex-. For more info, type help dataex
input int participant_ID byte(age gender education) float amphetamines_past30m
180 52 2 3 0
 38 67 2 4 0
266 63 1 4 1
184 55 2 2 0
 32 65 1 1 0
190 52 2 1 1
244 68 2 3 0
303 55 1 2 0
211 62 2 1 0
196 60 1 2 0
174 46 2 4 0
 51 57 2 3 0
 52 35 2 2 0
309 36 1 2 1
127 53 1 3 0
 59 60 2 3 0
 94 60 2 3 0
274 58 1 4 0
246 43 2 2 1
 67 65 2 1 1
  6 59 2 2 0
187 60 1 1 1
258 73 2 3 0
 42 59 2 2 0
 96 59 1 1 0
 69 67 2 2 0
end

dtable c.age i.gender i.education, ///
    by(amphetamines_past30m, nototals tests) ///
    sample(, statistics(freq) place(seplabels)) ///
    sformat("(N=%s)" frequency) ///
    note(Total sample: N = 344) ///
    nformat(%7.2f mean sd) ///
    title(Table 1. Demographics)

* peek at the levels of dimension result
collect levels result
* define custom composite results to split the statistics into 2 columns
collect composite define col1 = mean fvfrequency
collect composite define col2 = sd fvpercent
collect label levels result ///
    col1 "N/mean" ///
    col2 "sd/%" ///
    , modify
* replace the result autolevels with our new custom columns, but keep
* the test column
collect style autolevels result col1 col2 _dtable_test, clear
* turn level labels on for our composite results in the column header
collect style header result[col1 col2], title(hide) level(label)
* put result last in the column spec so that sample sizes in
* _dtable_sample_dim show up once
collect layout (var) (amphetamines_past30m#_dtable_sample_dim#result)

* polish some variable labels for our table
collect label levels var ///
    age "Age [mean(sd)]" ///
    gender "Gender [N(%)]" ///
    education "Education [N(%)]" ///
    , modify
collect preview

* publish our table to MS Excel 
collect export table1.xlsx, replace

Here is the resulting table.

. collect preview

Table 1. Demographics
----------------------------------------------------
                         amphetamines_past30m
                        0              1        Test
                     (N=20)          (N=6)
                 N/mean   sd/%  N/mean   sd/%
----------------------------------------------------
Age [mean(sd)]    58.50  (8.32)  53.17 (11.69) 0.221
Gender [N(%)]
  1                   6 (30.0%)      3 (50.0%) 0.366
  2                  14 (70.0%)      3 (50.0%)
Education [N(%)]
  1                   3 (15.0%)      3 (50.0%) 0.207
  2                   7 (35.0%)      2 (33.3%)
  3                   7 (35.0%)      0  (0.0%)
  4                   3 (15.0%)      1 (16.7%)
----------------------------------------------------
Total sample: N = 344

Here is a screenshot of the exported spreadsheet in Libre Office.

[Mark Hawes]

Hello Jeff!

dtable combined with the collect code that you just provided has changed my life! I spent most of my Ph.D. training mind-numbingly typing massive amounts of data into an Excel spreadsheet. This is life-changing . I really appreciate you answering my post and providing this solution.

All the best,

Mark