WHERE and IF Statements in SAS

Master the difference between WHERE and IF Statements in SAS for efficient data filtering. Learn when to use WHERE statements for speed on existing variables and IF statements for new variables. Our guide includes syntax examples, performance tips, and helps you avoid common subsetting errors to clean your datasets faster.

Differentiate between WHERE and IF Statements in SAS

There is a fundamental difference between WHERE and IF Statements in SAS. The breakdown of differences between WHERE and IF statements in SAS is as follows:

Where Statement in SAS

The WHERE statement in SAS acts as a filter at the source. It tells SAS only to read observations that meet a specific condition from the input dataset(s). This happens very early in the process, often before the Program Data Vector (PDV) is fully constructed.

When to use WHERE Statement in SAS: Almost always for simple filtering, especially when working with large datasets, as it significantly improves performance by reducing I/O.

Example 1: Basic Filtering in a DATA Step

data high_earners;
    set sashelp.class;
    where age > 13; /* Only reads observations where Age is >13 */
run;

In this case, observations where age <= 13 are never even loaded into the PDV for processing.

Example 2: Using a WHERE Dataset Option

This is very powerful in procedures or when merging.

proc print data=sashelp.class(where=(sex='F')); /* Prints only females */
run;

data combined;
    merge ds1(where=(valid=1)) /* Merge only valid records from ds1 */
          ds2;
    by id;
run;

IF Statement in SAS

The IF statement in SAS is a processing-time filter. The entire observation is read into the PDV, all variables are calculated, and then the IF condition is evaluated. If the condition is false, the OUTPUT statement is bypassed (for that observation), and SAS returns to the beginning of the Data Step to process the next observation.

When to use IF Statement in SAS: When your filtering condition involves variables created within the Data Step or requires complex logic that must be executed row-by-row.

Example 1: Filtering on a New Variable

data tall_people;
    set sashelp.class;
    height_inches = height * 2.54;      /* Create a new variable */
    if height_inches > 64;                     /* Subsetting IF statement;*/
                                                              /* Equivalent to: if height_inches <= 64 then delete; */
run;

This works perfectly because the new variable height_inches exists in the PDV by the time the IF statement is executed.

Example 2: Complex Row-by-Row Logic

data flagged_records;
    set mydata;
    if some_var = . then do;        /* Check for missing value */
        error_flag = 'M';                   /* Set a flag */
        error_count + 1;                  /* Increment a counter */
    end;
    if error_flag = 'M';                    /* Output only the records with errors */
run;

This kind of multi-step logic is not possible with a WHERE statement.

What is the Special Case of Subsetting IF vs DELETE Statements?

A common use of an IF statement is the subsetting IF (if condition;). This outputs an observation only if the condition is true. Its logical opposite is if condition then delete; which deletes an observation if the condition is true.

data adults;
    set people;
    if age >= 18; * Output if true;
run;

/* Is logically equivalent to: */

data adults;
    set people;
    if age < 18 then delete; * Delete if true;
run;

WHERE or IF Statement: Which One to Use?

• Use WHERE when:
  • You are filtering based on variables that exist in the input dataset.
  • You want the most efficient processing, especially for large data.
  • You are working in a PROC step (like PROC PRINT, PROC SORT).
  • You want to use special operators like CONTAINS or LIKE.
• Use IF when:
  • You need to filter based on a variable created within the same Data Step.
  • Your filtering logic is complex and requires other SAS statements (like DO loops or ARRAY processing).
  • You are already reading every observation into the PDV for other necessary calculations, and the efficiency gain of WHERE is negligible.

The WHERE statement can be used …. IF statement cannot be used

• WHERE statement can be used in procedures to subset data, while the IF statement cannot be used in procedures.
• WHERE can be used as a data set option, while IF cannot be used as a data set option.
• WHERE statement is more efficient than the IF statement. It tells SAS not to read all observations from the data set
• WHERE statement can be used to search for all similar character values that sound alike, while the IF statement cannot be used.
• WHERE statement can not be used when reading data using the INPUT statement, whereas the IF statement can be used.
• Multiple IF statements can be used to execute multiple conditional statements
• When it is required to use newly created variables, use an IF statement, as it doesn’t require variables to exist in the READIN data set.  

What is the one statement to set the criteria of data that can be coded in any step?

A WHERE statement can set the criteria for any data set in a data step or a proc step.

General Knowledge Quizzes