Generating a data set that includes zero counts

[EmilyAV]

I have a dataset organized as follows:

data have;

infile id group1 $ group2 $;

datalines

1 a1 b1

2 a1 b2

3 a2 b1

;

run;

Group1 contains 50 categories with no missing values.

Group2 contains 2 categories and some missing values.

I need to generate a dataset that includes counts for all combinations of group 1 and group 2, including an observation of 0 when group 2 has a missing value.

I've been using proc freq as follows:

proc freq data=have;
tables group1*group2 / crosslist missing out=want;
run;

This code displays the results that I'm looking for, but the generated dataset does not include observations where group1*group2 counts are missing. The result is not changed by using the missprint option. Is there another approach I might try?

Thank you.

[PaigeMiller · Accepted solution]

Use the SPARSE option in the TABLES statement of PROC FREQ

[EmilyAV]

Thank you!