The SQL Procedure

Example 15: Counting Missing Values with a SAS Macro

Procedure feature:

COUNT function

Table:

SURVEY

This example uses a SAS macro to create columns. The SAS macro is not explained here. See the SAS Guide to Macro Processing for complete documentation on the SAS COUNTM macro.

Input Table

data survey; input id $ diet $ exer $ hours xwk educ; datalines; 1001 yes yes 1 3 1 1002 no yes 1 4 2 1003 no no . . .n 1004 yes yes 2 3 .x 1005 no yes 2 3 .x 1006 yes yes 2 4 .x 1007 no yes .5 3 . 1008 no no . . . ;

Program

options nodate pageno=1 linesize=80 pagesize=60;

 %macro countm(col);
    count(&col) "Valid Responses for &col",

   count(case
            when &col is missing  then put(&col, 2.)
            end) "Missing or NOT VALID Responses for &col",

   count(case
            when &col=.n  then put(&col, 2.)
            end) "Coded as NO ANSWER for &col",
   count(case
            when &col=.x  then put(&col, 2.)
            end) "Coded as NOT VALID answers for &col",
   count(case
            when &col=.  then put(&col, 1.)
            end) "Data Entry Errors for &col"
%mend;

proc sql;
   title 'Counts for Each Type of Missing Response';
   select count(*)  "Total No. of Rows",
          %countm(educ)
      from survey;

Output

Chapter Contents
Previous
Next
Top of Page