It is not uncommon for a cross tabulation of two variables to produce cells
with zero counts. In these cases, the output for proc freq with the
list option will omit combinations of variable values that have zero counts.
For example, in the sample data below, there are no cases where gender = 2 and
eth = 2. The dataset below contains three variables. Two variables,
gender and eth, are the categorical variables we want to cross tabulate. The
third variable is count, which is a frequency weight,
indicating the number of cases with that pattern in the dataset, i.e., there are
21 cases where gender=1 and eth=3.
data test; input gender eth count; datalines; 1 1 12 1 2 12 1 3 21 2 1 2 2 3 43 ; run;
When we run proc freq without the list option, the table
includes the cell with a zero count (i.e., gender=2 and eth=2).
The weight statement specifies that the variable count should be
used to determine the number of cases with each pattern in the dataset.
proc freq data = test;
weight count;
table gender*eth;
run;
The FREQ Procedure
Table of gender by eth
gender eth
Frequency
Percent
Row Pct
Col Pct 1 2 3 Total
1 12 12 21 45
13.33 13.33 23.33 50.00
26.67 26.67 46.67
85.71 100.00 32.81
2 2 0 43 45
2.22 0.00 47.78 50.00
4.44 0.00 95.56
14.29 0.00 67.19
Total 14 12 64 90
15.56 13.33 71.11 100.00
Running proc freq with the list option as shown below, however, produces a table without
a line for gender=2 and eth=2, because the count for this combination is zero.
proc freq data = test;
weight count;
table gender*eth /missprint list;
run;
Cumulative Cumulative
gender eth Frequency Percent Frequency Percent
1 1 12 13.33 12 13.33
1 2 12 13.33 24 26.67
1 3 21 23.33 45 50.00
2 1 2 2.22 47 52.22
2 3 43 47.78 90 100.00
It is possible, with a little work, to get a table such as the one above, that
does contain the missing cell. The first step is to create a variable that is a constant
equal to one. We do this below with the variable called one.
data test; set test; one = 1; run;
Next we will use ods to capture the output from running proc means.
The option summary = t instructs SAS to store the summary produced by proc means in
a dataset t. In the proc means command, the n option specifies that only the n (i.e., the count
of cases) should be computed. The completetypes option specifies that all
combinations of the class variables (gender and eth) should be
listed, including those with zero counts in the dataset; otherwise, they would be
omitted. The freq option specifies that the variable count should be used as the
number of cases that follow a given pattern in the dataset (i.e., in this
example, the freq option does in proc means what weight did in proc freq).
The var statement indicates the variable about which statistics should be
calculated; this is where the variable one is used.
ods output summary = t;
proc means data = test n completetypes;
class gender eth;
freq count;
var one;
run;
The MEANS Procedure
Analysis Variable : one
gender eth N Obs N
1 1 12 12
2 12 12
3 21 21
2 1 2 2
2 0 0
3 43 43
This creates a dataset t (shown below) with six observations, one for each combination of
eth by gender both of which appear as variables in our new dataset.
The new dataset also contains a variable called one_N,
which is the number of cases that fall into each of the categories of gender
by eth (including a count of 0 where appropriate).
proc print data=t; run; Obs gender eth NObs one_N 1 1 1 12 12 2 1 2 12 12 3 1 3 21 21 4 2 1 2 2 5 2 2 0 0 6 2 3 43 43
Now we can run proc freq on the new dataset t. The weight
statement uses one_N as the variable containing the count of cases with a
given pattern of variables. The /zeros option instructs SAS to
include in the list cases in which one_N = 0. The resulting list
includes a row for the combination of gender and eth with a zero
count.
proc freq data = t;
table gender*eth /list;
weight one_N /zeros;
run;
The FREQ Procedure
Cumulative Cumulative
gender eth Frequency Percent Frequency Percent
1 1 12 13.33 12 13.33
1 2 12 13.33 24 26.67
1 3 21 23.33 45 50.00
2 1 2 2.22 47 52.22
2 2 0 0.00 47 52.22
2 3 43 47.78 90 100.00