Home » SAS: How to Use the IN Operator in PROC SQL

SAS: How to Use the IN Operator in PROC SQL

by Erma Khan

You can use the IN operator in the PROC SQL statement in SAS to only return rows where a variable in a dataset contains a value in a list.

The following example shows how to use the IN operator in practice.

Example: Using IN Operator in PROC SQL in SAS

Suppose we have the following dataset in SAS that contains information about various basketball players:

/*create dataset*/
data my_data;
    input team $ points;
    datalines;
A 12
A 14
A 15
A 18
B 31
B 32
C 35
C 36
C 40
D 28
E 20
E 21
;
run;

/*view dataset*/
proc print data=my_data;

We can use the IN operator in PROC SQL to select only the rows where the team is equal to A, B, or E:

/*select all rows where team is A, B, or E*/
proc sql;
   select *
   from my_data
   where team in ('A', 'B', 'E');
quit;

Notice that only the rows where the team is equal to A, B, or E are returned.

The opposite of the IN operator in PROC SQL is NOT IN, which selects rows where some variable in a dataset does not contain a value in a list.

The following code shows how to use the NOT IN operator to select all rows where the team is not equal to A, B, or E:

/*select all rows where team is not A, B, or E*/
proc sql;
   select *
   from my_data
   where team not in ('A', 'B', 'E');
quit;

Notice that only the rows where the team is not equal to A, B, or E are returned.

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

SAS: How to Use UNION in PROC SQL
SAS: How to Use EXCEPT in PROC SQL
SAS: How to Use Proc Univariate by Group

Related Posts