SAS: How to Use a “NOT IN” Operator

You can use the NOT IN operator in SAS to return only the rows where a variable does not have a value in some list of values.

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

Example: Using NOT IN Operator 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;
Cavs 12
Cavs 14
Warriors 15
Hawks 18
Mavs 31
Mavs 32
Mavs 35
Celtics 36
Celtics 40
;
run;

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

We can use the NOT IN operator in PROC SQL to select only the rows where the team is not equal to ‘Cavs’ or ‘Celtics’:

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

Notice that the only rows returned are the ones where the team is not equal to ‘Cavs’ or ‘Celtics.’

You can also use the NOT IN operator within a SET statement to create a new dataset that only contains rows where the team is not equal to ‘Cavs’ or ‘Celtics’:

/*create new dataset that only contains rows where team is not Cavs or Celtics*/
data new_data;
    set my_data;
    where team not in ('Cavs', 'Celtics');
run;

/*view new dataset*/
proc print data=new_data; 

The new dataset called new_data only contains the rows from the original dataset where the team is not equal to ‘Cavs’ or ‘Celtics.’

Additional Resources

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

SAS: How to Use the IN Operator in PROC SQL
SAS: How to Use LIKE Operator in PROC SQL
SAS: How to Use the WHERE Operator in PROC SQL