List variables with zero frequencies - Proc Freq or Proc Tabulate

Question

I am selecting a group of zipcodes to tabulate frequency counts by age group via a two by two table. I would like to list the zipcodes with zero frequency counts so that the whole group of selected zipcodes and the whole set of possible combinations of age groups (there are 5 age groups) appear in the final table.

Here is the code that I have tried using Proc Freq. This still currently does not list all of the possible combinations.

proc freq data = join;
where group_1 = 1 and ZIP in ('20814' '20815' '20816' '20817' '20832' 
'20850' '20851' '20852' '20853' '20866') and Race_n = 'NH-Black';
tables ZIP*agegrp / nocol norow nopercent sparse list;
title "Disease Mortality Counts 2016 By Race";
run;

One of the age groups doesn't have any counts, so the proc freq does not list that age group at all. — tipscode
And that age group is somewhere in the data? If it's not in the data whatsoever you need to use a CLASSDATA or PRELOADFMT approach instead. Can you provide sampled data where this occurs? — Reeza

Richard Richard · Accepted Answer · 2019-07-12T15:10:17

Proc TABULATE

You need a classdata table that lists all possible values of the class combinations.

For example:

data all_ages;
  do age = 18 to 65;
    output;
  end;
run;

data patients;
  do patid = 1 to 10000;
    do until (age not in (19, 23, 29, 31, 37, 41, 43,  47, 53, 59));
      age = 18 + int((65-17) *ranuni(123));
    end;
    output;
  end;
run;

proc format;
  value misszero .=0 other=[best12.];

proc tabulate data=patients classdata=all_ages;
  class age ;
  table age, n*f=misszero.;
run;

Proc FREQ

Amend the data with the classdata and assign a weight of zero to the classdata items. Allow zeros as weight in the weight statement.

data patients_v;
  set
    patients
    all_ages (in=zero)
  ;
  unity = 1 - zero;
run;

proc freq data=patients_v;
  table age;
  weight unity / zeros ;
run;

List variables with zero frequencies - Proc Freq or Proc Tabulate

1 Answers