Tabulation based on multiple categorical variables

[Marc Klee]

I am using Stata 17 and I’d like to produce a frequency table (tabulate one way) based on multiple variables that have been manually encoded from an open survey-question.
Since respondents have given multiple answers to that question, each person in the dataset has five variables named “encoding_t1” to “encoding_t5” that relate to that same open question. Therefore, a value like 101 encoding for example “cultural related activities”, or 102 encoding "religious related activities" can occur in every single one of these variables - same for every other value of the code scheme.
The frequency table I'd like to produce, should, however, not provide only statistics (like number of observations, percentages) on one variable (e.g. encoding_t1), but should provide information on all of these variables, since I would like to know how many respondents engage in cultural activities or religion activities overall.

Is there a way to produce a variable set where I can get statistics on responses from all these variables in one table? Or do I have to produce a dummy-variable for every possible value from encoding_t1 to encoding_t5?

[Ken Chui]

Welcome to Statalist.

Is there a way to produce a variable set where I can get statistics on responses from all these variables in one table? Or do I have to produce a dummy-variable for every possible value from encoding_t1 to encoding_t5?

Either way. The first approach: reshape the five variables into long, collapse them into counts by different response option, and then divide the counts by the total sample size to get the percentage for each item. Or create a series of binary specific to the item, and them loop through a summary command over them. Both can work, the former one is probably less coding.

[Nick Cox]

Search the forum for mentions of tabm from tab_chi on SSC.

[Marc Klee]

Thank you for your quick reply!

Changing from wide to long format is difficult when producing the table. It is because most of the respondents only have one or two valid answers (cf. table). Therefore, values from *_t3 to *_t5 are frequently missing making the interpretation of number of observations probably difficult in the long format.

encoding_t1	encoding_t2	encoding_t3	encoding_t4	encoding_t5
101	.	.	.	.
102	101	.	.	.
101	102	.	.	.

Then I tried using the wrapper tabm as suggested by Nick. I guess it worked in a way but I only get the number of observations as an output. When setting the transpose-Option which might do the trick, I received an error message:

. tabm encoding_t* [aweight=w4_pgew2], transpose
(1 missing value generated)
too many values

I am grateful for any further suggestions. If not, I’ll do what Ken suggested using a binary variable and the summary command.
Thank you!

[Nick Cox]

Everything works fine, or reasonably so, for your toy example, unsurprisingly.

* Example generated by -dataex-. For more info, type help dataex
clear
input int(encoding_t1 encoding_t2) byte(encoding_t3 encoding_t4 encoding_t5)
101   . . . .
102 101 . . .
101 102 . . .
end

. tabm encoding_*

            |        values
   variable |       101        102 |     Total
------------+----------------------+----------
encoding_t1 |         2          1 |         3 
encoding_t2 |         1          1 |         2 
------------+----------------------+----------
      Total |         3          2 |         5 

. tabm encoding_*, missing

            |              values
   variable |       101        102          . |     Total
------------+---------------------------------+----------
encoding_t1 |         2          1          0 |         3 
encoding_t2 |         1          1          1 |         3 
encoding_t3 |         0          0          3 |         3 
encoding_t4 |         0          0          3 |         3 
encoding_t5 |         0          0          3 |         3 
------------+---------------------------------+----------
      Total |         3          2         10 |        15 

. tabm encoding_*, missing transpose

           |                        variable
    values | encoding_  encoding_  encoding_  encoding_  encoding_ |     Total
-----------+-------------------------------------------------------+----------
       101 |         2          1          0          0          0 |         3 
       102 |         1          1          0          0          0 |         2 
         . |         0          1          3          3          3 |        10 
-----------+-------------------------------------------------------+----------
     Total |         3          3          3          3          3 |        15

There is a limit on what tabulate can do, but on your information I am very surprised to hear that it is biting. Something else is going on that you need to tell us about.


Show us the result of

which tabm

please.

[Marc Klee]

Sorry, Nick. My mistake. When I tried to extract and upload the data so you can replicate the problem, it started to work. And it works fine now.

However, the problem with tabm is that it calculates total-percentages based on a different population that it constructs in the background (probably based on valid cases). When using binary variables as suggested by Ken Chui, results are different. Actually, I need to interpret percentages based on the fixed sample as a reference. So I probably stick to the binary variable solution.

Just to give an impression of the differences in case someone is interested (binary variable constructed for value==14 from encoding_t*-variables, example-data uploaded as csv):

gen binary_14 = 0
forval i=1/5 {
    replace binary_14 = 1 if encoding_t`i'==14
}

tab binary_14


  binary_14 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |         89       89.00       89.00
          1 |         11       11.00      100.00
------------+-----------------------------------
      Total |        100      100.00



tabm encoding_*, transpose col

           |             variable
    values | encoding_  encoding_  encoding_ |     Total
-----------+---------------------------------+----------
        11 |         8          0          0 |         8
           |      8.00       0.00       0.00 |      6.06
-----------+---------------------------------+----------
        12 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        13 |         9          0          0 |         9
           |      9.00       0.00       0.00 |      6.82
-----------+---------------------------------+----------
        14 |         7          4          0 |        11
           |      7.00      14.81       0.00 |      8.33
-----------+---------------------------------+----------
        15 |         2          0          1 |         3
           |      2.00       0.00      20.00 |      2.27
-----------+---------------------------------+----------
        19 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        21 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        24 |         3          0          0 |         3
           |      3.00       0.00       0.00 |      2.27
-----------+---------------------------------+----------
        25 |         6          3          0 |         9
           |      6.00      11.11       0.00 |      6.82
-----------+---------------------------------+----------
        26 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        31 |         6          0          0 |         6
           |      6.00       0.00       0.00 |      4.55
-----------+---------------------------------+----------
        32 |         0          2          0 |         2
           |      0.00       7.41       0.00 |      1.52
-----------+---------------------------------+----------
        35 |         3          0          0 |         3
           |      3.00       0.00       0.00 |      2.27
-----------+---------------------------------+----------
        36 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        37 |         1          0          1 |         2
           |      1.00       0.00      20.00 |      1.52
-----------+---------------------------------+----------
        38 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        41 |         5          0          0 |         5
           |      5.00       0.00       0.00 |      3.79
-----------+---------------------------------+----------
        42 |         4          0          0 |         4
           |      4.00       0.00       0.00 |      3.03
-----------+---------------------------------+----------
        43 |         3          2          0 |         5
           |      3.00       7.41       0.00 |      3.79
-----------+---------------------------------+----------
        44 |         2          2          1 |         5
           |      2.00       7.41      20.00 |      3.79
-----------+---------------------------------+----------
        45 |         0          0          1 |         1
           |      0.00       0.00      20.00 |      0.76
-----------+---------------------------------+----------
        51 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
        52 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        53 |         3          1          0 |         4
           |      3.00       3.70       0.00 |      3.03
-----------+---------------------------------+----------
        54 |         3          1          0 |         4
           |      3.00       3.70       0.00 |      3.03
-----------+---------------------------------+----------
        55 |         1          2          1 |         4
           |      1.00       7.41      20.00 |      3.03
-----------+---------------------------------+----------
        56 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        63 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        64 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
        69 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        71 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
        72 |         3          0          0 |         3
           |      3.00       0.00       0.00 |      2.27
-----------+---------------------------------+----------
        73 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        74 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        75 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        81 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
        82 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
        85 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
        91 |         2          1          0 |         3
           |      2.00       3.70       0.00 |      2.27
-----------+---------------------------------+----------
        95 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
        99 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
       101 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
       111 |         3          1          0 |         4
           |      3.00       3.70       0.00 |      3.03
-----------+---------------------------------+----------
       114 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
       115 |         2          0          0 |         2
           |      2.00       0.00       0.00 |      1.52
-----------+---------------------------------+----------
       116 |         0          1          0 |         1
           |      0.00       3.70       0.00 |      0.76
-----------+---------------------------------+----------
       123 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
       124 |         1          0          0 |         1
           |      1.00       0.00       0.00 |      0.76
-----------+---------------------------------+----------
     Total |       100         27          5 |       132
           |    100.00     100.00     100.00 |    100.00