Creating a single table of multiple variables with identical categories using table and collect

[Tim Cheney]

I'm trying to create a table of multiple variables with identical response categories but can't get it the way I want using collect commands so I am resorting to manipulating the data instead. I would like to figure out how to do it with collect commands. Any suggestions? Thanks.

clear all
input x1 x2 x3
1 1 1
2 2 2
3 1 3
1 1 2
2 2 1
end

label define rating 1 "Meh" 2 "Good" 3 "Super"
label value x1-x3 rating

label var x1 "Pineapple on Pizza"
label var x2 "Manchester United"
label var x3 "SPSS"

/*** I can get this table
. table (var), statistic(fvpercent x1 x2 x3) statistic(fvfrequency x1 x2 x3)

------------------------------------------------------------------------------
                         |  Factor-variable percent   Factor-variable frequency
-------------------------+-----------------------------------------------------
Pineapple on Pizza=Meh   |                    40.00                           2
Pineapple on Pizza=Good  |                    40.00                           2
Pineapple on Pizza=Super |                    20.00                           1
Manchester United=Meh    |                    60.00                           3
Manchester United=Good   |                    40.00                           2
SPSS=Meh                 |                    40.00                           2
SPSS=Good                |                    40.00                           2
SPSS=Super               |                    20.00                           1
-------------------------------------------------------------------------------

But I want to produce this table:

---------------------------------------------------------
                     |               Response            
                     |     Meh         Good       Super  
                     |      %   N       %   N       %   N
---------------------+-----------------------------------
Item Rated           |                                   
  Pineapple on Pizza |  40.00   2   40.00   2   20.00   1
  Manchester United  |  60.00   3   40.00   2            
  SPSS               |  40.00   2   40.00   2   20.00   1
---------------------------------------------------------
*****/



*** Here is my workaround:
*** loop over variables and create a table for each one
*** would like to avoid creating a temporary variable but
*** that is my solution to combining them into a single dimension
*** I would like to find a way to do it after the collect combine instead
collect clear
foreach var of varlist x1-x3 {
capture drop _var
gen _var = `var'
local val : value label `var'
label value _var "`val'"
table () (var) , statistic(fvpercent _var) statistic(fvfrequency _var)
collect rename Table `var'
}

** Combine the tables
collect combine New = x1 x2 x3
*** copy the variable labels to the collection labels
foreach var of varlist x1 x2 x3 {
local vlab: var label `var'
collect label levels collection `var' "`vlab'"
}

collect label levels result fvfrequency "N" fvpercent "%", modify
collect label dim _var "Response"
collect label dim collection "Item Rated"
collect layout (collection) (_var#result)

[Nick Cox]

Not your question, but tabm from tab_chi on SSC can do this:

Stata 8.2 required.

clear all
input x1 x2 x3
1 1 1
2 2 2
3 1 3
1 1 2
2 2 1
end

label define rating 1 "Meh" 2 "Good" 3 "Super"
label value x1-x3 rating

label var x1 "Pineapple on Pizza"
label var x2 "Manchester United"
label var x3 "SPSS"



. tabm x? 

                   |              values
          variable |       Meh       Good      Super |     Total
-------------------+---------------------------------+----------
Pineapple on Pizza |         2          2          1 |         5 
 Manchester United |         3          2          0 |         5 
              SPSS |         2          2          1 |         5 
-------------------+---------------------------------+----------
             Total |         7          6          2 |        15 

. 
. tabm x?, row

+----------------+
| Key            |
|----------------|
|   frequency    |
| row percentage |
+----------------+

                   |              values
          variable |       Meh       Good      Super |     Total
-------------------+---------------------------------+----------
Pineapple on Pizza |         2          2          1 |         5 
                   |     40.00      40.00      20.00 |    100.00 
-------------------+---------------------------------+----------
 Manchester United |         3          2          0 |         5 
                   |     60.00      40.00       0.00 |    100.00 
-------------------+---------------------------------+----------
              SPSS |         2          2          1 |         5 
                   |     40.00      40.00      20.00 |    100.00 
-------------------+---------------------------------+----------
             Total |         7          6          2 |        15 
                   |     46.67      40.00      13.33 |    100.00 

. 
. tabm x?, row col

+-------------------+
| Key               |
|-------------------|
|     frequency     |
|  row percentage   |
| column percentage |
+-------------------+

                   |              values
          variable |       Meh       Good      Super |     Total
-------------------+---------------------------------+----------
Pineapple on Pizza |         2          2          1 |         5 
                   |     40.00      40.00      20.00 |    100.00 
                   |     28.57      33.33      50.00 |     33.33 
-------------------+---------------------------------+----------
 Manchester United |         3          2          0 |         5 
                   |     60.00      40.00       0.00 |    100.00 
                   |     42.86      33.33       0.00 |     33.33 
-------------------+---------------------------------+----------
              SPSS |         2          2          1 |         5 
                   |     40.00      40.00      20.00 |    100.00 
                   |     28.57      33.33      50.00 |     33.33 
-------------------+---------------------------------+----------
             Total |         7          6          2 |        15 
                   |     46.67      40.00      13.33 |    100.00 
                   |    100.00     100.00     100.00 |    100.00

.

[Jeff Pitblado (StataCorp)]

You can either reshape your data (temporarily).

clear all

input x1 x2 x3
1 1 1
2 2 2
3 1 3
1 1 2
2 2 1
end

label define rating 1 "Meh" 2 "Good" 3 "Super"
label value x1-x3 rating

label var x1 "Pineapple on Pizza"
label var x2 "Manchester United"
label var x3 "SPSS"

* temporarily reshape the data
preserve

* data is in wide format, so define an observation identifier, then
* reshape to long format
gen id = _n
reshape long x, i(id) j(item)

* define some value/variable labels for -table- to pick up
label define item ///
    1 "Pineapple on Pizza" ///
    2 "Manchester United" ///
    3 "SPSS" 
label value item item
label var item "Item rated"
label var x "Response"

* predefine your custom result labels
collect label levels result fvpercent "%" fvfrequency "N"
collect label save mylabs, replace

* perform the calculations and arrange them as requested
table (item) (var result), ///
    stat(fvpercent x)  ///
    stat(fvfrequency x) ///
    label(mylabs) ///
    nototal

* restore original data
restore

This yields the table.

---------------------------------------------------------
                     |               Response
                     |     Meh         Good       Super
                     |      %   N       %   N       %   N
---------------------+-----------------------------------
Item rated           |
  Pineapple on Pizza |  40.00   2   40.00   2   20.00   1
  Manchester United  |  60.00   3   40.00   2    0.00   0
  SPSS               |  40.00   2   40.00   2   20.00   1
---------------------------------------------------------

Or manipulate the tags attached to the items in the collection produced by your original call to table.

clear all

input x1 x2 x3
1 1 1
2 2 2
3 1 3
1 1 2
2 2 1
end

label define rating 1 "Meh" 2 "Good" 3 "Super"
label value x1-x3 rating

label var x1 "Pineapple on Pizza"
label var x2 "Manchester United"
label var x3 "SPSS"

table (var), statistic(fvpercent x1 x2 x3) statistic(fvfrequency x1 x2 x3)

* add new tag elemens for a rating dimension
collect addtags rating[Meh], fortags(var[1.x1 1.x2 1.x3])
collect addtags rating[Good], fortags(var[2.x1 2.x2 2.x3])
collect addtags rating[Super], fortags(var[3.x1 3.x2 3.x3])
collect label dim rating "Response"

* add new tag elements for the items rated dimension
collect addtags item["Pineapple on Pizza"], fortags(var[i.x1])
collect addtags item["Manchester United"], fortags(var[i.x2])
collect addtags item["SPSS"], fortags(var[i.x3])
collect label dim item "Item rated"

* custom result labels
collect label levels result fvpercent "%" fvfrequency "N", modify

collect layout (item) (rating#result)

This yields the table.

---------------------------------------------------------
                     |               Response
                     |     Meh         Good       Super
                     |      %   N       %   N       %   N
---------------------+-----------------------------------
Item rated           |
  Pineapple on Pizza |  40.00   2   40.00   2   20.00   1
  Manchester United  |  60.00   3   40.00   2
  SPSS               |  40.00   2   40.00   2   20.00   1
---------------------------------------------------------

Here is some extra code to fill in for empty cells.

collect style cell result[fvpercent], empty(0.00)
collect style cell result[fvfrequency], empty(0)
collect preview

This yields the following table.

---------------------------------------------------------
                     |               Response
                     |     Meh         Good       Super
                     |      %   N       %   N       %   N
---------------------+-----------------------------------
Item rated           |
  Pineapple on Pizza |  40.00   2   40.00   2   20.00   1
  Manchester United  |  60.00   3   40.00   2    0.00   0
  SPSS               |  40.00   2   40.00   2   20.00   1
---------------------------------------------------------

[Tim Cheney]

Nick thanks I appreciate the response. However in our work flow I need to get my output into a Word Doc in a table.
Jeff thanks the fortags is what I need but I am having a hard time understanding the concept. Your example will help me work through it.