Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Showing Frequency in Nested Table (Dtable or Table?)

    Hi All,

    I would like to have a table that shows two levels and frequencies. I have bounced back and forth between dtable and table function. Any help would be greatly appreciated! Here is sample data from nhanes2.

    Code:
    dtable i.agegrp, by (race) column( by(hide) ) ///
    sformat("(N=%s)" frequency) ///
    sample(, statistic(frequency) place(seplabels) ) ///
    nformat(%9.0f)
    Click image for larger version

Name:	dtable example.jpg
Views:	1
Size:	38.4 KB
ID:	1770088




    Problem: I need to group the "race" variable with a higher level of "Rural." Dtable does not seem to allow to multiple "by" statements, so I switched to Table

    Code:
     table (agegrp) (rural race), ///
     statistic(percent, across(agegrp)) ///
     nformat(%9.0f percent) sformat(`"%s%%"' percent)
    Click image for larger version

Name:	Table Example.jpg
Views:	1
Size:	57.9 KB
ID:	1770089


    Question: how can I show the frequency under the "race" labels as in the dtable example above? If not possible, just displaying the N for each racial group somewhere.
    Question: how to remove the top "rural" header?
    Not necessary but would be nice: Is there a way to add a horizontal line between "urban" "rural" and "total"

    Thank you!

  • #2
    Thanks for the working example.

    Adding custom column headers is not very difficult, but it involves building them in a loop. It's roughly double the lines of code; which I'll show this in a subsequent post.

    The easier route is to build a separate table (collection) with the column counts, combine the two collections, and update the layout.

    Here is the code I came up with to add column counts to the bottom of the table.
    Code:
    webuse nhanes2
    
    * table of percentages
    table (agegrp) (rural race), ///
        statistic(percent, across(agegrp)) ///
        nformat(%9.0f percent) ///
        sformat(`"%s%%"' percent)
    
    * suppress the rural dimension from the headers
    collect style header rural, title(hide)
    
    * table of some marginal counts
    table () (rural race), ///
        name(Counts)
    
    * combine the collections
    collect combine all = Table Counts
    
    * we now have both results in the collection
    collect levelsof result
    * hide the percent result from the header
    collect style header result[percent], level(hide)
    
    * replay the table
    collect layout
    * fix layout to also show the counts
    collect layout (agegrp#result result[frequency]) (rural#race)
    Here is the resulting table.
    Code:
    -----------------------------------------------------------------------------------------------------------
              |              Urban                           Rural                            Total            
              |               Race                            Race                            Race             
              |  White   Black   Other   Total   White   Black   Other   Total   White   Black   Other    Total
    ----------+------------------------------------------------------------------------------------------------
    Age group |                                                                                                
      20–29   |    24%     27%     30%     25%     18%     22%     26%     18%     22%     26%     30%      22%
      30–39   |    15%     16%     16%     15%     17%     19%     15%     17%     16%     16%     16%      16%
      40–49   |    12%     12%     14%     12%     13%      9%     13%     13%     12%     11%     14%      12%
      50–59   |    12%     12%     11%     12%     13%     16%     13%     13%     12%     13%     11%      12%
      60–69   |    27%     24%     25%     27%     30%     22%     21%     29%     28%     24%     24%      28%
      70+     |    10%      9%      4%      9%     10%     12%     13%     10%     10%      9%      6%      10%
      Total   |   100%    100%    100%    100%    100%    100%    100%    100%    100%    100%    100%     100%
    Frequency |  5,419     968     161   6,548   3,646     118      39   3,803   9,065   1,086     200   10,351
    -----------------------------------------------------------------------------------------------------------

    Comment


    • #3
      Here is the code I came up with to add custom column labels containing the column counts.
      Code:
      webuse nhanes2
      
      * table of percentages
      table (agegrp) (rural race), ///
          statistic(percent, across(agegrp)) ///
          nformat(%9.0f percent) ///
          sformat(`"%s%%"' percent)
      
      * suppress the rural dimension from the headers
      collect style header rural, title(hide)
      
      * loop over the column-level combinations in the above to create a new
      * dimension to tag each column so we can construct a custom column
      * label; I'm naming the new dimension -colidx-, but you can use any name
      * you like so long as it is not already being used in the collection
      
      collect levels rural
      local rural_levels = s(levels)
      collect levels race
      local race_levels = s(levels)
      * note that level ".m" is the auto-assigned marginal level, is labeled
      * "Total", and does not exist in the dataset
      
      * if you add a condition in the above -table- call, also put that
      * condition here
      mark touse
      
      local colidx 0
      foreach rural of local rural_levels {
          if "`rural'" == ".m" {
              local andrural
          }
          else {
              * note: assuming rural is numeric; otherwise, you need
              * to quote the level
              local andrural `"& (rural==`rural')"'
          }
          foreach race of local race_levels {
              if "`race'" == ".m" {
                  local andrace
              }
              else {
                  * note: assuming race is numeric; otherwise,
                  * you need to quote the level
                  local andrace `"& (race==`race')"'
              }
              * compute the column count
              count if touse `andrural' `andrace'
              local ++colidx
              * tag all elements that belong to this combination of
              * -rural- and -race-
              collect addtags colidx[`colidx'], ///
                  fortags(rural[`rural']#race[`race'])
              * format the count result as you like
              local N : display %21.0fc r(N)
              collect label levels colidx `colidx' "(N=`:list retok N')"
          }
      }
      
      * style the header to hide the dimension title, but show our custom
      * level labels
      collect style header colidx, title(hide) level(label)
      
      * query and update the layout to include the new dimension with custom
      * column labels
      collect layout
      collect layout (`s(rows)') (`s(columns)'#colidx) (`s(tables)')
      Here is the resulting table.
      Code:
      ------------------------------------------------------------------------------------------------------------------------------------------------
                |                    Urban                                       Rural                                        Total                   
                |                     Race                                       Race                                         Race                    
                |      White     Black     Other       Total       White     Black    Other       Total       White       Black     Other        Total
                |  (N=5,419)   (N=968)   (N=161)   (N=6,548)   (N=3,646)   (N=118)   (N=39)   (N=3,803)   (N=9,065)   (N=1,086)   (N=200)   (N=10,351)
      ----------+-------------------------------------------------------------------------------------------------------------------------------------
      Age group |                                                                                                                                     
        20–29   |        24%       27%       30%         25%         18%       22%      26%         18%         22%         26%       30%          22%
        30–39   |        15%       16%       16%         15%         17%       19%      15%         17%         16%         16%       16%          16%
        40–49   |        12%       12%       14%         12%         13%        9%      13%         13%         12%         11%       14%          12%
        50–59   |        12%       12%       11%         12%         13%       16%      13%         13%         12%         13%       11%          12%
        60–69   |        27%       24%       25%         27%         30%       22%      21%         29%         28%         24%       24%          28%
        70+     |        10%        9%        4%          9%         10%       12%      13%         10%         10%          9%        6%          10%
        Total   |       100%      100%      100%        100%        100%      100%     100%        100%        100%        100%      100%         100%
      ------------------------------------------------------------------------------------------------------------------------------------------------

      Comment

      Working...
      X