December 2007 Technical Tip SAS: Special Missing Values

The use of missing values in SAS is similar to the use of nulls in relational databases: missing values are used to indicate the absence of a value within an observation. For numeric fields, a missing value is indicated by a period. But SAS also allows "special" missing values for numeric fields. With special missing values we could, for example, record the reason for the absence of a value. In this article I will show you how to use special missing values.

Consider the following data step which contains survey data (three responders, three questions, and three possible responses to each question):


data survey;
missing response M U;  /* M = multiple, U = unreadable */
input responder question response;
datalines;
1 1 1
1 2 2
1 3 B
2 1 U
2 2 3
2 3 2
3 1 .
3 2 B
3 3 1
;

Numeric fields can contain a single alphabetic character: these characters are known as special missing values. The MISSING statement is required if special missing values are used. In the above example, M is used to indicate multiple responses (not allowed) and U is used to indicate an unreadable response. "Normal" missing values (period only) are still allowed and in this case would indicate the question was left blank.

So how do you select based on special missing values?

where response is missing; All missing values ("normal" and "special")
where response EQ . ; "Normal" missing values only
where response EQ .M ; Special missing values M only
where response in (.M .U) ; Special missing values M and U only

If the MISSING option is used in PROC FREQ, you'll get a breakdown for each type of missing value. For example, given (without MISSING):


proc freq data=survey;
tables question*response / nopercent nocol norow;

...the output is as follows:


    question     response

    Frequency|       1|       2|       3|  Total
    ---------+--------+--------+--------+
           1 |      1 |      0 |      0 |      1
    ---------+--------+--------+--------+
           2 |      0 |      1 |      1 |      2
    ---------+--------+--------+--------+
           3 |      1 |      1 |      0 |      2
    ---------+--------+--------+--------+
    Total           2        2        1        5

                Frequency Missing = 4

But given (with MISSING):


proc freq data=survey;
tables question*response / nopercent nocol norow missing;

...the output is as follows:


    question     response

    Frequency|       .|       M|       U|       1|       2|       3|  Total
    ---------+--------+--------+--------+--------+--------+--------+
           1 |      1 |      0 |      1 |      1 |      0 |      0 |      3
    ---------+--------+--------+--------+--------+--------+--------+
           2 |      0 |      1 |      0 |      0 |      1 |      1 |      3
    ---------+--------+--------+--------+--------+--------+--------+
           3 |      0 |      1 |      0 |      1 |      1 |      0 |      3
    ---------+--------+--------+--------+--------+--------+--------+
    Total           1        2        1        2        2        1        9

We hope you will consider Caliber Data Training when deciding upon a SAS training provider.


Go to the articles index. Written by Bill Qualls. Copyright © 2007 by Caliber Data Training 800.938.1222