Most of the time WHERE and IF give the same answer, so people treat them as interchangeable. They are not. The difference is when the filter runs, and that timing decides which variables you can test and what comes out of a merge. This post shows both on a small ADSL and AE example, including the case where the choice changes your row count.
The one rule behind every difference
WHERE is applied as SAS reads the input dataset. Records that fail the condition never enter the step. A subsetting IF runs inside the step, on a record that has already been read and combined. Every row in this table follows from that:
| Question | WHERE | Subsetting IF |
|---|---|---|
| Works in PROC steps (PROC PRINT, PROC FREQ...) | Yes | No |
| Available as a dataset option | Yes, WHERE= | No |
| Can test a variable created in the same step | No | Yes |
Can test _N_, FIRST.x, LAST.x, IN= variables | No | Yes |
| Works with data read by INPUT | No | Yes |
Special operators: BETWEEN-AND, CONTAINS, LIKE, IS MISSING | Yes | No |
| In a MERGE, applied | Before records are combined | After records are combined |
Test data
data adsl;
input usubjid $ trt01a $ saffl $;
datalines;
1001 Drug Y
1002 Placebo Y
1003 Drug N
;
run;
data ae;
input usubjid $ aeseq aedecod :$12. aesev :$8.;
datalines;
1001 1 Headache MILD
1001 2 Nausea SEVERE
1002 1 Rash MODERATE
1003 1 Dizziness SEVERE
;
run;
Subject 1003 is not in the safety population but has a severe event. That detail matters below.
WHERE cannot see what the step creates
data ae_num;
set ae;
if aesev = 'SEVERE' then aesevn = 3;
where aesevn = 3; /* wrong: aesevn does not exist in WORK.AE */
run;
The log stops the step:
ERROR: Variable aesevn is not on file WORK.AE.
Replace the WHERE with if aesevn = 3; and it works, because the IF runs after the assignment. The same applies to _N_, FIRST. and LAST. variables and IN= flags: they only exist inside the step, so only IF can test them.
The case that changes your row count: MERGE
Task: list severe adverse events with the subject's treatment. Here are two programs that look equivalent.
/* A: filter AE before the merge */
data sev_a;
merge adsl(in=insl) ae(where=(aesev = 'SEVERE'));
by usubjid;
if insl;
run;
/* B: filter after the merge */
data sev_b;
merge adsl(in=insl) ae;
by usubjid;
if insl and aesev = 'SEVERE';
run;
library(dplyr)
# A: filter AE before the join
sev_a <- adsl %>%
left_join(filter(ae, AESEV == "SEVERE"), by = "USUBJID")
# B: filter after the join
sev_b <- adsl %>%
left_join(ae, by = "USUBJID") %>%
filter(AESEV == "SEVERE")
Program A returns every ADSL subject, with AE columns filled only where a severe event exists:
| USUBJID | TRT01A | SAFFL | AESEQ | AEDECOD | AESEV |
|---|---|---|---|---|---|
| 1001 | Drug | Y | 2 | Nausea | SEVERE |
| 1002 | Placebo | Y | . | ||
| 1003 | Drug | N | 1 | Dizziness | SEVERE |
The log shows the WHERE working at read time:
NOTE: There were 3 observations read from the data set WORK.ADSL.
NOTE: There were 2 observations read from the data set WORK.AE.
WHERE aesev='SEVERE';
NOTE: The data set WORK.SEV_A has 3 observations and 6 variables.
Program B returns only the two severe event rows (1001 Nausea and 1003 Dizziness). Subject 1002 drops out because the IF tests the merged record, and 1002's merged record has a blank AESEV.
Neither is wrong. A is the shape you want for a subject-level listing ("every subject, plus their severe event if any"). B is an event-level listing. The bug is picking one while expecting the other.
Combining both
The usual production pattern uses each for what it does best: WHERE= to restrict the input population, IF for anything that depends on the merge.
data sev_saf;
merge adsl(in=insl where=(saffl = 'Y')) ae;
by usubjid;
if insl and aesev = 'SEVERE';
run;
Result: one row, subject 1001 Nausea. Subject 1003 is removed at read time because SAFFL = 'N'.
Put the WHERE on the dataset it belongs to. A WHERE statement in a merge applies to every dataset in the MERGE statement, so a variable that exists in only one of them causes an error. The WHERE= option avoids that.
Two WHERE statements and WHERE ALSO
Two subsetting IF statements in one step both apply, like an AND. Two WHERE statements do not. The second one replaces the first:
data check;
set ae;
where aesev = 'SEVERE';
where usubjid = '1001';
run;
NOTE: WHERE clause has been replaced.
You get both of subject 1001's events, including the mild headache. To add a condition instead of replacing it, use WHERE ALSO:
data check;
set ae;
where aesev = 'SEVERE';
where also usubjid = '1001';
run;
NOTE: WHERE clause has been augmented.
Now only the Nausea record is kept. In practice, one WHERE with and is easier to read. WHERE ALSO is useful when a condition is added later, for example from a macro parameter.
Does WHERE run faster?
Usually, yes. Records that fail a WHERE are not moved into the program data vector, so on a large LB or QS dataset less work is done. The log shows it: with WHERE, the "observations read" count is the number that matched. With IF, it is the full dataset. On small data you will not notice. Choose by correctness first.
Common mistakes
- Testing a derived variable in WHERE. If the variable is created in the step, only IF can see it.
- Filtering the child dataset before a merge and expecting fewer rows. The parent's unmatched records still come through, as program A shows.
- Two WHERE statements. The second silently replaces the first, apart from a NOTE. Search logs for "WHERE clause has been replaced".
- A WHERE statement in a MERGE on a variable from only one dataset. Use the
WHERE=option on that dataset. - In R, forgetting that
filter()drops NA.filter(AESEV == "SEVERE")silently removes rows whereAESEVis NA, which is the same row loss as program B.
Try it yourself: how many rows?
Using the test data above, how many observations does this step create, and which subjects are they?
data q;
merge adsl(where=(saffl = 'Y')) ae(in=inae where=(aesev ne 'MILD'));
by usubjid;
if inae;
run;
Answer: Three rows, not the two many people expect. ADSL is read as 1001 and 1002 only. AE is read without the mild headache, so it supplies 1001 Nausea, 1002 Rash and 1003 Dizziness. if inae keeps every row that has an AE record, and nothing checks for an ADSL record. So 1003 Dizziness is kept with missing TRT01A and SAFFL. That row is a finding. Add in=insl to the ADSL options and use if insl and inae; to get the two safety-population rows.
Quick check
1. Which statement can test a variable created earlier in the same DATA step?
WHERE is applied while reading the input data, before the step creates anything.
2. A step has where aesev = 'SEVERE'; followed by where usubjid = '1001';. What is kept?
The second WHERE replaces the first. Use WHERE ALSO or a single WHERE with AND.
3. Which can you use inside PROC FREQ to restrict the records analysed?
IF is a DATA step statement. Procedures accept WHERE, as a statement or a dataset option.
Frequently asked questions
What is the difference between WHERE and IF in SAS?
WHERE filters records as they are read, so it is faster and works in procedures, but it only sees variables in the input data. IF filters inside the DATA step, so it can test anything the step has created or read.
Can I use WHERE with FIRST. or _N_?
No. Those are automatic variables that exist only during the DATA step. Use a subsetting IF.
Why does my merge give different results with WHERE and IF?
WHERE on one input dataset filters before the records are combined, so unmatched records from the other dataset still appear. IF filters the combined record.
What does WHERE ALSO do?
It adds a condition to the existing WHERE clause with AND, instead of replacing it. The log says "WHERE clause has been augmented".
- WHERE runs while reading. IF runs inside the step, after reading and merging.
- Only IF can test new variables,
FIRST./LAST.,_N_andIN=flags. - In a merge,
WHERE=on one dataset and IF after the merge can return different rows. Decide which shape you need. - A second WHERE replaces the first. Use
WHERE ALSOor one combined condition.
Related: FIRST. and LAST. in SAS and R · Building test data in SAS and R · Your first weeks as a clinical SAS programmer