proc freq: how to include levels with frequencies equaling 0 in your output

[hein68]

When using proc freq, what code is needed to include levels with frequencies equaling 0 in your output?

Thanks!

[Reeza · Accepted solution]

So you have a value but that value doesn't appear anywhere in your data and you want SAS to put a 0 for that unknown value?

You can look into PRELOADFMT or CLASSDATA within PROC TABULATE/MEANS which deal with this better but a quick trick for PROC FREQ is to use the SPARSE option. BUT...it only works if that value is listed at least once within other categories.
https://blogs.sas.com/content/sastraining/2013/10/18/sas-authors-tip-using-the-sparse-option-with-pr...

https://www.pharmasug.org/proceedings/2012/CC/PharmaSUG-2012-CC26.pdf

[hein68]

Thanks, this did help.

[ballardw]

---

@hein68 wrote:

When using proc freq, what code is needed to include levels with frequencies equaling 0 in your output?

Thanks!

---

I think you need to be a bit more detailed in your question such as providing some example data and exactly what "level" that has a frequency of 0 that should be counted.

Are you talking about a combination of variables or a single variable?

[FreelanceReinh]

Hi @hein68,

Another trick is to use the WEIGHT statement with the ZEROS option:

/* Create test data for demonstration */

data have;
set sashelp.class;
run;

/* Introduce age levels with frequency zero */

data want / view=want;
do until(last);
  set have end=last;
  _w=1;
  output;
end;
do age=5, 18, 99;
  _w=0;
  output;
end;
run;

/* Create frequency table including the new levels */

proc freq data=want;
weight _w / zeros;
tables age;
run;

Output:

                                Cumulative    Cumulative
Age    Frequency     Percent     Frequency      Percent
--------------------------------------------------------
  5           0        0.00             0         0.00
 11           2       10.53             2        10.53
 12           5       26.32             7        36.84
 13           3       15.79            10        52.63
 14           4       21.05            14        73.68
 15           4       21.05            18        94.74
 16           1        5.26            19       100.00
 18           0        0.00            19       100.00
 99           0        0.00            19       100.00