7.5 Handling Missing Values & Preventing Calculation Propagations

7.5 Handling Missing Values & Preventing Calculation Propagations

Missing values are a fundamental reality of data processing. A missing value represents the absence of data for a specific variable in an observation. In Base SAS programming, missing values behave in very specific ways during arithmetic calculations, conditional evaluations, and sorting operations. Misunderstanding missing value propagation is one of the most common causes of logic errors on the Base SAS certification exam.

---

1. Representation of Missing Values in SAS

SAS distinguishes between numeric and character missing values in internal memory and dataset storage:

Special Numeric Missing Values (.A through .Z and ._)

In addition to the standard period (.), SAS supports 27 special numeric missing values: .A through .Z and ._ (underscore). These are used in survey research, clinical trials, and data warehousing to encode specific reasons why data is missing (e.g., .N = Not Applicable, .R = Refused to Answer).

When sorted, special numeric missing values follow a strict hierarchy:

._ < . < .A < .B < ... < .Z < Negative Numbers < 0 < Positive Numbers

---

2. Missing Values in Logical Comparisons & Subsetting

Because numeric missing values (.) evaluate as smaller than all negative numbers, failing to account for missing values in IF or WHERE clauses leads to critical logic errors.

/* CRITICAL EXAM PITFALL: Missing values in comparison logic */
data work.youth;
    set work.patients;
    if age < 18 then status = 'Minor'; /* LOGIC ERROR! */
run;

Why is this a logic error? If age is missing (.), SAS evaluates . < 18 as TRUE because . is smaller than 18. Consequently, all patients with missing ages are incorrectly classified as 'Minor'!

Safe Subsetting Coding Patterns

To prevent missing values from satisfying numeric comparison filters, use explicit missing value checks or range conditions:

/* Safe Pattern 1: Using the MISSING() function */
data work.youth_correct;
    set work.patients;
    if not missing(age) and age < 18 then status = 'Minor';
run;

/* Safe Pattern 2: Using compound comparison bounds */
data work.youth_correct2;
    set work.patients;
    if 0 <= age < 18 then status = 'Minor';
run;

---

3. Arithmetic Expressions vs. SAS Summary Functions

The most heavily tested concept regarding missing values is the distinction between using standard arithmetic operators (+, -, *, /) versus SAS sample statistic functions (SUM(), MEAN(), MIN(), MAX()).

/* Comparing Arithmetic Operators vs. SAS Functions */
data work.calc_demo;
    input Q1 Q2 Q3;
    
    /* Method A: Arithmetic Expression */
    Total_Expr = Q1 + Q2 + Q3;
    
    /* Method B: SAS Summary Function */
    Total_Func = sum(Q1, Q2, Q3);
    Avg_Func   = mean(Q1, Q2, Q3);
    
    datalines;
10 20 30
10 .  30
.  .  .
;
run;

Detailed Evaluation Breakdown

Rules for Arithmetic Propagation vs. Summary Functions

1. Arithmetic Expression Rule: If any operand in a standard expression (+, -, *, /) is missing, the entire result propagates as missing (.), and SAS writes a note to the log: NOTE: Missing values were generated as a result of performing an operation on missing values.
2. SAS Function Rule: SAS summary functions (SUM, MEAN, MIN, MAX) automatically ignore missing values among their arguments. The function calculates the statistic using only non-missing values.
3. All-Missing Exception: A summary function returns a missing value (.) only if all arguments passed to it are missing.
4. Denominator Adjustment in MEAN(): The MEAN() function computes the arithmetic average of non-missing arguments only. In Row 2 above, MEAN(10, ., 30) divides 40 by 2 (the count of non-missing items), yielding 20, rather than dividing by 3.

---

4. System Options & The MISSING Statement

SAS provides tools to customize missing value display and handling across reports and DATA steps.

A. System Option MISSING

By default, numeric missing values print as a period (.) in PROC PRINT and PROC REPORT. The OPTIONS MISSING= global statement changes the character used to display numeric missing values in output reports:

/* Display numeric missing values as a blank space in printed reports */
options missing=' ';

/* Display numeric missing values as 'N/A' (single character limit in Base SAS) */
options missing='N';

B. The MISSING Statement in DATA Steps

When reading raw data files containing custom missing value characters (e.g., 'N' or 'a'), the MISSING statement informs SAS to interpret those specific characters as special numeric missing values during ingestion:

data work.survey;
    missing N R;
    input id score1 score2;
    datalines;
101 85 90
102 N  95
103 78 R
;
run;

In this example, record 102 will store score1 as special missing value .N, and record 103 will store score2 as .R.