Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Can we do this with tables?

    Hello,

    Using data from (use https://www.stata-press.com/data/r18/nhanes2l) as an example, can we add a new column in the output table for percentage of Total columns only. For example

    when I did
    Code:
    tab sex race, col
    , I got this table

    Race
    Sex White Black Other Total
    Male 4,312 500 103 4,915
    47.57 46.04 51.5 47.48
    Female 4,753 586 97 5,436
    52.43 53.96 48.5 52.52
    Total 9,065 1,086 200 10,351
    100 100 100 100

    but I do not want the percentages to appear in each row, instead I want to add a new column for %total, can that be achieved in Stata because I heard that the new update has fully customizable tables commands like collect and dtable, I am using Stata V16.
    Race
    Sex White Black Other Total %Total
    Male 4,312 500 103 4,915 47.48
    Female 4,753 586 97 5,436 52.52
    Total 9,065 1,086 200 10,351
    100 100 100 100

    Thanks in advance!
    Tags: None
  • #2
    Are you asking for a solution in the version of Stata available to you (version 16), or that can be done in Stata 18 (with the new -table- command, introduced in Stata 17) ?

    Comment

    • #3
      If it can be done with version 16 that will be great, but if it can only be done with version 17 and up, then I guess I have to purchase the upgrade.
      Last edited by Bader Bin Adwan; 22 May 2023, 16:00.

      Comment

      • #4
        Here is my attempt to produce your table with commands available in Stata 16 (nothing extra to install).
        Code:
        webuse nhanes2l

keep sex race
mark touse
markout touse sex race
keep if touse
collapse (count) count=touse, by(sex race)

frame put count sex, into(sex_margin)
frame sex_margin {
        collapse (count) count [fw=count], by(sex)
        sum count, meanonly
        expand 2 in l 
        replace sex = . in l
        replace count = r(sum) in l 
        gen percent = 100 * count / r(sum)
        format %5.2f percent
}

frame put count race, into(race_margin)
frame race_margin : collapse (count) count [fw=count], by(race)

list
frame sex_margin : list
frame race_margin : list

frame sex_margin : save sex_margin, replace
frame race_margin : save race_margin, replace
append using sex_margin race_margin
list

format %7.0fc count
tabdisp sex race, total cellvar(count percent)
        The final output from tabdisp is
        Code:
        . tabdisp sex race, total cellvar(count percent)

------------------------------------------
          |              Race             
      Sex |  White   Black   Other   Total
----------+-------------------------------
     Male |  4,312     500     103   4,915
          |                          47.48
          | 
   Female |  4,753     586      97   5,436
          |                          52.52
          | 
    Total |  9,065   1,086     200  10,351
          |                         100.00
------------------------------------------
        I was not able to get tabdisp to put the row percents in their own column.
        • 1 like

        Comment

        • #5
          Here is my attempt to produce your table with commands available in Stata 17 (or 18) (nothing extra to install)
          Code:
          webuse nhanes2l

table sex race, totals(race) name(counts)
collect layout

table sex, statistic(frequency) statistic(percent) name(sex_percent)
collect style cell result[percent], sformat("%s%%")
collect addtags race[.a], fortags(result[frequency])
collect addtags race[.b], fortags(result[percent])
collect label levels race .a "Total" .b "% Total"
collect layout
        
collect combine comb = counts sex_percent
collect composite define show = frequency percent
collect style header result, level(hide)
collect layout (sex) (race#result[show])
          The output from the final layout is
          Code:
          . collect layout (sex) (race#result[show])

Collection: comb
      Rows: sex
   Columns: race#result[show]
   Table 1: 4 x 5

----------------------------------------------------
         |                    Race                  
         |  White   Black   Other    Total   % Total
---------+------------------------------------------
Sex      |                                          
  Male   |  4,312     500     103    4,915    47.48%
  Female |  4,753     586      97    5,436    52.52%
  Total  |  9,065   1,086     200   10,351   100.00%
----------------------------------------------------
          In addition, you can use collect export to publish this table to MS Word, Excel, HTML, LaTeX, or PDF.
          • 1 like

          Comment

          • #6
            Thank you @Jeff Pitblado (StataCorp), I Really appreciate your efforts. I have another question though and I hope I can find a solution to it as it will huge credit to me at work. I deal with national census and statistics and health information at national level, I am the only one who use Stata while all the rest use SPSS. In SPSS they use a command to produce one of the tables published in annual report for the country. The SPSS syntax is as follows:
            Code:
            CROSSTABS
/Tables=Sex By month By gov By nationality
/FORMAT=AVALUE TABLES
/CELLS=COUNT
/COUNT ROUND CELL.
            This command produce the following table:
            Count
            nationality gov month Total
            Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
            Citizens C Sex M 1 2 0 1 1 1 1 1 0 1 9
            F 0 1 3 1 1 1 0 2 2 2 13
            Total 1 3 3 2 2 2 1 3 2 3 22
            H Sex M 1 1 1 0 1 1 5
            F 1 2 0 1 1 0 5
            Total 2 3 1 1 2 1 10
            A Sex M 3 0 3 1 1 2 1 1 3 2 1 2 20
            F 1 1 1 1 3 2 1 0 1 1 1 3 16
            Total 4 1 4 2 4 4 2 1 4 3 2 5 36
            J Sex M 1 2 0 1 1 1 1 2 2 1 0 12
            F 0 0 2 1 1 2 1 0 0 1 1 9
            Total 1 2 2 2 2 3 2 2 2 2 1 21
            Fa Sex M 6 6 2 2 4 3 2 4 5 4 6 44
            F 2 2 0 1 2 0 0 2 3 1 0 13
            Total 8 8 2 3 6 3 2 6 8 5 6 57
            Mu Sex M 0 4 1 1 2 1 0 1 1 1 1 13
            F 2 1 1 0 0 1 1 0 3 0 0 9
            Total 2 5 2 1 2 2 1 1 4 1 1 22
            not stated Sex M 1 3 1 1 2 0 1 2 11
            F 0 0 1 0 1 1 0 1 4
            Total 1 3 2 1 3 1 1 3 15
            Total Sex M 11 16 9 8 11 9 5 8 11 12 3 11 114
            F 5 6 7 7 7 6 5 2 8 5 5 6 69
            Total 16 22 16 15 18 15 10 10 19 17 8 17 183
            Non-Citizens C Sex M 1 1 2 4
            Total 1 1 2 4
            H Sex M 3 2 1 0 1 2 2 0 2 1 1 15
            F 0 1 1 1 0 0 0 3 0 0 2 8
            Total 3 3 2 1 1 2 2 3 2 1 3 23
            A Sex M 0 1 2 1 1 3 1 1 1 11
            F 1 0 0 0 0 2 0 0 0 3
            Total 1 1 2 1 1 5 1 1 1 14
            J Sex M 0 2 2 3 1 1 0 1 0 10
            F 1 1 0 0 0 2 1 0 1 6
            Total 1 3 2 3 1 3 1 1 1 16
            Fa Sex M 3 7 4 5 6 4 5 9 4 6 2 4 59
            F 1 5 2 0 0 3 1 2 0 1 2 1 18
            Total 4 12 6 5 6 7 6 11 4 7 4 5 77
            Mu Sex M 1 0 1 2
            F 0 1 0 1
            Total 1 1 1 3

            is there any way to produce such tables in Stata? They want to validate the results produces by SPSS and Stata. I cannot give a sample of the real data, but using any data available to produce table with such template will be helpful.

            Thanks in Advance!

            Comment

            • #7
              Originally posted by Jeff Pitblado (StataCorp) View Post
              Here is my attempt to produce your table with commands available in Stata 17 (or 18) (nothing extra to install)
              Code:
              webuse nhanes2l

table sex race, totals(race) name(counts)
collect layout

table sex, statistic(frequency) statistic(percent) name(sex_percent)
collect style cell result[percent], sformat("%s%%")
collect addtags race[.a], fortags(result[frequency])
collect addtags race[.b], fortags(result[percent])
collect label levels race .a "Total" .b "% Total"
collect layout

collect combine comb = counts sex_percent
collect composite define show = frequency percent
collect style header result, level(hide)
collect layout (sex) (race#result[show])
              The output from the final layout is
              Code:
              . collect layout (sex) (race#result[show])

Collection: comb
Rows: sex
Columns: race#result[show]
Table 1: 4 x 5

----------------------------------------------------
| Race
| White Black Other Total % Total
---------+------------------------------------------
Sex |
Male | 4,312 500 103 4,915 47.48%
Female | 4,753 586 97 5,436 52.52%
Total | 9,065 1,086 200 10,351 100.00%
----------------------------------------------------
              In addition, you can use collect export to publish this table to MS Word, Excel, HTML, LaTeX, or PDF.
              If I want to save the results above as a solo dataset,
              how can I write the Stata code?

              Comment

              • #8
                Here is simulated example based on my understanding of the data used to build the above table.
                Code:
                set seed 18
set obs 900

generate month = runiformint(1,12)
label define month ///
        1 "Jan" ///
        2 "Feb" ///
        3 "Mar" ///
        4 "Apr" ///
        5 "May" ///
        6 "Jun" ///
        7 "Jul" ///
        8 "Aug" ///
        9 "Sep" ///
        10 "Oct" ///
        11 "Nov" ///
        12 "Dec" 
label values month month

generate sex = runiformint(0,1)
label define sex 0 "M" 1 "F"
label values sex sex

generate gov = runiformint(1,7)
label define gov 1 "C" 2 " H" 3 "A" 4 "J" 5 "Fa" 6 "MU" 7 "not stated"
label values gov gov

generate nationality = runiformint(1,2)
label define nationality 1 "Citizens" 2 "Non-Citizens"
label values nationality nationality

table (nationality gov sex) (month), ///
        total(nationality#gov nationality#gov#month nationality#gov#sex)

collect style row split, dups(first)
collect preview
                table's default style is to stack the row headers, so I use collect to change the row headers to use a split format. The resulting table is
                Code:
                ----------------------------------------------------------------------------------------------------------------------------------
                                                  |                                      month                                    
                                                  |  Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec   Total
--------------------------------------------------+-------------------------------------------------------------------------------
nationality Citizens     gov C          sex M     |    1     4     3     5     2     1     3     2     4     5     1     8      39
                                            F     |    3     2     1     1     1     2     3     3     2     2     3     3      26
                                            Total |    4     6     4     6     3     3     6     5     6     7     4    11      65
                             H          sex M     |    1     2     2     4     2     2     1     2     1     4     2     1      24
                                            F     |    4     3     7     1           1     6     3     2     1     3     2      33
                                            Total |    5     5     9     5     2     3     7     5     3     5     5     3      57
                             A          sex M     |    1     1     1     4     5     3     2     2     4     2     4            29
                                            F     |    1     4     3     6           3     2     5     6     2     3     2      37
                                            Total |    2     5     4    10     5     6     4     7    10     4     7     2      66
                             J          sex M     |    2           2     5     4     2     4     4     2           2     2      29
                                            F     |    2     2     2     4     1     1           3     3     2     2     2      24
                                            Total |    4     2     4     9     5     3     4     7     5     2     4     4      53
                             Fa         sex M     |    1     5     3     1     7     3     6     3     1     4     2     4      40
                                            F     |    3     2     4     2     2     2           1     7     2     3     1      29
                                            Total |    4     7     7     3     9     5     6     4     8     6     5     5      69
                             MU         sex M     |    1     4     6           7     5     1     1     2     4     4     3      38
                                            F     |    4     3     3     1     3     7     2     8           1     5     2      39
                                            Total |    5     7     9     1    10    12     3     9     2     5     9     5      77
                             not stated sex M     |          2     2     2     3     2     1           4     2     2     3      23
                                            F     |    1     1     3     5     3     3     1     4     1     2     4     2      30
                                            Total |    1     3     5     7     6     5     2     4     5     4     6     5      53
            Non-Citizens gov C          sex M     |    6     7     4     4     1     1     2     2     4     2     2     3      38
                                            F     |    4     3     4     3     2     4     3     2     4     6     1     2      38
                                            Total |   10    10     8     7     3     5     5     4     8     8     3     5      76
                             H          sex M     |    1     2     5     5     4     3           5     3           3     4      35
                                            F     |    2     5     3     2     3           5           4     4     2     1      31
                                            Total |    3     7     8     7     7     3     5     5     7     4     5     5      66
                             A          sex M     |    2     4     3     4     1     2     2     2     2     3     2     4      31
                                            F     |    5     1     2     4           2     4     1     1           5     4      29
                                            Total |    7     5     5     8     1     4     6     3     3     3     7     8      60
                             J          sex M     |    3     1     2     2     6     2     4     2     4     5     5     2      38
                                            F     |    2     2     3     2     7     2     2     3     4     3     1            31
                                            Total |    5     3     5     4    13     4     6     5     8     8     6     2      69
                             Fa         sex M     |    2     3           2     1     2     3     3     3     1     3     2      25
                                            F     |    5     2     1     3     3     2     6     2     2     3     3     4      36
                                            Total |    7     5     1     5     4     4     9     5     5     4     6     6      61
                             MU         sex M     |    4     2     2     4     2           2     3     2     2     1     4      28
                                            F     |    4     2     3           2     4     1     3     3     3     5     1      31
                                            Total |    8     4     5     4     4     4     3     6     5     5     6     5      59
                             not stated sex M     |    2     3           2     2     3     3     3     3     2     1     6      30
                                            F     |    5     2     5     6     2     5     2     6     1     3           2      39
                                            Total |    7     5     5     8     4     8     5     9     4     5     1     8      69
----------------------------------------------------------------------------------------------------------------------------------
                The stacked header is taller (less wide)
                Code:
                -------------------------------------------------------------------------------------------------
                 |                                      month                                    
                 |  Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec   Total
-----------------+-------------------------------------------------------------------------------
nationality      |                                                                               
  Citizens       |                                                                               
    gov          |                                                                               
      C          |                                                                               
        sex      |                                                                               
          M      |    1     4     3     5     2     1     3     2     4     5     1     8      39
          F      |    3     2     1     1     1     2     3     3     2     2     3     3      26
          Total  |    4     6     4     6     3     3     6     5     6     7     4    11      65
      H          |                                                                               
        sex      |                                                                               
          M      |    1     2     2     4     2     2     1     2     1     4     2     1      24
          F      |    4     3     7     1           1     6     3     2     1     3     2      33
          Total  |    5     5     9     5     2     3     7     5     3     5     5     3      57
      A          |                                                                               
        sex      |                                                                               
          M      |    1     1     1     4     5     3     2     2     4     2     4            29
          F      |    1     4     3     6           3     2     5     6     2     3     2      37
          Total  |    2     5     4    10     5     6     4     7    10     4     7     2      66
      J          |                                                                               
        sex      |                                                                               
          M      |    2           2     5     4     2     4     4     2           2     2      29
          F      |    2     2     2     4     1     1           3     3     2     2     2      24
          Total  |    4     2     4     9     5     3     4     7     5     2     4     4      53
      Fa         |                                                                               
        sex      |                                                                               
          M      |    1     5     3     1     7     3     6     3     1     4     2     4      40
          F      |    3     2     4     2     2     2           1     7     2     3     1      29
          Total  |    4     7     7     3     9     5     6     4     8     6     5     5      69
      MU         |                                                                               
        sex      |                                                                               
          M      |    1     4     6           7     5     1     1     2     4     4     3      38
          F      |    4     3     3     1     3     7     2     8           1     5     2      39
          Total  |    5     7     9     1    10    12     3     9     2     5     9     5      77
      not stated |                                                                               
        sex      |                                                                               
          M      |          2     2     2     3     2     1           4     2     2     3      23
          F      |    1     1     3     5     3     3     1     4     1     2     4     2      30
          Total  |    1     3     5     7     6     5     2     4     5     4     6     5      53
  Non-Citizens   |                                                                               
    gov          |                                                                               
      C          |                                                                               
        sex      |                                                                               
          M      |    6     7     4     4     1     1     2     2     4     2     2     3      38
          F      |    4     3     4     3     2     4     3     2     4     6     1     2      38
          Total  |   10    10     8     7     3     5     5     4     8     8     3     5      76
      H          |                                                                               
        sex      |                                                                               
          M      |    1     2     5     5     4     3           5     3           3     4      35
          F      |    2     5     3     2     3           5           4     4     2     1      31
          Total  |    3     7     8     7     7     3     5     5     7     4     5     5      66
      A          |                                                                               
        sex      |                                                                               
          M      |    2     4     3     4     1     2     2     2     2     3     2     4      31
          F      |    5     1     2     4           2     4     1     1           5     4      29
          Total  |    7     5     5     8     1     4     6     3     3     3     7     8      60
      J          |                                                                               
        sex      |                                                                               
          M      |    3     1     2     2     6     2     4     2     4     5     5     2      38
          F      |    2     2     3     2     7     2     2     3     4     3     1            31
          Total  |    5     3     5     4    13     4     6     5     8     8     6     2      69
      Fa         |                                                                               
        sex      |                                                                               
          M      |    2     3           2     1     2     3     3     3     1     3     2      25
          F      |    5     2     1     3     3     2     6     2     2     3     3     4      36
          Total  |    7     5     1     5     4     4     9     5     5     4     6     6      61
      MU         |                                                                               
        sex      |                                                                               
          M      |    4     2     2     4     2           2     3     2     2     1     4      28
          F      |    4     2     3           2     4     1     3     3     3     5     1      31
          Total  |    8     4     5     4     4     4     3     6     5     5     6     5      59
      not stated |                                                                               
        sex      |                                                                               
          M      |    2     3           2     2     3     3     3     3     2     1     6      30
          F      |    5     2     5     6     2     5     2     6     1     3           2      39
          Total  |    7     5     5     8     4     8     5     9     4     5     1     8      69
-------------------------------------------------------------------------------------------------

                Comment

                • #9
                  Regarding Jason's question in #7, I would refer you to the code in #4.

                  Comment

                  • #10

                    percent) ------------------------------------------ | Race Sex | White Black Other Total ----------+------------------------------- Male | 4,312 500 103 4,915 | 47.48 | Female | 4,753 586 97 5,436 | 52.52 | Total | 9,065 1,086 200 10,351 | 100.00 ------------------------------------------ I need to save this as a dataset. I really do not know how to do this.

                    Comment

                    • #11
                      Thanks you so much for your time and support @Jeff Pitblado (StataCorp), this is really helpful. I wish I could do that in Stata 16 but I guess I have to upgrade it.

                      Thanks again!

                      Comment

                      Previous Next
                      Working...
                      X