Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 37 ยท Data Manipulation

Subsetting and Selecting Data: WHERE, IF and DELETE

Choose rows and columns in SAS: the WHERE statement and operators (BETWEEN, IN, CONTAINS, LIKE, IS MISSING), subsetting IF, DELETE, OBS and FIRSTOBS, and when WHERE and IF differ.

Most analyses start by narrowing the data: only 2024 orders, only adults, only customers in three countries. SAS has two main ways to filter rows, WHERE and the subsetting IF. They look similar and behave differently in important ways. This lesson explains both, along with row-limiting options and the tools to choose columns.

The WHERE statement

WHERE filters observations before they are read into the program data vector, so it is efficient and it works in DATA steps and in PROC steps:

SAS
data teens;
   set sashelp.class;
   where age between 13 and 15;
run;

proc print data=sashelp.class;
   where sex = "F" and weight > 100;
run;

The WHERE-only operators are handy:

Operator Meaning
between a and b Inclusive range
in ("x", "y") One of a list
contains "abc" or ? Text contains a substring
like "A%" Pattern: % is any text, _ one character
is missing / is null Value is missing
=* Sounds like
same and Add another condition to the previous WHERE
SAS
where name like "A%";          /* names starting with A */
where age is not missing;
where region in ("East", "West") and sales > 1000;

Character comparisons are case-sensitive: where sex = "f" finds nothing in sashelp.class. The same goes for contains and like. Use upcase() on the variable when you are not sure.

The subsetting IF

An IF statement without THEN keeps an observation only if the condition is true:

SAS
data adults;
   set sashelp.class;
   if age >= 15;
run;

Its twin is IF ... THEN DELETE;, which says the opposite: if age < 15 then delete;. They are equivalent. Which to use is a matter of taste: describe what you keep (subsetting if) or what you remove (delete). Pick one convention per team.

WHERE versus subsetting IF

WHERE subsetting IF
When it filters Before the observation enters the PDV After it has been read and statements above have run
Works in PROC steps Yes No
Can use new variables created in the step No Yes
Special operators (between, like, contains) Yes No (use functions)
Speed on big data Faster, may use indexes Reads every row
Used with merge Applies to each input before merging Applies after the merge

The important row is the third one. This does not work:

SAS
data wrong;
   set sashelp.class;
   bmi = weight * 703 / height**2;
   where bmi > 25;       /* ERROR: bmi is not on the input data set */
run;

SAS reports a message such as ERROR: Variable bmi is not on file SASHELP.CLASS. because where looks at the input data set. Use if bmi > 25; instead, or filter in a second step. Conversely, if the condition uses only original variables, prefer where, because rows are rejected before the step does any work.

A step may contain both: where first (cheap), then if for the calculated condition.

With a MERGE, where= and if can give different results, because if runs after the rows have been combined. This is the subject of Combining Data Sets.

Limiting by position: OBS and FIRSTOBS

Data set options pick rows by position, useful for samples and testing:

SAS
proc print data=sashelp.class (obs=5); run;               /* first 5 rows */
proc print data=sashelp.class (firstobs=10 obs=15); run;  /* rows 10 to 15 */

obs= is the last observation number to read, not the count; with firstobs=10 obs=15 you get six rows. A global options obs=100; limits every step until you restore it with options obs=max;, convenient for testing but dangerous if you forget it.

Choosing columns

Row filters go with column choices:

SAS
data small (keep=name age);
   set sashelp.class (drop=sex);
   where age > 12;
run;

Here drop=sex is applied as the data is read, so sex is not available in the step; keep=name age is applied when the data is written. As the previous lessons showed, use the input option for variables you do not need at all, and the output option when you need them for calculations but not in the result.

Removing duplicates

Duplicate rows are a different kind of subsetting. PROC SORT with nodupkey keeps the first row of each by group:

SAS
proc sort data=orders out=unique_customers nodupkey;
   by customer_id;
run;

Sorting is covered in the next lesson, along with FIRST. and LAST. for finer control.

A filter model

The Python stand-in (not SAS) reproduces where with between, in and like, and a subsetting if on a computed value.

Python Python stand-in: WHERE and IF filters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement filters on a variable computed earlier in the same DATA step?

    where sees only the input data set; if tests the program data vector, which contains new variables.
  2. 2. Which operator works in WHERE but not in IF?

    between, like, contains and is missing are WHERE operators.
  3. 3. What rows does (firstobs=10 obs=15) read?

    obs= is the last row number, so rows 10 to 15 (six rows).
  4. 4. Which is the safest way to compare a character value case-insensitively?

    Normalize the case on one side so both agree.

Summary

  • where filters before the PDV, works in PROC steps and has extra operators; it cannot use variables created in the step.
  • A subsetting if (or if ... then delete) filters after statements run and can use new variables.
  • obs= and firstobs= pick rows by position; keep/drop options pick columns.
  • Character comparisons are case-sensitive; test for missing values deliberately.

Next: Sorting and BY-Group Processing