SQL Procedure

Example 15: Counting Missing Values with a SAS Macro

Features:	COUNT function
Table name:	SURVEY

This example uses a SAS macro to create columns. The SAS macro is not explained here. See SAS Macro Language: Reference for information about SAS macros.

SURVEY contains data from a questionnaire about diet and exercise habits. SAS enables you to use a special notation for missing values. In the EDUC column, the .x notation indicates that the respondent gave an answer that is not valid, and .n indicates that the respondent did not answer the question. A period as a missing value indicates a data entry error.

Program

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

nmiss(&col) "Missing or NOT VALID Responses for &col",

count(case
            when &col=.n  then "count me"
            end) "Coded as NO ANSWER for &col",
   count(case
            when &col=.x  then "count me"
            end) "Coded as NOT VALID answers for &col",
   count(case
            when &col=.  then "count me"
            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;

Program Description

Count the nonmissing responses.The COUNTM macro uses the COUNT function to perform various counts for a column. Each COUNT function uses a CASE expression to select the rows to be counted. The first COUNT function uses only the column as an argument to return the number of nonmissing rows.

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

Count missing or invalid responses.The NMSS function returns the number of rows for which the column has any type of missing value: .n, .x, or a period.

nmiss(&col) "Missing or NOT VALID Responses for &col",

Count the occurrences of various sources of missing or invalid responses. The last three COUNT functions use CASE expressions to count the occurrences of the three notations for missing values. The “count me” character string gives the COUNT function a nonmissing value to count.

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

Use the COUNTM macro to create the columns.The SELECT clause specifies the columns that are in the output. COUNT(*) returns the total number of rows in the table. The COUNTM macro uses the values of the EDUC column to create the columns that are defined in the macro.

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

Output

Counts for Each Type of Missing Response