Combining Multiple Tables Using DTable Over Multiple Binary Dependent Variables (i.e. Outcomes) With Tests

[Kevin Blaine]

Hi all (though Jeff Pitblado (StataCorp) is probably the most appropriate person to help) -- how do I combine multiple DTables with the same predictors, but different outcomes -- while still retaining the PValues from the tests of each? I've spent hours sifting through this forum trying to use any combination of dtable, collect, collect combine, collect addtags, etc in order to produce the output I'm looking for. Ultimately, I want a table that looks like this, where "Hope" and "Fear" are binary variables and correspond to values from edu_hope[1] and edu_fear[1].

Table 1
-------------------------------------------------------------------------------
                        Total       Hope      PVal      Fear        Pval
                        (n=500)    (n=109)              (n=54)
-------------------------------------------------------------------------------
race_ethnicity                                        
  White               97 (19.4%) 21 (19.3%)   0.594    7 (13.0%)    0.502
  Black                15 (3.0%)   1 (0.9%)            1 (1.9%)
  Hispanic or Latino 325 (65.1%) 75 (68.8%)           39 (72.2%)
  Asian                30 (6.0%)   5 (4.6%)            2 (3.7%)
  Other                32 (6.4%)   7 (6.4%)            5 (9.3%)
-------------------------------------------------------------------------------

See my dataex snippet below for data that corresponds to the table above.

* Example generated by -dataex-. For more info, type help dataex
clear
input float race_ethnicity byte(gender edu_hope edu_fear)
3 2 0 0
1 2 0 0
4 1 1 1
3 1 0 0
3 1 0 0
1 2 0 0
3 2 0 1
3 2 0 0
1 2 0 0
3 2 0 0
1 2 0 0
4 2 0 0
3 1 0 0
1 2 1 0
3 2 0 0
1 1 0 0
3 1 1 0
5 1 0 0
3 1 0 1
3 1 0 0
1 2 0 0
5 1 0 0
1 1 0 0
3 1 0 0
3 1 1 0
3 2 0 0
1 1 1 0
3 2 0 0
3 2 0 0
3 1 0 0
5 2 0 0
3 2 0 0
3 1 0 0
3 1 1 0
5 1 0 1
3 1 0 0
3 1 0 0
3 2 0 0
1 2 0 0
3 2 0 0
3 2 0 0
3 2 1 0
3 2 0 0
5 1 1 1
3 2 0 0
3 2 0 0
1 2 0 0
3 2 0 0
1 2 0 0
3 1 0 0
1 1 1 0
3 2 0 0
3 2 0 0
1 2 0 0
4 1 0 0
3 1 1 1
3 1 0 0
3 1 0 0
3 1 0 0
3 1 0 0
5 2 0 0
3 2 0 0
4 1 1 0
4 1 0 0
4 1 0 0
2 2 0 0
3 2 1 0
3 1 1 1
3 1 0 0
3 2 0 0
1 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
3 2 0 0
3 1 1 1
3 1 1 0
1 1 0 0
3 2 0 0
1 1 0 0
3 2 0 0
5 1 0 0
3 2 0 0
3 1 1 0
4 2 0 0
4 2 0 0
3 1 0 0
2 1 0 0
1 1 0 0
3 2 0 0
3 2 0 0
3 1 0 0
1 2 1 0
3 1 0 0
3 1 1 1
3 1 0 0
3 2 0 0
5 2 0 0
3 1 0 0
3 2 1 0
5 1 0 0
3 2 0 0
3 1 1 1
3 2 0 0
3 2 0 0
3 1 0 0
4 1 0 0
4 2 0 0
4 1 0 0
5 2 0 1
3 2 0 0
3 1 1 0
3 1 1 0
3 1 0 0
1 2 0 0
1 1 0 0
3 2 0 1
5 1 0 0
3 1 0 0
3 2 0 0
3 2 0 0
3 1 0 0
3 2 0 0
1 1 0 0
5 1 0 0
3 1 0 0
3 2 0 0
1 1 0 0
3 1 1 1
1 2 0 0
3 1 0 0
3 2 0 0
1 1 0 0
4 2 0 0
5 2 0 0
3 2 0 0
3 1 0 0
3 2 0 0
1 1 0 1
3 2 0 0
1 1 1 0
4 2 0 0
4 2 1 1
3 2 0 0
3 1 0 0
2 2 0 0
3 2 1 0
3 1 0 0
3 1 1 1
3 2 0 0
3 2 0 0
3 1 0 0
1 1 0 0
3 2 0 0
3 1 0 0
3 1 0 0
5 2 0 0
3 2 0 0
3 2 0 1
3 2 1 0
5 2 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 1 1 0
4 1 0 0
1 1 1 1
1 2 0 0
5 1 0 0
3 1 0 0
4 1 0 0
3 2 0 0
1 2 0 0
4 1 0 0
3 2 0 0
2 2 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
3 2 0 1
5 1 0 0
2 1 0 0
3 1 1 0
5 2 1 0
3 1 1 1
3 2 0 0
3 1 1 0
3 2 0 0
3 2 0 1
3 1 0 1
3 2 0 0
3 1 1 0
2 2 0 0
4 2 0 0
1 1 0 0
3 1 1 0
3 2 0 0
3 2 0 0
3 1 0 0
1 1 1 0
3 1 0 0
4 1 0 0
1 2 0 0
3 2 0 0
3 2 0 0
2 2 1 1
1 2 0 0
3 1 1 0
1 1 0 0
5 1 1 0
5 1 0 1
2 2 0 0
3 2 0 0
3 2 0 0
3 1 0 0
3 2 0 0
1 2 0 0
3 1 1 0
3 2 0 0
4 2 0 0
3 1 0 0
5 2 0 0
3 1 1 1
3 1 0 0
1 2 0 0
3 1 0 0
3 2 1 1
3 1 0 0
3 1 0 0
1 1 0 0
1 2 0 0
3 1 0 1
3 2 0 0
1 2 0 0
2 2 0 0
3 2 1 0
3 2 1 1
3 1 0 0
3 1 0 0
3 1 1 1
1 1 0 0
3 1 1 1
1 1 0 0
1 2 1 0
3 2 0 0
1 2 0 0
3 2 0 0
3 2 0 0
3 1 0 0
4 1 0 0
3 1 0 0
2 1 0 0
3 2 0 1
3 2 1 1
3 2 1 0
1 1 0 0
3 1 1 0
4 1 0 0
3 1 0 0
1 1 1 0
3 1 0 0
1 2 0 0
2 2 0 0
3 1 0 0
3 1 0 0
3 1 0 0
2 2 0 0
1 2 0 0
3 2 0 0
1 1 1 0
1 2 0 0
1 1 0 0
4 1 0 0
3 2 0 0
3 1 0 0
. 1 0 0
3 1 0 0
3 1 1 1
3 2 0 0
3 1 0 0
3 2 1 0
3 1 0 0
1 1 1 0
3 1 1 0
3 1 0 0
3 2 1 1
3 1 0 0
3 1 0 0
3 2 1 1
3 2 0 0
5 2 0 0
3 1 1 1
3 2 1 0
1 1 0 0
3 2 0 0
2 2 0 0
3 1 0 0
3 1 0 0
3 2 0 0
3 1 0 0
3 1 0 1
2 1 0 0
1 1 0 0
4 1 1 0
3 1 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
3 2 0 0
3 1 0 0
3 2 0 0
1 1 0 0
3 1 0 0
3 2 0 0
3 2 1 0
3 1 0 0
5 2 1 0
3 1 0 0
1 1 0 0
1 2 0 0
4 1 1 0
5 2 0 0
3 2 0 0
3 2 0 0
5 2 0 0
3 1 0 0
3 1 1 1
3 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
5 2 1 0
3 1 0 0
3 2 0 0
3 1 0 1
1 2 0 0
3 1 0 0
4 1 0 0
1 1 0 0
3 1 1 1
3 1 1 0
3 1 1 0
3 2 0 0
5 2 1 0
1 1 0 0
5 1 0 0
3 1 1 0
3 1 1 1
3 2 0 0
3 1 0 0
3 2 1 0
1 1 1 1
5 2 0 0
1 1 1 0
1 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
3 1 0 0
3 1 1 0
1 2 0 0
3 1 0 0
5 1 0 0
3 2 0 0
3 2 0 0
3 1 0 0
3 1 0 0
1 1 0 0
3 2 1 0
1 2 0 0
1 2 0 0
3 2 0 0
1 1 1 0
1 2 0 0
3 1 1 0
1 1 0 0
1 1 0 1
2 1 0 0
3 2 0 0
1 1 1 0
1 1 0 0
1 2 1 1
3 1 0 0
3 2 1 0
3 1 1 0
3 1 1 0
1 1 1 0
3 2 0 0
4 1 0 0
1 2 0 0
3 1 0 0
3 2 1 0
1 1 0 0
3 1 0 0
3 2 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
1 2 1 0
3 2 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 2 1 1
3 1 0 0
3 2 0 0
1 2 0 0
1 2 0 0
3 1 0 0
1 1 0 0
5 2 1 0
3 2 0 0
1 1 0 0
3 2 0 0
3 2 0 0
3 1 0 0
4 1 0 0
3 1 0 0
3 2 0 0
3 2 0 0
3 2 0 0
1 2 0 0
3 2 0 0
3 1 0 0
3 2 0 0
3 1 0 0
3 1 0 0
1 2 0 0
3 1 0 0
3 1 0 0
1 2 0 1
1 1 0 0
1 1 0 0
3 1 0 0
3 1 0 0
3 1 0 0
3 2 0 0
3 1 0 0
3 1 1 0
3 2 0 0
3 1 0 0
3 2 1 0
3 1 0 0
3 1 0 0
4 2 0 0
3 1 0 0
3 1 1 0
3 1 1 1
3 1 1 0
3 2 1 0
3 2 1 0
1 1 0 0
3 2 0 0
3 2 0 0
3 2 0 0
3 2 0 0
3 1 1 0
3 1 0 0
3 2 0 0
3 2 1 1
3 2 0 0
3 2 0 0
3 2 0 0
3 2 0 0
3 1 0 0
3 2 0 0
1 1 0 0
3 2 0 1
1 1 0 0
1 1 1 0
3 2 0 0
3 2 0 0
3 1 1 0
3 1 0 0
3 1 0 1
4 2 0 0
3 1 0 0
3 2 0 0
3 1 0 0
3 1 0 0
3 2 1 0
1 2 1 0
1 1 1 0
3 2 0 0
1 2 0 0
3 1 0 0
3 2 1 1
1 1 0 1
3 1 1 1
3 2 1 0
3 1 1 0
3 1 1 0
1 1 0 0
3 2 0 0
5 2 0 1
end
label values race_ethnicity la_race_ethnicity
label def la_race_ethnicity 1 "White", modify
label def la_race_ethnicity 2 "Black", modify
label def la_race_ethnicity 3 "Hispanic or Latino", modify
label def la_race_ethnicity 4 "Asian", modify
label def la_race_ethnicity 5 "Other", modify
label values gender gender
label def gender 1 "Female", modify
label def gender 2 "Male", modify
label values edu_hope yesno
label values edu_fear yesno
label def yesno 0 "No", modify
label def yesno 1 "Yes", modify

Now, I can get ALMOST there following some advice from a different thread:

collect addtag group[Total]
dtable i.race_ethnicity if edu_hope==1, name(Hope)
collect addtag group[Hope]
dtable i.race_ethnicity if edu_fear==1, name(Fear)
collect addtag group[Fear]
collect combine comb = Total Hope Fear
collect style autolevels group Total Hope Fear
collect style header result[_dtable_stats], title(hide) level(hide)
collect layout (var) (group#result)

Table 1
------------------------------------------------------
                        Total       Hope       Fear  
------------------------------------------------------
race_ethnicity                                        
  White               97 (19.4%) 21 (19.3%)  7 (13.0%)
  Black                15 (3.0%)   1 (0.9%)   1 (1.9%)
  Hispanic or Latino 325 (65.1%) 75 (68.8%) 39 (72.2%)
  Asian                30 (6.0%)   5 (4.6%)   2 (3.7%)
  Other                32 (6.4%)   7 (6.4%)   5 (9.3%)
------------------------------------------------------

But I can't figure out how to use collect and collect addtags for both the p-value portion of the combined collection, as well as the "eligible" sample for each group, both of which I know I can get if I do the following:

dtable i.race_ethnicity, by(edu_hope)
dtable i.race_ethnicity, by(edu_fear)

Any advice?

[Jeff Pitblado (StataCorp)]

It seems you want a single column for the "true/yes" level of each indicator/binary by() variable, but also including their p-value from the test of independence. In addition, you want the total sample statistics in the first column.

In Stata 19, with the addition of collect unget command this is easily accomplished.

Here is what I came up with.

dtable i.race_ethnicity, name(Total) nosample

dtable i.race_ethnicity, name(Hope) nosample by(edu_hope, test nototal)
collect unget fvfrequency fvpercent, fortags(edu_hope[1])

dtable i.race_ethnicity, name(Fear) nosample by(edu_fear, test nototal)
collect unget fvfrequency fvpercent, fortags(edu_fear[1])

collect combine all = Total Hope Fear
collect style header result[_dtable_stats], level(hide)

collect layout (var) (collection#result)

Here is the resulting table.

--------------------------------------------------------------------
                        Total           Hope              Fear
--------------------------------------------------------------------
race_ethnicity
  White               97 (19.4%)  76 (19.5%) 0.594  90 (20.2%) 0.502
  Black                15 (3.0%)   14 (3.6%)         14 (3.1%)
  Hispanic or Latino 325 (65.1%) 250 (64.1%)       286 (64.3%)
  Asian                30 (6.0%)   25 (6.4%)         28 (6.3%)
  Other                32 (6.4%)   25 (6.4%)         27 (6.1%)
--------------------------------------------------------------------

If you want the p-values to show up in the same column as the other statistics, but in the row above the levels of the row variable, try the following.

* retag the p-value results
collect addtags race_ethnicity[_hide], fortags(result[pearson]) replace

* put the new _hide level first
collect levels race_ethnicity
collect style autolevels race_ethnicity _hide `s(levels)', clear
* make -collect- show the title for this dimension
collect style header race_ethnicity, title(label)

* define our own composite result to include all the results of interest
collect composite define ///
    mystats = pearson fvfrequency frequency fvpercent percent, trim
collect style autolevels result mystats, clear

* update the row specification in the layout
collect layout (race_ethnicity) (collection#result)

Here is the resulting table.

--------------------------------------------------------
                        Total        Hope        Fear
--------------------------------------------------------
race_ethnicity                         0.594       0.502
  White               97 (19.4%)  76 (19.5%)  90 (20.2%)
  Black                15 (3.0%)   14 (3.6%)   14 (3.1%)
  Hispanic or Latino 325 (65.1%) 250 (64.1%) 286 (64.3%)
  Asian                30 (6.0%)   25 (6.4%)   28 (6.3%)
  Other                32 (6.4%)   25 (6.4%)   27 (6.1%)
--------------------------------------------------------

[Kevin Blaine]

That's exactly what I was hoping for. Thanks Jeff!

Also, I'm having trouble getting the _dtable_sample_dim results for the Total, Hope, and Fear columns to populate up top. When I collect combine, it seems as though only a single _dtable_sample_dim stays attached to the combined collection, despite trying to collect addtags after each dtable command. (FYI -- I want to have the n=xx (%) for when edu_hope==1 and edu_fear==1, for each column.

P.S. Great webinar this morning.

[Jeff Pitblado (StataCorp)]

The dimension _dtable_sample_dim is created by dtable when you specify option sample(, place(seplabel)). It is part of some subtle trickery to get the sample size and percentages into the column headers. Here is what is needed to keep those headers in the combined collection, my changes from the above example are highlighted in blue.

dtable i.race_ethnicity, name(Total) sample(, place(seplabel))

dtable i.race_ethnicity, name(Hope) sample(, place(seplabel)) by(edu_hope, test nototal)
collect unget fvfrequency fvpercent, fortags(edu_hope[1])

dtable i.race_ethnicity, name(Fear) sample(, place(seplabel)) by(edu_fear, test nototal)
collect unget fvfrequency fvpercent, fortags(edu_fear[1])

collect combine all = Total Hope Fear
collect style header result[_dtable_stats], level(hide)

collect layout (var) (collection#result#_dtable_sample_dim)

* retag the p-value results
collect addtags race_ethnicity[_hide], fortags(result[pearson]) replace
collect addtags _dtable_sample_dim[0], fortags(result[pearson]) replace

* put the new _hide level first
collect levels race_ethnicity
collect style autolevels race_ethnicity _hide `s(levels)', clear
* make -collect- show the title for this dimension
collect style header race_ethnicity, title(label)

* define our own composite result to include all the results of interest
collect composite define ///
    mystats = pearson fvfrequency frequency fvpercent percent, trim
collect style autolevels result mystats, clear

*collect style header _dtable_sample_dim, title(name) level(value)
*collect style header result, title(name) level(value)

* update the row specification in the layout
collect layout (race_ethnicity) (collection#result#_dtable_sample_dim)

Here is the first table.

--------------------------------------------------------------------
                        Total           Hope              Fear
                         500     391 (78.2%)       391 (78.2%)
--------------------------------------------------------------------
race_ethnicity
  White               97 (19.4%)  76 (19.5%) 0.594  90 (20.2%) 0.502
  Black                15 (3.0%)   14 (3.6%)         14 (3.1%)
  Hispanic or Latino 325 (65.1%) 250 (64.1%)       286 (64.3%)
  Asian                30 (6.0%)   25 (6.4%)         28 (6.3%)
  Other                32 (6.4%)   25 (6.4%)         27 (6.1%)
--------------------------------------------------------------------

Here is the second table.

--------------------------------------------------------
                        Total        Hope        Fear
                         500     391 (78.2%) 391 (78.2%)
--------------------------------------------------------
race_ethnicity                         0.594       0.502
  White               97 (19.4%)  76 (19.5%)  90 (20.2%)
  Black                15 (3.0%)   14 (3.6%)   14 (3.1%)
  Hispanic or Latino 325 (65.1%) 250 (64.1%) 286 (64.3%)
  Asian                30 (6.0%)   25 (6.4%)   28 (6.3%)
  Other                32 (6.4%)   25 (6.4%)   27 (6.1%)
--------------------------------------------------------

[Kevin Blaine]

So, the n's in the sample dimension aren't quite doing what I'm hoping for them to do. So far, they are showing the entire sample, specifically for the "No" of the edu_hope variable, and it seems to be duplicated across both headers. What I'm looking for is for the edu_hope[1] sample to be (n=109, 21.8%) and the edu_fear[1] sample to be (n=54, 10.8%). In this way, we can see that 10% of the whole sample has expressed a fear, while 21.8% have expressed a hope, both as they pertain to the domain of Education.

Happy to clarify further. Thanks for bearing with me!

- Kevin

[Jeff Pitblado (StataCorp)]

Sorry, I should have paid closer attention to the seplabels and values reported in the final tables.

The correct solution is more involved:

• dtable assigns levels to _dtable_sample_dim based on the levels (or absence) of the by() variable. So we need to find/copy the seplabels within each collection and build a new selector dimension with unique levels (and seplabel them) so they combine without conflicts.
• Be careful about tagging the p-values with our new selector dimension.
• Replace _dtable_sample_dim with our new selector dimension.
• We do not need to use collect unget because our new selector dimension will target/tag only the items we want to see.

I'll name the new selector dimension mysel, and use the collection names as its levels.

dtable i.race_ethnicity, name(Total) sample(, place(seplabel))
* find seplabel and tag/select items
collect label list _dtable_sample_dim, all
sreturn list
local hlab = `"`s(label1)'"'
collect addtags mysel[Total], fortags(_dtable_sample_dim[_dtable_stats])
collect label levels mysel Total `"`hlab'"'

dtable i.race_ethnicity, name(Hope) sample(, place(seplabel)) by(edu_hope, test nototal)
* find seplabel and tag/select items
collect label list _dtable_sample_dim, all
sreturn list
local hlab = `"`s(label3)'"'
collect addtags mysel[Hope], fortags(_dtable_sample_dim[1])
collect label levels mysel Hope `"`hlab'"'
collect addtags mysel[_hide], fortags(result[pearson])

dtable i.race_ethnicity, name(Fear) sample(, place(seplabel)) by(edu_fear, test nototal)
* find seplabel and tag/select items
collect label list _dtable_sample_dim, all
sreturn list
local hlab = `"`s(label3)'"'
collect addtags mysel[Fear], fortags(_dtable_sample_dim[1])
collect label levels mysel Fear `"`hlab'"'
collect addtags mysel[_hide], fortags(result[pearson])

collect combine all = Total Hope Fear
collect style header result[_dtable_stats], level(hide)

collect layout (var) (collection#result#mysel)

* retag the p-value results
collect addtags race_ethnicity[_hide], fortags(result[pearson]) replace

* put the new _hide level first
collect levels race_ethnicity
collect style autolevels race_ethnicity _hide `s(levels)', clear
* make -collect- show the title for this dimension
collect style header race_ethnicity, title(label)

* define our own composite result to include all the results of interest
collect composite define ///
    mystats = pearson fvfrequency frequency fvpercent percent, trim
collect style autolevels result mystats, clear

* fix tags on p-values
collect addtags mysel[Hope], fortags(result[pearson]#collection[Hope]) replace
collect addtags mysel[Fear], fortags(result[pearson]#collection[Fear]) replace

* update the row specification in the layout
collect layout (race_ethnicity) (collection#result#mysel)

Here is the first table.

-------------------------------------------------------------------
                        Total           Hope             Fear
                         500     109 (21.8%)       54 (10.8%)
-------------------------------------------------------------------
race_ethnicity
  White               97 (19.4%)  21 (19.3%) 0.594  7 (13.0%) 0.502
  Black                15 (3.0%)    1 (0.9%)         1 (1.9%)
  Hispanic or Latino 325 (65.1%)  75 (68.8%)       39 (72.2%)
  Asian                30 (6.0%)    5 (4.6%)         2 (3.7%)
  Other                32 (6.4%)    7 (6.4%)         5 (9.3%)
-------------------------------------------------------------------

Here is the second table.

-------------------------------------------------------
                        Total        Hope       Fear
                         500     109 (21.8%) 54 (10.8%)
-------------------------------------------------------
race_ethnicity                         0.594      0.502
  White               97 (19.4%)  21 (19.3%)  7 (13.0%)
  Black                15 (3.0%)    1 (0.9%)   1 (1.9%)
  Hispanic or Latino 325 (65.1%)  75 (68.8%) 39 (72.2%)
  Asian                30 (6.0%)    5 (4.6%)   2 (3.7%)
  Other                32 (6.4%)    7 (6.4%)   5 (9.3%)
-------------------------------------------------------

[Kevin Blaine]

Perfect. Thank you, Jeff!