Debugging the difference between WHERE and IF in SAS

In the DATA step, the WHERE statement and the IF statement (a.k.a. the “subsetting IF”) have similar functions. In many scenarios, they produce identical results. But new SAS programmers are taught early on that these two statements work very differently, and in important ways. To understand the differences, it helps to step through the program line-by-line to see how SAS “thinks.” Fortunately, the new DATA step debugger in SAS Enterprise Guide 7.13 makes this really easy to do.

Difference between WHERE statement and IF statement

Here are the basics: the WHERE statement rules are determined when the DATA step is compiled. As the DATA step runs, incoming data (from a SET or MERGE statement) is filtered to just those records that match the WHERE condition, so only those records are ever loaded into the program data vector (PDV). This results in fewer iterations through DATA step code, but provides no opportunity for “dynamic” decisions about which records to examine.

In contrast, the IF statement is evaluated at run time, and operates on the variables after they are already in the PDV. When the IF condition is met, the current observation is kept for eventual output. Unlike the WHERE statement, the IF statement can examine values of new variables that are defined within the step.

Consider these two DATA steps. They produce identical output of 10 records, but the first one processes only those 10 records whereas the second step processes all 19 records from the input.

data results1;
  set sashelp.class;
  /* WHERE applied at compile time  */
  /* Processes ONLY matching obs    */
  where sex='M';
run;
 
data results2;
  set sashelp.class;
  /* IF evaluated at run time  */
  /* Processes EVERY obs       */
  if sex='M';
run;

Using the DATA step debugger to understand the DATA step

The new DATA step debugger in SAS Enterprise Guide makes it very easy to illustrate how WHERE is processed differently from IF. I loaded each of the above programs into my session, then clicked the new “bug” toolbar icon to activate the debugger. Once activated, you can click the bug in the left “gutter” of the program editor to begin a debug session. (You can also press F5 to debug the active DATA step.)

Watch this first animation of a debugger session and see what you notice about the WHERE statement.

Watching this little movie, I see a few things that reveal some insights.

• The statement pointer never lands on Line 5 (the WHERE statement). That’s because the WHERE statement isn’t processed at run time.
• Even though the CLASS data contains 19 records, the value of the _N_ automatic variable reaches only 11, indicating that only 10 records were processed.
• The variable watch window uses red to indicate when a variable changes between iterations. The Sex variable never changes from ‘M’, and thus stays colored black through the entire session.

Let’s compare that to the IF statement. Study this animation and see what stands out to you.

Here’s what I see:

• The statement pointer begins at Line 2, then 5, and moves to Line 6 (the RUN statement) only when the record has made it past the IF condition and into the output. For each observation where Sex=’F’, the DATA step stops processing the record and the RUN statement is skipped.
• In this program, _N_ reaches 20 — that’s because all 19 records in SASHELP.CLASS are processed and the step exits at the end-of-file condition.

Learning more about subsetting IF, IF-THEN, WHERE, and debugging

There are several good articles about how the IF statement works, on its own and in combination with IF-THEN-ELSE constructs. Here’s a recent article by SAS trainer Charu Shankar. And here’s another reference that’s included in a piece about the Top 10 SAS coding efficiencies.

The new DATA step debugger in SAS Enterprise Guide opens a new world of understanding for beginner and veteran SAS programmers. It has all of the functions of the “classic” debugger available in the Base SAS windowing environment, but with a much friendlier user interface, keyboard shortcuts, and useful watch windows. In a future post, I’ll cover the debugging functions in more detail.

The post Debugging the difference between WHERE and IF in SAS appeared first on The SAS Dummy.