Return to Homepage

Goto the Tip of the Month Archive

Other interesting pages ...
LinkedIn Profile
SAS Cheat Sheet
Useful SAS Code
Full SAS Example
Basic Statistics
Contact Information

SAS Tip of the Month
August 2015
(for SAS)

How do we put a ‘0’ in our report for a category that has no records? For example, there are age groups 0-17, 18-65, and >65, but you have no records with a age of 0-17?

Lets first look at some data as an example:

  data _agegrp;
     infile cards;
     input age @@;
     if age>65 then agegrp=3;
     else if age>=18 then agegrp=2;
     else if n(age) then agegrp=1;
  cards;
  33 39 52 73 47 45 26 57 82 90
  ;
  run;

and this results in the following output table:

  Obs  age  agegrp

   1    33     2 
   2    39     2
   3    52     2
   4    73     3
   5    47     2
   6    45     2
   7    26     2
   8    57     2
   9    82     3
  10    90     3

Also for the purposes of this article, the following format will be applied:

  proc format;
   value agegrpf
      1='0-17'
      2='18-65'
      3='>65';
  run;

One way to make sure all age groups are represented in the output is to get the frequency, merge it with a dummy table showing all possible results and then use a PROC PRINT to do the output, as shown below:

  proc freq data=_agegrp;
     tables agegrp /noprint out=_tmp;
  data _allpos;
     do agegrp=1 to 3;
        output;
     end;
  data _final;
     merge _allpos _tmp;
     by agegrp;
     count=max(0,count);
  proc print data=_final;
     var agegrp count;
     format agegrp agegrpf.;
  run;

with the following output:

  Obs    agegrp    COUNT
   1     0-17        0
   2     18-65       7
   3     >65         3

While this works, it does involve four steps – lets see if we can do it in one!

Lets look at PROC TABULATE. There are two options that are useful for this, PRINTMISS and PRELOADFMT, as the following code demonstrates:

  proc tabulate data=_agegrp;
     format agegrp agegrpf.;
     class agegrp /preloadfmt;
     var age;
     table agegrp='Age Group',age=''*n='N'*f=12.
          /printmiss;
  run;

with the output

  -----------------------------
  |              |     N      |
  |--------------+------------|
  |Age Group     |            |
  |--------------|            |
  |0-17          |           0|
  |--------------+------------|
  |18-65         |           7|
  |--------------+------------|
  |>65           |           3|
  -----------------------------

Related is using PROC REPORT and the COMPLETEROWS and, again, the PRELOADFMT options, as the following code demonstrates:

  proc report data=_agegrp completerows nowindows; 
     format agegrp agegrpf.;
     column agegrp age;
     define agegrp /group order=internal preloadfmt width=10 'Age Group'; 
     define age / analysis n 'N' width=5;
     quit;
  run;

with the output

   Age Group      N
       0-17       0
       18-65      7
       >65        3

Next question, can’t PROC MEANS do it? Answer is yes with the COMPLETETYPES, and, again, the PRELOADFMT options, as the following code demonstrates:

  proc means data=_agegrp completetypes n;
     format agegrp agegrpf.;
     class agegrp /preloadfmt;
     var age;
  run;

with the following output

  The MEANS Procedure
  Analysis Variable : age

              N
  agegrp    Obs     N
  -------------------
  0-17        0     0
  18-65       7     7
  >65         3     3
  -------------------

Presented have been three methods that can put a ‘0’ in our report for a category that has no records, in one step.

Hope this was useful.

________________________________
Updated August 2, 2015