dtable formatting

[Salome-Joelle Gass]

Good morning,

I am trying to figure out some formatting for dtable using the following code (I am not able to share the data): dtable, by(group, tests testnotes) sample(, statistic(frequency proportion)) factor(agecat gender race rural payment_type alcohol substance_use employment exposure_local exposure_inttravel exposure_foreign HIV asthma malnutrition dayreadmission ICU mortality_inpatient, statistics(fvfrequency fvpercent)) nformat(%9.2f percent fvpercent) export(StrongyPhaseIResults.xlsx, replace)

This gives me this result:

---------------------------------------------------------------------------------
Strongyloides Test Result
Controls Positives Total Test
---------------------------------------------------------------------------------
N 37 0.587 26 0.413 63 1.000
Age
<18 5 (13.51%) 4 (15.38%) 9 (14.29%) 0.747
18-29 5 (13.51%) 6 (23.08%) 11 (17.46%)
30-49 9 (24.32%) 4 (15.38%) 13 (20.63%)
50-69 13 (35.14%) 10 (38.46%) 23 (36.51%)
70+ 5 (13.51%) 2 (7.69%) 7 (11.11%)
Gender
Male 18 (48.65%) 16 (61.54%) 34 (53.97%) 0.312
Female 19 (51.35%) 10 (38.46%) 29 (46.03%)
Race
White 21 (56.76%) 14 (53.85%) 35 (55.56%) 0.121
Black 3 (8.11%) 4 (15.38%) 7 (11.11%)
Hispanic 5 (13.51%) 5 (19.23%) 10 (15.87%)
Asian 8 (21.62%) 1 (3.85%) 9 (14.29%)
Not listed 0 (0.00%) 2 (7.69%) 2 (3.17%)
Area of Residence
Urban 34 (91.89%) 23 (88.46%) 57 (90.48%) 0.648
Rural 3 (8.11%) 3 (11.54%) 6 (9.52%)
Insurance Status
Private 11 (29.73%) 5 (19.23%) 16 (25.40%) 0.413
Public 21 (56.76%) 19 (73.08%) 40 (63.49%)
Not insured 5 (13.51%) 2 (7.69%) 7 (11.11%)
Alcohol Use
No 27 (72.97%) 24 (92.31%) 51 (80.95%) 0.054
Yes 10 (27.03%) 2 (7.69%) 12 (19.05%)
Substance Use
No 31 (86.11%) 25 (96.15%) 56 (90.32%) 0.187
Yes 5 (13.89%) 1 (3.85%) 6 (9.68%)
Employment Status
Employed 11 (29.73%) 6 (23.08%) 17 (26.98%) 0.272
Unemployed 14 (37.84%) 9 (34.62%) 23 (36.51%)
Retired 8 (21.62%) 5 (19.23%) 13 (20.63%)
Disabled 3 (8.11%) 1 (3.85%) 4 (6.35%)
Unknown 1 (2.70%) 5 (19.23%) 6 (9.52%)
Local Exposure
No 37 (100.00%) 20 (76.92%) 57 (90.48%) 0.002
Yes 0 (0.00%) 6 (23.08%) 6 (9.52%)
Travel Exposure
No 31 (83.78%) 23 (88.46%) 54 (85.71%) 0.601
Yes 6 (16.22%) 3 (11.54%) 9 (14.29%)
exposure_foreign
No 26 (70.27%) 18 (69.23%) 44 (69.84%) 0.929
Yes 11 (29.73%) 8 (30.77%) 19 (30.16%)
Living with HIV
No 34 (91.89%) 25 (96.15%) 59 (93.65%) 0.495
Yes 3 (8.11%) 1 (3.85%) 4 (6.35%)
Asthma
No 20 (54.05%) 25 (96.15%) 45 (71.43%) <0.001
Yes 17 (45.95%) 1 (3.85%) 18 (28.57%)
Malnutrition
No 32 (86.49%) 26 (100.00%) 58 (92.06%) 0.051
Yes 5 (13.51%) 0 (0.00%) 5 (7.94%)
Readmitted within 30 days
No 32 (86.49%) 22 (84.62%) 54 (85.71%) 0.834
Yes 5 (13.51%) 4 (15.38%) 9 (14.29%)
Admitted to ICU
No 34 (94.44%) 25 (96.15%) 59 (95.16%) 0.757
Yes 2 (5.56%) 1 (3.85%) 3 (4.84%)
Mortality during inpatient admission
No 33 (89.19%) 23 (88.46%) 56 (88.89%) 0.928
Yes 4 (10.81%) 3 (11.54%) 7 (11.11%)
---------------------------------------------------------------------------------


Is it possible to (1) move the totals column so that it comes first before Controls and Positives? (2) Is it possible to separate variables and level names within the variables so that they are in separate columns?

[Devra Golbe]

You are more likely to get a useful response if you make your problem reproducible by using publicly available data and easily parsed code. Does this example (adapted from this excellent Stata blog post by Mia Lv) illustrate your question?

webuse idu

dtable, by(male, tests testnotes ) sample(, statistic(frequency proportion)) ///
factor(needle, statistics(fvfrequency fvpercent)) ///
 nformat(%9.2f percent fvpercent)

result

----------------------------------------------------------
                                   Male                  
                    No          Yes         Total     Test
----------------------------------------------------------
N                 76 0.068  1,048 0.932  1,124 1.000      
Shared needles                                            
  No           43 (56.58%) 679 (64.79%) 722 (64.23%) 0.149
  Yes          33 (43.42%) 369 (35.21%) 402 (35.77%)      
----------------------------------------------------------

[Jeff Pitblado (StataCorp)]

Using the example provided by Devra, here is how you can use
collect to change the order of the columns and split the row
headers so the factor variable names/labels show up in separate columns
in line with their first level.

webuse idu

dtable, ///
    by(male, test notestnotes) ///
    factor(needle) ///
    nformat(%9.2f percent fvpercent)

* get the autolevels of the -by()- variable; notice that .m is the level
* with the label "Total"
collect query autolevels male
* reset the autolevels of the -by()- variable, putting .m; duplicates are
* ignored
collect style autolevels male .m `s(levels)', clear

* split the row heads is separate columns for dimension/factors and their
* levels
collect style row split, dups(first)

* replay the table with these changes
collect preview

Here is the resulting table.

-------------------------------------------------------------------
                                         Male
                        Total           No           Yes       Test
-------------------------------------------------------------------
N                  1,124 (100.00%)  76 (6.76%) 1,048 (93.24%)
Shared needles No     722 (64.23%) 43 (56.58%)   679 (64.79%) 0.149
               Yes    402 (35.77%) 33 (43.42%)   369 (35.21%)
-------------------------------------------------------------------

[Salome-Joelle Gass]

Perfect, that worked great! Thank you so so much!