Table of Contents
The IN operator in PROC SQL with SAS is a logical operator used to test whether a value matches any value in a list of values. It returns a Boolean result that is either TRUE or FALSE. It is used to filter the results of a query to return only those records that match the values in the list specified in the IN operator. It is typically used in the WHERE clause of a query.
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.
The following tutorials explain how to perform other common tasks in SAS: