Counting Missingness with PROC FORMAT and PROC FREQ

Author
Samantha Saykao
Publish Date

The combination of PROC FORMAT and PROC FREQ can be used to quickly identify missingness, or values that are invalid or not present, in data. Straightforward logic and low line count make this code digestible for both new and experienced SAS programmers.

This code works when missingness can be tied to specific values. It defines missingness in PROC FORMAT and then applies that format in PROC FREQ. It’s great for initial observations but may not work with more complicated coding logic due to PROC FORMAT limitations. The following example shows how to combine PROC FORMAT and PROC FREQ.

Coding Example: Finding Blank Values in the SRVC_PRVDR_NPI Variable

 

PROC FORMAT;
    value $missingchar " "="Missing"
                       OTHER="Not Missing";
run;

PROC FREQ data=HAVE;
    tables VARIABLE1 / missing out=WANT;
    format VARIABLE1 $missingchar.;
run;

 

Explaining the Coding Example

In the first block of code, we define a character format called “$missingchar”. Variables are “Missing” if they have a blank value (“ “). All other values are considered “Not Missing”. In the second block of code, we refer to our existing dataset, HAVE, in our PROC FREQ statement. We apply PROC FREQ to VARIABLE1, keeping missing values using the “missing” option. We also create a dataset called WANT based on the PROC FREQ results. In PROC FREQ, we format the VARIABLE1 using our pre-defined format, “$missingchar”. The output will contain a table with counts for the two categories we defined in PROC FORMAT: “Missing” and “Not Missing”.

Summary

The power behind this code is its simplicity. It is useful for pulling generalizations or reviewing output without needing to create additional variables. Once coding requirements start to get more complex, programmers should utilize other tools such as indicator flags or PROC TABULATE.

Tags
analytic guidance
data analysis