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