Combining table with both column and row percentages

[Nina Fajs]

Hi all,

I am relatively new to Stata and am somewhat stuck on an data visualisation issue. I am trying to make a descriptive table with colums of Total (column percentage) and category levels (row percentage), and rows of variables (and variable categories), but I cannot figure out how to combine those two (I either get one or the other in the output).

The code is something like this, using examples from previopus forum posts (this is not complete and I put *** where I tried to merge the two tables from the for loops):

webuse nhanes2l

unab vlist : smsa sex race hlthstat heartatk diabetes highlead
    
* build the overall sample table
table () (rural), ///
    stat(frequency) ///
    stat(percent, across(rural)) ///
    name(N)
collect addtags N[_hide]
collect layout (N) (rural#result)

*loop 1 — generate (column percent)
foreach v1 of local vlist {
    table (`v'), ///
        stat(frequency) ///
        stat(percent) ///
        totals(`v') ///
        name(tot_`v')
}

* loop 2 — generate row percent and p-value
foreach v2 of local vlist {
    table (`v') (rural), ///
        stat(frequency) ///
        stat(percent, across(rural)) ///
        nototals ///
        name(`v')

    * p-value
    quietly tabulate `v' rural if in_model_sample == 1, chi2
    collect get p = (r(p)), name(`v') tags(`v'[1] rural[Test])

}

***

* table format
collect composite define stats = frequency percent
collect style cell result[percent], nformat("%6.2f") sformat("(%s%%)")
collect style cell result[p], nformat("%5.3f") minimum(.001)
collect style header result[stats p], title(hide) level(hide)

* Test to the end
collect levels rural
local levels = s(levels)
local levels : subinstr local levels "Test" ""
collect style autolevels rural `levels' Test, clear

* layout
collect layout (N `vlist') (rural#result[stats])

The output would be something like this (this was manually done):

Thank you!

[Andrew Musau]

Thanks for the reproducible example. Note that dtable provides the desired output, so there's no need to program this using table. Here's a way to obtain appended tables:

webuse nhanes2l, clear
collect clear
local vars smsa race sex hlthstat heartatk diabetes highlead
foreach var of local vars{
    dtable i.`var', by(rural, test) name(`var')
}
collect combine all= `vars'
collect levelsof across
collect layout (var#result) (`s(levels)')

Res.:

. collect layout (var#result) (`s(levels)')
(dimension _hide not found)

Collection: all
      Rows: var#result
   Columns: _hide rural
   Table 1: 33 x 4

-------------------------------------------------------------------------
                                               Rural                     
                             Urban         Rural         Total      Test 
-------------------------------------------------------------------------
Diabetes status                                                          
  Not diabetic           6,233 (95.2%) 3,617 (95.1%) 9,850 (95.2%)       
                                                                    0.873
  Diabetic                  314 (4.8%)    185 (4.9%)    499 (4.8%)       
Prior heart attack                                                       
  No heart attack        6,272 (95.8%) 3,601 (94.7%) 9,873 (95.4%)       
                                                                    0.011
  Had heart attack          275 (4.2%)    201 (5.3%)    476 (4.6%)       
High lead level                                                          
  lead<25                2,902 (93.3%) 1,753 (95.4%) 4,655 (94.1%)       
                                                                    0.002
  lead>=25                  209 (6.7%)     84 (4.6%)    293 (5.9%)       
Health status                                                            
  Excellent              1,609 (24.6%)   798 (21.0%) 2,407 (23.3%)       
                                                                   <0.001
  Very good              1,713 (26.2%)   878 (23.1%) 2,591 (25.1%)       
  Good                   1,878 (28.7%) 1,060 (27.9%) 2,938 (28.4%)       
  Fair                     950 (14.5%)   720 (19.0%) 1,670 (16.2%)       
  Poor                      389 (5.9%)    340 (9.0%)    729 (7.1%)       
Race                                                                     
  White                  5,419 (82.8%) 3,646 (95.9%) 9,065 (87.6%)       
                                                                   <0.001
  Black                    968 (14.8%)    118 (3.1%) 1,086 (10.5%)       
  Other                     161 (2.5%)     39 (1.0%)    200 (1.9%)       
Sex                                                                      
  Male                   3,023 (46.2%) 1,892 (49.8%) 4,915 (47.5%)       
                                                                   <0.001
  Female                 3,525 (53.8%) 1,911 (50.2%) 5,436 (52.5%)       
SMSA type                                                                
  SMSA, central city     2,629 (40.1%)      3 (0.1%) 2,632 (25.4%)       
                                                                   <0.001
  SMSA, not central city 2,360 (36.0%)   648 (17.0%) 3,008 (29.1%)       
  Not in an SMSA         1,559 (23.8%) 3,152 (82.9%) 4,711 (45.5%)       
-------------------------------------------------------------------------

[Andrew Musau]

I realized that #2 does not achieve what is wanted in #1:

I am trying to make a descriptive table with colums of Total (column percentage) and category levels (row percentage), and rows of variables (and variable categories), but I cannot figure out how to combine those two (I either get one or the other in the output).

For context, Jeff Pitblado (StataCorp)'s post in https://www.statalist.org/forums/for...ow-percentages creates a table similar to dtable, but with row percentages across a by variable instead of column percentages. Combining this and dtable can produce the desired outcome.

webuse nhanes2l, clear
collect clear
unab vlist : smsa sex race hlthstat heartatk diabetes highlead

* build the overall sample table
table () (rural), ///
    stat(frequency) ///
    stat(percent, across(rural)) ///
    name(N) nototal
collect addtags N[_hide]
collect layout (N) (rural#result)

* loop over the factor variables to compute their "row" percentages
* across the levels of -rural-
foreach v of local vlist {
    table (`v') (rural), ///
        stat(frequency) ///
        stat(percent, across(rural)) ///
        nototal ///
        name(`v')
}

* create a new collection named 'all' that combines the above collections
collect combine all = N `vlist'


* add formats for the percent like -dtable- does
collect style cell result[percent], nformat("%6.2f") sformat("(%s%%)")

* hide the title and level labels in the header for our composite result
collect style header result, title(hide) level(hide)

* new layout putting all the factor variables in the rows
collect layout (N `vlist') (rural#result)

*DTABLE + COMBINING TABLES

dtable i.(`vlist'), by(rural, tests)
collect remap rural[0]= rural[2], name(DTable)
collect remap rural[1]= rural[3], name(DTable)
collect combine wanted= all DTable
collect layout (`vlist') (rural[0 1 .m _dtable_test]#result[ _dtable_stats _dtable_test])

Res.:

. collect layout (`vlist') (rural[0 1 .m _dtable_test]#result[ _dtable_stats _dtable_test])

Collection: wanted
      Rows: smsa sex race hlthstat heartatk diabetes highlead
   Columns: rural[0 1 .m _dtable_test]#result[ _dtable_stats _dtable_test]
   Table 1: 26 x 4

-------------------------------------------------------------------------------------
                         |                            Rural                          
                         |           Urban            Rural            Total     Test
-------------------------+-----------------------------------------------------------
SMSA type                |                                                          
  SMSA, central city     |  2,629 (99.89%)        3 (0.11%)   2,632 (25.43%)   <0.001
  SMSA, not central city |  2,360 (78.46%)     648 (21.54%)   3,008 (29.06%)        
  Not in an SMSA         |  1,559 (33.09%)   3,152 (66.91%)   4,711 (45.51%)        
Sex                      |                                                          
  Male                   |  3,023 (61.51%)   1,892 (38.49%)   4,915 (47.48%)   <0.001
  Female                 |  3,525 (64.85%)   1,911 (35.15%)   5,436 (52.52%)        
Race                     |                                                          
  White                  |  5,419 (59.78%)   3,646 (40.22%)   9,065 (87.58%)   <0.001
  Black                  |    968 (89.13%)     118 (10.87%)   1,086 (10.49%)        
  Other                  |    161 (80.50%)      39 (19.50%)      200 (1.93%)        
Health status            |                                                          
  Excellent              |  1,609 (66.85%)     798 (33.15%)   2,407 (23.29%)   <0.001
  Very good              |  1,713 (66.11%)     878 (33.89%)   2,591 (25.07%)        
  Good                   |  1,878 (63.92%)   1,060 (36.08%)   2,938 (28.43%)        
  Fair                   |    950 (56.89%)     720 (43.11%)   1,670 (16.16%)        
  Poor                   |    389 (53.36%)     340 (46.64%)      729 (7.05%)        
Prior heart attack       |                                                          
  No heart attack        |  6,272 (63.53%)   3,601 (36.47%)   9,873 (95.40%)    0.011
  Had heart attack       |    275 (57.77%)     201 (42.23%)      476 (4.60%)        
Diabetes status          |                                                          
  Not diabetic           |  6,233 (63.28%)   3,617 (36.72%)   9,850 (95.18%)    0.873
  Diabetic               |    314 (62.93%)     185 (37.07%)      499 (4.82%)        
High lead level          |                                                          
  lead<25                |  2,902 (62.34%)   1,753 (37.66%)   4,655 (94.08%)    0.002
  lead>=25               |    209 (71.33%)      84 (28.67%)      293 (5.92%)        
-------------------------------------------------------------------------------------

[Nina Fajs]

Thank you so much! I was looking into the dtable, but didn't know you can combine them. Much appreciated!