Often the variables in your dictionary do not map exactly to the categories desired in your tables and you need to "recode" one or more variables to map it to the groupings you want in the rows and columns of your table. Recodes involving a single variable can be accomplished by creating additional value sets while recodes of multiple variables can be accomplished using multiple subtables.
Single variable recodes
To recode a single variable, simply create an additional value set for that variable in your dictionary, which regroups the values as desired. Then drag this new value set onto the table.
For example if you have the following value set for marital status but would like to count only two categories, Married and Unmarried, in your table.
Create a new value set that regroups the original values for marital status into these new categories.
Now drag the new value set onto your table.
Value sets may have disjoint values, one category which contains values from different ranges, for example, one category for values 23, 45, and 67-71 (see Tabulate categories with disjoint values).
Multiple variable recodes
To do a recode using multiple variables, create multiple subtables by dragging the variables onto the table multiple times and set universes on these subtables. For example, if you want to tabulate houses that have "complete plumbing", meaning that they have piped water, bathing facilities and a toilet inside the housing unit. This involves the following three variables:
The "recoded" variable, "complete plumbing", has three categories:
Complete – piped water inside the unit (source of water = 1), and a private toilet inside the unit (type of toilet = 1) and bathing facilities inside the unit (type of bathing = 1).
Some but not all – any one of the three variables (piped water, flush toilet, bathing facilities) inside the unit but not all three.
None – all other cases.
To create the table for complete plumbing, use 3 subtables, one for each of the 3 possible values of the recoded variable (complete, some but not all, none). Each of these subtables should have only one row. The easiest way to create a subtable with one row is to drag a variable whose value set has only one category onto the table. If no such value set exists, you can create one. In this case, create the value set on the variable "source of water". In fact, you could use any variable on the same record as the variables we are using in the recode (the housing record in this case). Note that the single category in this value set must include all valid values for the variable so that all housing units will be counted when tabulating the variable.
Drag this new value set onto the table 4 times, once for each of three possible values of complete plumbing and once for the total.
Since we only need one row for each subtable, hide the system generated total in each of the four subtables (see Hide or Change the Position of the Total). Also hide the captions for each of the subtables (see Formats for a Part of a Table).
Next customize the text in the stubs to match the categories for the recode (Total, Complete, Some but not all, None). Also edit the title of the table and put the name of the variable in the Stub Head. See Customize Table Text for how to edit text in the table.
Now set the universes for the subtables to correspond to the appropriate values of the complete plumbing recode. The first subtable will be the total so it should use the default universe, which includes all cases. The second subtable represents "complete plumbing" and must include only those cases where piped water, flush toilet AND bathing facilities all equal 1. The third subtable represents "some but not all" and must include the cases where one or two of the variables are equal to 1 but not all of them. The fourth subtable represents "none" and must include all cases where none of the variables are equal to 1. For more information on setting the universe on a subtable see Restrict a Universe. This produces the following table:
Note that often it is preferable to create recoded variables in a batch edit program rather than during tabulation. This recode could have been accomplished by adding a new variable to the dictionary for complete plumbing and writing a batch edit program to set the value of this variable for each case. Then the new complete plumbing variable could be dropped on the table directly rather than creating multiple subtables with universes. This makes the creation of the table much simpler although it involves creating a batch edit application and writing a small amount of logic. Using batch edit is best when the recoded variable will be used in multiple tables.
Another alternative would be to create the recoded variable in the working storage dictionary and use tablogic to set its value to each case. See Table Logic (tablogic) for more information.