I have a question about the new dtable command.

[Milton Romero]

Good morning community, I hope you are doing well.

I have a question about the new dtable command.

I would like to get the relative frequencies by rows (as in N) and not by columns.

I have checked the command options but I can't find something related. I hope for your prompt help.

Thank you very much!

[Jeff Pitblado (StataCorp)]

Here is one example in the post Using dtable to calculate row percentage

Here is another example in the post dtable Stata18 row percentages

The idea is to use table to build a separate collection for each of your row variables with the percentages you want, then combine these collections into a single collect, make some style edits, and construct your table's layout.

Neither of the above examples address how to handle the p-values for tests across groups, but that is a matter of collecting the p-value from tabulate, chi2 for each of your row variables.

Here is a modified version of second example that also handles the p-values.

clear all

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 over the factor variables to compute their "row" percentages
* across the levels of -rural-; and compute the p-value
foreach v of local vlist {
    table (`v') (rural), ///
        stat(frequency) ///
        stat(percent, across(rural)) ///
        totals(`v') ///
        name(`v')
    * get minimum level for tagging the p-value
    collect levels `v'
    local min = s(levels)
    gettoken min : min
    * compute the p-value
    tabulate `v' rural, chi2
    * collect the p-value and tag it so we can place it in our combined table
    collect get p = (r(p)), name(`v') tags(`v'[`min'] rural[Test])
}

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

* define a composite result like -dtable- does
collect composite define stats = frequency percent

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

* add formats for the p-value like -dtable- does
collect style cell result[p], nformat("%5.3f") minimum(.001)

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

* make Test the last column (i.e. level of the rural dimension)
collect levels rural
local levels = s(levels)
local levels : subinstr local levels "Test" ""
collect style autolevels rural `levels' Test, clear

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

Here is the final table.

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

[Girish Venkataraman]

Jeff Pitblado (StataCorp) : That looks great. What would be a good way to bold the rowlist names (for e.g. Sex) but not each of their levels like Male and Female?

[Jeff Pitblado (StataCorp)]

Currently, there is no simple way to bold the dimension name/label in
the header without also bolding one of its levels. However, you can add
a blank space result with a hidden row dimension-level as a work-around.
In the following code I modified (in blue) the above example to bold the
row variable labels.

clear all

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 over the factor variables to compute their "row" percentages
* across the levels of -rural-; and compute the p-value
foreach v of local vlist {
    table (`v') (rural), ///
        stat(frequency) ///
        stat(percent, across(rural)) ///
        totals(`v') ///
        name(`v')
    * get minimum level for tagging the p-value
    collect levels `v'
    local levels = s(levels)
    gettoken min : levels
    * compute the p-value
    tabulate `v' rural, chi2
    * collect the p-value and tag it so we can place it in our combined table
    collect get p = (r(p)), name(`v') tags(`v'[`min'] rural[Test])
    collect get p = " ", name(`v') tags(`v'[_hide] rural[Test])
    collect style autolevels `v' _hide `levels', clear
    collect style cell `v'[_hide]#cell_type[row-header], font(, bold)
}

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

* define a composite result like -dtable- does
collect composite define stats = frequency percent

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

* add formats for the p-value like -dtable- does
collect style cell result[p], nformat("%5.3f") minimum(.001)

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

* make Test the last column (i.e. level of the rural dimension)
collect levels rural
local levels = s(levels)
local levels : subinstr local levels "Test" ""
collect style autolevels rural `levels' Test, clear

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

Here is a screen-shot of the table from a PDF file I generated using LaTeX.

[Vusimuzi Maduna]

Jeff Pitblado (StataCorp) : How do you change the above Code from Row percentages to Column percentages? I would like to apply the same in my data to produce a similar table but showing column percentages instead of row percentages.

[Jeff Pitblado (StataCorp)]

If you have Stata 18, use dtable.

webuse nhanes2l
local vlist smsa sex race hlthstat heartatk diabetes highlead
dtable, by(rural, test) factor(`vlist')

If you have Stata 17, you can construct the same table as dtable using
table and tabulate with some loops.

webuse nhanes2l
local vlist smsa sex race hlthstat heartatk diabetes highlead
table (var result) (rural), ///
    statistic(frequency) ///
    statistic(percent) ///
    statistic(fvfrequency `vlist') ///
    statistic(fvpercent `vlist') ///

foreach v of local vlist {
    * get minimum level for tagging the p-value
    quietly collect levels `v'
    local levels = s(levels)
    gettoken min : levels
    * compute the p-value
    quietly tabulate `v' rural, chi2
    * collect the p-value and tag it so we can place it in our combined table
    collect get p = (r(p)), tags(var[`min'.`v'] rural[Test])
}

* rearrange the columns
collect levels rural
local levels = s(levels)
collect style autolevels rural `levels', clear
collect style cell result[p], nformat("%5.3f") minimum(.001)

* style changes to look like dtable
collect style row stack, nobinder
collect style column, extraspace(0)
collect style cell cell_type[column-header], halign(center)
collect composite define stats = frequency percent fvfrequency fvpercent, trim
collect style cell result[percent fvpercent], nformat("%5.1f") sformat("(%s%%)")
collect style cell border_block[corner row-header], border(right, pattern(none))
collect style header result, level(hide)
collect recode var _hide = N
collect layout (var) (rural#result[stats p])

Either case, you get the following table.

---------------------------------------------------------------------------
                                                Rural
                             Urban         Rural          Total       Test
---------------------------------------------------------------------------
N                        6,548 (63.3%) 3,803 (36.7%) 10,351 (100.0%)
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%)
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%)
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%)
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%)
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%)
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%)
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%)
---------------------------------------------------------------------------

[Vusimuzi Maduna]

Dear Jeff,

Thanks for the code. One of my dependent variables (Wealth Asset Index) is repeated twice at the bottom of the output table. What could cause this anomality. With the Row Percentage table everything comes out well. See attached table output.

[Jeff Pitblado (StataCorp)]

It could be any of a number of things. Please provide your code with a sample dataset that reproduces the problem, and we might be able to help you.

[Vusimuzi Maduna]

* By Column Percentage

local vlist w1c_rage_cal_1 w2c_rsex w1c_bd_mar w1c_hhsize w1bd026_1 w1c_bd_educ4 w1c_wealth w1gt001s6 w1cm068 w1cm059
table (var result) (AAI_Category), ///
    statistic(frequency) ///
    statistic(percent) ///
    statistic(fvfrequency `vlist') ///
    statistic(fvpercent `vlist') ///

foreach v of local vlist {
    * get minimum level for tagging the p-value
    quietly collect levels `v'
    local levels = s(levels)
    gettoken min : levels
    * compute the p-value
    quietly tabulate `v' AAI_Category, chi2
    * collect the p-value and tag it so we can place it in our combined table
    collect get p = (r(p)), tags(var[`min'.`v'] AAI_Category[Test])
    collect style cell `v'[_hide]#cell_type[row-header], font(, bold)
    
}

* add formats for the p-value like -dtable- does
collect style cell result[p], nformat("%5.3f") minimum(.001)
collect stars manual_pvalue 0.01 "***" 0.05 "**" 0.1 "*", ///
    attach(manual_pvalue) shownote
    
* rearrange the columns
collect levels AAI_Category
local levels = s(levels)
collect style autolevels AAI_Category `levels', clear
collect style cell result[p], nformat("%5.3f") minimum(.001)

* style changes to look like dtable
collect style row stack, nobinder
collect style column, extraspace(0)
collect style cell cell_type[column-header], halign(center)
collect composite define stats = frequency percent fvfrequency fvpercent, trim
collect style cell result[percent fvpercent], nformat("%5.1f") sformat("(%s%%)")
collect style cell border_block[corner row-header], border(right, pattern(none))
collect style header result, level(hide)
collect recode var _hide = N
collect layout (var) (AAI_Category#result[stats p])

Sample Data

* Example generated by -dataex-. For more info, type help dataex
clear
input float w1c_rage_cal_1 byte(w2c_rsex w1c_bd_mar w1c_hhsize w1bd026_1 w1c_bd_educ4 w1c_wealthindex w1gt001s6 w1cm068 w1cm059) float(AAI_Category DI_Health_Category DI_Participation_Category DI_Security_Category)
1 1 3 1 1 1 1 1 0 1 3 3 1 3
3 2 1 0 1 1 3 1 1 0 1 1 1 3
1 1 3 1 2 2 3 1 1 1 2 2 1 3
1 2 2 1 1 1 3 1 0 0 2 2 1 3
1 2 2 1 1 1 3 1 0 0 2 3 1 3
3 2 2 1 0 1 2 1 1 0 2 3 1 3
2 2 2 1 1 2 2 1 1 0 3 3 1 3
2 2 3 1 2 2 4 1 0 0 3 2 3 3
2 1 3 1 2 1 4 1 1 0 3 3 1 3
1 1 3 1 1 1 3 1 1 1 3 3 1 3
1 2 2 1 1 2 1 1 0 0 2 1 1 3
2 2 2 1 0 1 4 0 0 0 2 1 1 3
3 2 2 0 2 1 4 1 0 0 2 2 1 3
2 2 2 1 1 1 3 0 0 0 2 2 1 3
2 2 3 1 0 2 2 1 0 0 3 3 1 3
1 2 2 1 1 3 4 1 0 0 2 2 1 3
1 2 2 1 1 1 3 1 0 0 2 3 2 3
2 1 2 1 1 2 3 1 1 1 3 3 2 3
3 2 2 1 0 1 3 1 0 0 3 3 1 3
1 2 0 1 1 3 4 1 0 0 3 3 1 3
1 1 3 1 0 1 2 1 0 1 3 3 1 3
3 1 3 1 0 1 3 1 1 1 2 2 1 3
1 2 2 1 0 1 2 1 1 0 2 3 1 3
3 1 3 1 0 1 2 1 0 0 2 2 1 3
1 2 2 1 1 1 4 1 1 0 3 3 1 3
2 1 3 1 2 2 4 1 0 0 2 2 1 3
1 2 2 1 0 1 3 1 0 0 3 3 2 3
1 2 2 1 2 1 1 0 0 0 2 3 1 3
1 1 3 1 1 2 3 1 1 1 2 2 1 3
1 1 3 1 1 2 5 1 0 0 2 2 2 3
1 1 3 1 1 2 2 0 1 1 2 3 1 3
1 2 2 1 1 3 5 1 0 0 3 3 2 3
1 2 3 1 2 3 5 1 0 0 2 3 1 3
1 1 0 0 1 2 1 1 1 0 3 3 1 3
1 1 3 1 0 1 3 1 1 0 3 3 1 3
3 2 2 1 0 1 4 0 1 0 3 3 1 3
1 1 3 1 2 3 5 1 1 0 2 3 1 3
1 2 2 1 1 2 5 1 0 0 2 2 1 3
1 2 2 1 1 1 1 0 0 0 2 3 2 3
2 1 2 1 0 2 3 1 0 0 2 1 1 3
2 2 2 1 0 2 5 1 1 0 3 3 2 3
2 2 2 1 0 1 2 1 0 0 3 3 1 3
1 2 2 1 1 1 3 1 0 0 2 3 2 3
1 1 3 1 2 2 2 1 0 1 3 3 1 3
1 1 3 1 1 3 3 1 1 1 2 2 1 3
1 2 3 1 2 2 5 0 0 0 3 3 1 3
2 2 2 1 1 1 2 0 1 0 2 1 1 3
2 1 2 0 0 1 1 1 1 1 2 2 1 3
2 2 2 1 1 1 5 1 0 0 2 2 2 3
2 2 2 1 1 2 1 1 1 0 3 3 1 3
3 1 1 0 0 1 2 1 1 0 2 2 1 3
2 1 1 0 1 2 2 1 1 1 3 3 1 3
2 1 3 1 1 1 3 1 1 1 2 2 2 3
2 1 3 1 1 2 5 1 0 1 2 3 1 3
1 2 2 1 1 2 4 1 0 0 2 2 2 3
2 2 3 1 1 1 2 1 1 0 3 3 1 3
3 2 3 1 0 1 2 1 1 0 2 1 1 3
3 2 2 1 0 2 3 1 1 0 1 1 1 2
2 2 2 1 0 2 5 1 0 0 2 3 1 3
1 1 3 0 1 1 4 1 0 0 3 3 1 3
1 1 3 1 2 2 3 0 1 1 3 2 3 3
1 2 2 1 2 1 1 0 0 0 2 2 1 3
2 1 2 1 2 1 2 1 1 0 3 3 1 3
1 2 3 1 2 1 3 0 0 0 2 1 1 3
1 2 2 1 1 2 2 1 1 0 3 3 2 3
1 1 3 1 2 1 2 1 1 1 2 3 1 3
2 1 3 1 0 1 3 1 1 1 2 2 2 3
2 2 2 1 2 1 2 0 0 0 2 2 1 3
1 2 1 1 1 1 4 1 0 0 2 2 1 3
1 2 2 1 2 2 5 1 0 0 2 2 2 3
3 2 2 0 0 1 2 1 0 0 2 2 1 3
2 2 2 1 0 1 5 1 0 0 2 2 2 3
3 1 3 1 0 1 1 1 1 1 3 3 2 3
2 2 2 0 0 1 1 1 1 0 2 2 1 3
3 1 3 1 0 1 1 0 1 0 3 3 1 3
1 2 2 1 1 1 5 1 0 0 2 2 1 3
1 2 2 1 0 2 4 1 0 0 2 2 1 3
1 1 3 1 1 2 2 1 0 0 2 3 1 3
1 2 1 1 1 2 5 1 0 0 3 3 1 3
3 2 3 1 0 1 1 1 1 0 2 2 1 3
3 2 2 1 0 1 4 1 0 0 2 2 1 3
1 2 3 1 1 2 5 1 0 0 2 3 1 3
1 2 1 1 1 2 1 1 0 0 2 3 1 3
1 2 2 1 2 1 3 1 0 0 2 2 1 3
3 2 2 1 0 2 5 1 0 0 2 1 1 3
1 2 3 1 1 2 3 1 0 0 3 2 3 3
2 1 1 1 0 1 5 1 1 1 3 3 1 3
1 1 3 1 2 2 1 1 1 0 3 3 2 3
1 2 2 1 0 1 5 1 0 0 3 3 1 3
2 2 2 1 0 1 3 1 1 0 1 1 1 1
2 1 3 1 0 1 4 1 1 1 2 2 1 3
1 2 2 1 0 1 4 1 0 0 3 3 2 3
2 1 2 0 0 1 2 1 0 0 3 3 1 3
1 1 3 1 1 3 4 1 1 1 3 3 1 3
3 2 2 0 0 2 2 1 1 0 1 1 1 3
3 2 2 0 2 1 1 1 0 0 2 2 1 3
2 2 3 1 0 2 1 1 1 0 2 2 1 3
1 1 3 1 0 1 1 1 1 1 3 3 1 3
1 1 3 1 1 1 1 1 1 0 3 3 2 3
2 2 3 1 0 1 2 1 1 0 3 3 2 3
end
label values w1c_rage_cal_1 w1c_rage_cal_1
label def w1c_rage_cal_1 1 "60-69 years", modify
label def w1c_rage_cal_1 2 "70-79 years", modify
label def w1c_rage_cal_1 3 "80+ years", modify
label values w2c_rsex SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify
label values w1c_bd_mar marc
label def marc 0 "Never married", modify
label def marc 1 "Separated / divorced", modify
label def marc 2 "Widowed", modify
label def marc 3 "Currently married", modify
label values w1c_hhsize w1c_hhsize
label def w1c_hhsize 0 "Living alone", modify
label def w1c_hhsize 1 "Living with other people", modify
label values w1bd026_1 w1bd026_1
label def w1bd026_1 0 "No grandchildren", modify
label def w1bd026_1 1 "1-7 grandchildren", modify
label def w1bd026_1 2 "8 and more grandchildren", modify
label values w1c_bd_educ4 educ
label def educ 1 "No formal education", modify
label def educ 2 "Some primary (1-7 years)", modify
label def educ 3 "Some secondary (8-11 years)", modify
label values w1c_wealthindex w1c_wealthindex
label def w1c_wealthindex 1 "Poorest", modify
label def w1c_wealthindex 2 "Poorer", modify
label def w1c_wealthindex 3 "Middle", modify
label def w1c_wealthindex 4 "Richer", modify
label def w1c_wealthindex 5 "Richest", modify
label values w1gt001s6 w1gt001s6
label def w1gt001s6 0 "No", modify
label def w1gt001s6 1 "Yes", modify
label values w1cm068 w1cm068
label def w1cm068 0 "No", modify
label def w1cm068 1 "Yes", modify
label values w1cm059 w1cm059
label def w1cm059 0 "No", modify
label def w1cm059 1 "Yes", modify
label values AAI_Category AAI_labels
label def AAI_labels 1 "Low", modify
label def AAI_labels 2 "Moderate", modify
label def AAI_labels 3 "High", modify
label values DI_Health_Category DI_labels
label def DI_labels 1 "Low", modify
label def DI_labels 2 "Moderate", modify
label def DI_labels 3 "High", modify
label values DI_Participation_Category DI_Participation_labels
label def DI_Participation_labels 1 "Low", modify
label def DI_Participation_labels 2 "Moderate", modify
label def DI_Participation_labels 3 "High", modify
label values DI_Security_Category DI_Security_labels
label def DI_Security_labels 1 "Low", modify
label def DI_Security_labels 2 "Moderate", modify
label def DI_Security_labels 3 "High", modify

------------------ copy up to and including the previous line ------------------

Listed 100 out of 2807 observations
Use the count() option to list more

[Jeff Pitblado (StataCorp)]

You have a variable name abbreviation that you use in the foreach loop to define p-values.
To fix this, change

local vlist w1c_rage_cal_1 w2c_rsex w1c_bd_mar w1c_hhsize w1bd026_1 w1c_bd_educ4 w1c_wealth w1gt001s6 w1cm068 w1cm059

to

unab vlist : w1c_rage_cal_1 w2c_rsex w1c_bd_mar w1c_hhsize w1bd026_1 w1c_bd_educ4 w1c_wealth w1gt001s6 w1cm068 w1cm059

[Vusimuzi Maduna]

Dear Jeff,
It worked. Thank you so much for the help.

[Federica Di Marcantonio]

Dear @Jeff Pitblado
is it possible to do something similar with stata 16? I run the command but the option stat() is not allowed.
Thanks
Federica

[Jeff Pitblado (StataCorp)]

The collect system was introduced in Stata 17, it is not available in Stata16.