Appending tables using collect

[Chris Stefancik]

I'm reasonably sure I'm missing something very simple with this question. Basically, I have two tables with the same layout that I'd like to append to one another so I can show a comparison of the whole sample to a subset.

This is the result (or similar) I'm trying achieve.

Perhaps there is an easier way of accomplishing this task but I also wanted to work more with the table and collect commands to have some additional experience with them.

Please see the sample code below that I've tried:

webuse nhanes2, clear

// Create a table for the general sample in the dataset -- Table 1
table race (diabetes agegrp), statistic(median bpsystol) total(diabetes#race)

// Exclude all but those in "Poor" health (hlthstat == 5) and recreate the table above -- Table 2
table race (diabetes agegrp) if hlthstat==5, statistic(median bpsystol) total(diabetes#race) append

// Rename "race" for the "poor health" table
collect label levels var race "Poor Health", modify

// Set the new layout Table 1 first then Table 2
collect layout (race race) (result)

I've seen something similar work, however, the "Row" var was different: such as race in Table 1 and Sex in Table 2 with the result being an average of weight. I assumed the table () (), append command would do the trick but I believe that has different functionality than how I read the documentation.

Thanks in advance for the the assistance.

Chris.

[William Lisowski]

webuse nhanes2, clear

// Create a table for the general sample in the dataset -- Table 1
generate univ = 1
table (univ race) (diabetes agegrp), statistic(median bpsystol) total(univ#diabetes#race)

// Exclude all but those in "Poor" health (hlthstat == 5) and recreate the table above -- Table 2
replace univ = 2
table (univ race) (diabetes agegrp) if hlthstat==5, statistic(median bpsystol) total(univ#diabetes#race) append

collect label levels univ 1 "Race" 2 "Poor Health"
collect style header univ, title(hide)
collect style header race, title(hide)
collect preview

. collect preview

------------------------------------------------------------------------------------------------------------------------
            |                                               Diabetes status                                             
            |                      Not diabetic                                            Diabetic                     
            |                       Age group                                             Age group                     
            |  20–29   30–39   40–49   50–59   60–69   70+   Total   20–29   30–39   40–49   50–59   60–69   70+   Total
------------+-----------------------------------------------------------------------------------------------------------
Race        |                                                                                                           
  White     |    118     120     122     130     138   142     128     106     120     134     140     142   160     142
  Black     |    116     120     130     140     140   149     130     129     124     155     146     143   148     140
  Other     |    110     111     124     126   152.5   150     120                     170             130   195     160
Poor Health |                                                                                                           
  White     |    110     120     120     136     138   142     136     111     168     130     140     144   160     142
  Black     |    120     125     130     130     137   155     134                     149     146     140   148     146
  Other     |    100     116     134     115     162           120                     170                   170     170
------------------------------------------------------------------------------------------------------------------------

[Chris Stefancik]

Thank you William, that is a remarkable elegant solution!

Aside from the "collect label levels" command I attempted, do you know why my initial approach would not reproduce what your code can?

Chris.

[William Lisowski]

Because the "append" does not append formatted data, but rather the collection of cell values and their stubs - in your case, the values of race, diabetes, and agegrp. So now the collection has, for each combination of race, diabetes, and agegrp, two values for each cell. By adding the univ dimension, and changing its value between the two table commands, the collection has, for each combination of univ, race, diabetes, and agegrp, just one value for each cell.

There is probably a better way of accomplishing this than by adding an artificial dimension, but I haven't been able to find it.

[Giulia Vivaldi]

Brilliant solution. I finally managed to come up with one myself (pasted below), but it's definitely not as elegant.

The first time I used collect, I spent 95% of my time inspecting the results of collect dims and collect levelsof, which I'd recommend to you if you're not doing so already!

*Set up table 1
table race (diabetes agegrp), statistic(median bpsystol) total(diabetes#race)

*Set up and append table 2
table race (diabetes agegrp) if hlthstat==5, statistic(median bpsystol) total(diabetes#race) append

*Use 'collect dims' to see what separates the two sets of results: you'll see it is 'cmdset', which has two levels (1 and 2)

*Format table
collect label values cmdset 1 "Race" 2 "Poor health", modify //Change table names
collect style header cmdset, title(hide)    //Hide the row saying 'Command results index'
collect style header result, level(hide) //Hide the row saying 'Median'
collect style header race, title(hide)    //Hide header saying 'Race' in both tables

*Present table, subdividing the rows and columns
collect layout (cmdset#race) (diabetes#agegrp#result)

[William Lisowski]

That is far more elegant than my solution - no artificial dimension. I thought cmdset was the key, but didn't know how to make collect layout work. I tried to apply the table syntax with spaces rather than the collect syntax with #. Need to read the fine material more closely next time.