How to create a count variables for the combination of two categorical variables?

[SAS-questioner]

Hi, I have a two categorical variables (1 to 4). the two categorical are supposed to be the same, but some people didn't chose parts of the answers, that make the second categorical variable has less categories than the first one. The data is like below:

ID  cat1    cat2
 1    1       1
 2    1       2
 3    1       4
 4    2       1
 5    2       2
 6    2       4
 7    3       1
 8    3       2
 9    3       4
10    4       1
11    4       2
12    4       4  

Because no one answered '3' for the cat2, it looks like cat2 only has three categories. I want to create a count data, but also include the '3' for cat2. Below are the data that I wanted:

cat1 cat2  count
  1     1        1
  1     2        1
  1     3        0
  1    4         1
  2    1         1
  2    2         1
  2    3         0
  2    4         1
  3    1         1
  3    2         1
  3   3          0
  3    4         1
  4    1         1
  4    2         1
  4    3         0
  4    4         1   

Because cat2 doesn't have 3, so the count for it always be 0. I tried to use 

proc freq;
table cat1*cat2/list;
run;

But this code doesn't create the "3" if cat2 doesn't have it. Could any one help me with it? Thank you!

[Patrick]

With Proc Freq you would need to create rows for the missing combinations and add it to the table - which is not pretty.

Proc Tabulate is one of the procedures that allows you to use a 2nd data set that defines all combinations of values for class variables that you want to see in a report.

Example 2: Specifying Class Variable Combinations to Appear in a Table

[mkeintz]

You could add a collection of records covering each of the cat1/cat2 combinations.  But assign a weight of zero to this additional obs, and a weight of one to the originals.  As in:

data have;
  input ID  cat1    cat2;
datalines;
 1    1       1
 2    1       2
 3    1       4
 4    2       1
 5    2       2
 6    2       4
 7    3       1
 8    3       2
 9    3       4
10    4       1
11    4       2
12    4       4
run;

data vtemp/view=vtemp;
  set have end=end_of_have;
  retain wgt 1;
  output;
  if end_of_have;
  wgt=0;
  do cat1=1 to 4; do cat2=1 to 4; output; end; end;
run;

proc freq data=vtemp;
  table cat1*cat2 / nopercent norow nocol;
  weight wgt /zeroes;
run;

Note you have to make a WGT variable (=1 for the originals and 0 for the dummy additions) and then use it in the WEIGHT statement.

[Tom]

Some how you have to tell SAS that 3 was a possible answer.

PROC SUMMARY will let you do that by attaching a FORMAT to the variable.

proc format ;
value cat 
 1='1' 2='2' 3='3' 4='4' 
;
run;

proc summary data=have nway completetypes;
  class cat1 cat2 / preloadfmt;
  format cat1 cat2 cat.;
  output out=counts ;
run;

Result

Obs    cat1    cat2    _TYPE_    _FREQ_

  1     1       1         3         1
  2     1       2         3         1
  3     1       3         3         0
  4     1       4         3         1
  5     2       1         3         1
  6     2       2         3         1
  7     2       3         3         0
  8     2       4         3         1
  9     3       1         3         1
 10     3       2         3         1
 11     3       3         3         0
 12     3       4         3         1
 13     4       1         3         1
 14     4       2         3         1
 15     4       3         3         0
 16     4       4         3         1