Multiway table with statistics for another variable

[Kirsten VanDerZee]

Hi there,

I'm trying to create a table of summary statistics, describing self-reported cigarette price (var: price), by different product characteristics, such as packaging type (var: packtype), brand producer (var: producertype), shop type, various demographics of the respondent, etc. I want to present these for four different time periods (var: round), as the columns.

I'd like to get the means, medians and standard devs of price within the population sub-groups (categories), for each of the four periods. A shortened version of what I want is the below (in reality I'd want more row variables, each with their own categories):

Note that I don't want the row variables to be interacted (combinations of categories), I just want the statistics for each category separately, and the row variables to be stacked on top of each other, in a long table.

I'm currently trying to use Stata 17's new table command with the collect and append functions, because it seems like it should be able to produce this, but I'm struggling! Here is as far as I've managed to get:

qui table packtype round, statistic(mean price) statistic(sd price) statistic(p50 price) nototals nformat(%9.2f) name(pricetab)
qui table producertype round, statistic(mean price) statistic(sd price) statistic(p50 price) nototals nformat(%9.2f) name(pricetab) append
collect layout (packtype producertype) (round) (result)

Which gives me the data I'm looking for, but in 3 separate tables, one with means, one with stdevs and one with medians. I just want them appended on top of each other. I'm sure I'm using the "result" option incorrectly. The labels, etc also don't look very nice.

Example code:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(packtype producertype price round)
3 0         2 4
3 0         1 4
1 0       .75 4
2 1       2.5 4
2 0         1 4
1 1       1.5 4
3 0         1 4
2 0         1 4
2 0         1 4
2 1       2.4 4
3 0         1 4
3 0       1.5 4
2 1      1.75 4
2 0         1 4
2 0         1 4
3 1         3 4
3 0 1.6666666 4
1 0        .6 4
3 .         1 4
3 0         1 4
3 0         1 4
3 1         3 4
2 0         1 4
3 1         3 4
2 1       2.6 4
3 1         2 4
3 0       1.5 4
2 0       1.2 4
2 1       2.2 4
3 1         3 4
2 1       2.2 4
2 0      2.15 4
2 1       1.9 4
2 0        .8 4
2 1      2.25 4
3 1         2 4
3 1         2 4
3 1       2.5 4
3 1       2.5 4
2 1       2.5 4
2 0        .5 4
2 0         1 4
3 1         2 4
2 0         1 4
3 1       2.5 4
2 0       .95 4
2 1      2.25 4
2 1         3 4
3 0         1 4
2 1      2.15 4
2 1       1.8 4
2 0         1 4
3 1       2.5 4
2 0         1 4
2 0         1 4
2 0       1.1 4
3 0         2 4
2 0         1 4
2 0         1 4
3 0       1.5 4
2 0         1 4
3 0       1.5 4
2 0         1 4
3 0       1.5 4
3 1         3 4
3 0         1 4
2 1      2.25 4
2 0      1.25 4
2 0         1 4
3 0       1.2 4
3 1         3 4
3 1         2 4
2 0      1.15 4
2 1     1.675 4
3 0       1.5 4
2 1       2.1 4
3 1       2.5 4
3 1       2.5 4
3 1         3 4
2 1       2.2 4
2 0        .8 4
3 0         1 4
3 0         2 4
2 1      1.45 4
3 1       2.5 4
3 0         1 4
3 1         3 4
3 0         1 4
2 0      1.15 4
2 1      1.35 4
2 0      1.25 4
3 0       1.5 4
2 1         1 4
2 0         1 4
3 1       2.5 4
3 1         3 4
2 0         1 4
3 0         1 4
3 1         3 4
3 0         1 4
end
label values packtype packtype
label def packtype 1 "1. Carton", modify
label def packtype 2 "2. Pack", modify
label def packtype 3 "3. Single", modify
label values producertype mnc
label def mnc 0 "0. Non-multinational", modify
label def mnc 1 "1. Multinational", modify

[Kirsten VanDerZee]

I had an error with the example data, here is a better version

* Example generated by -dataex-. For more info, type help dataex
clear
input float(packtype producertype price round)
3 1      2.5 1
3 0        1 4
3 1      2.5 1
3 0       .5 1
3 0        2 1
3 1        2 1
3 1      2.5 1
3 1        2 2
3 0        1 1
3 1      2.5 3
3 0       .5 1
3 1        2 3
3 1        2 1
3 1      2.5 3
3 0        1 1
3 1      2.5 1
3 0      1.5 4
2 0        1 1
3 0        2 3
3 1      2.5 3
3 0      1.5 4
3 1        2 4
3 0        2 4
3 1      2.5 1
3 1      2.5 1
3 1      2.5 1
3 0        1 4
3 1        2 1
2 1      1.4 1
3 1 .8333333 1
3 1        2 1
2 0       .5 4
3 1        2 2
3 1        2 1
3 1        3 3
3 1        2 1
3 1        2 1
3 1      2.5 4
2 1      2.2 1
3 0        1 3
3 1       .5 1
3 1      2.5 1
3 1      2.5 1
3 0        1 1
3 1      2.5 1
3 1        3 3
3 1        2 3
3 1        2 1
3 0        1 1
3 0        1 1
3 1        3 1
3 1        2 1
3 1      2.5 3
3 0        2 3
3 0        1 1
3 1        2 3
3 0        1 4
3 1      2.5 2
3 1       .5 1
3 1      2.5 1
2 1      2.5 4
3 0      1.5 3
3 1      2.5 1
3 1        2 1
3 1        3 1
3 0        1 3
2 1      1.5 2
3 1      2.5 1
3 1      2.5 3
3 1      2.5 1
3 0        1 1
3 1        2 2
3 1        2 1
3 0      1.5 4
3 1      2.5 2
3 0       .5 1
2 0        1 4
3 1      2.5 1
3 0       .5 1
2 1      1.9 1
3 1        2 1
3 1        2 4
3 1        3 4
3 0        1 2
2 1      1.5 2
3 1 .8333333 4
3 1      2.5 2
2 0       .8 4
3 1      2.5 3
3 0        1 1
3 1        2 1
3 1      2.5 1
3 1      2.5 3
3 1      2.5 3
3 1        2 1
3 0        1 1
3 0        1 1
2 1     1.75 1
3 0        1 2
3 1        3 4
end
label values packtype packtype
label def packtype 2 "2. Pack", modify
label def packtype 3 "3. Single", modify
label values producertype mnc
label def mnc 0 "0. Non-multinational", modify
label def mnc 1 "1. Multinational", modify

[Mark Horowitz]

The third parentheses is for multiple tables.
Using the data from your previous post, I removed the third parenthesis and interacted the result with each row variable to get this:

. collect layout (packtype#result producertype#result) (round)

Collection: pricetab
      Rows: packtype#result producertype#result
   Columns: round
   Table 1: 18 x 4

---------------------------------------------------
                       |            round          
                       |     1      2      3      4
-----------------------+---------------------------
packtype               |                          
  2. Pack              |                          
    Mean               |  1.65   1.50          1.20
    Standard deviation |  0.46   0.00          0.89
    50th percentile    |  1.75   1.50          0.90
  3. Single            |                          
    Mean               |  1.80   1.94   2.19   1.76
    Standard deviation |  0.77   0.62   0.57   0.74
    50th percentile    |  2.00   2.00   2.50   1.50
producertype           |                          
  0. Non-multinational |                          
    Mean               |  0.94   1.00   1.50   1.18
    Standard deviation |  0.36   0.00   0.50   0.44
    50th percentile    |  1.00   1.00   1.50   1.00
  1. Multinational     |                          
    Mean               |  2.13   2.06   2.46   2.26
    Standard deviation |  0.55   0.42   0.32   0.75
    50th percentile    |  2.00   2.00   2.50   2.50
---------------------------------------------------

[Emanuel Agu]

Hi @Mark,

Your suggestion also worked for a table I'm trying to do. Related to this post, using the same data, I'm now trying to get a table with the layout as in the attachment.

Would you mind sharing suggestions on this? Thank you!

[Hemanshu Kumar]

collect layout (packtype producertype) (round#result)

[Emanuel Agu]

Thanks a lot, Hemanshu Kumar!