SAS: How to Use the IN Operator in PROC SQL


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

2 Replies to “SAS: How to Use the IN Operator in PROC SQL”

  1. “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.” I believe that it should read:
    “…where a variable in a dataset EQUALS a value in a list.” Note the difference between contains and equals.

    1. Hi Grace…You are correct. In the context of SQL and PROC SQL in SAS, the IN operator checks for equality with any value in a specified list, not for containment. The correct phrasing should be:

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

      Here’s a more detailed explanation and example for clarity:

      ### Explanation

      – **IN Operator**: Used to specify multiple possible values for a column. It returns rows where the column’s value matches any value in the list provided.

      ### Example in PROC SQL

      Suppose you have a dataset named `employees` with a variable `department`.

      “`sas
      proc sql;
      select *
      from employees
      where department in (‘HR’, ‘Finance’, ‘IT’);
      quit;
      “`

      In this example, the query returns all rows from the `employees` dataset where the `department` variable is equal to either ‘HR’, ‘Finance’, or ‘IT’.

      ### Key Point

      – The **IN operator** checks for equality, meaning it returns rows where the value of the specified variable exactly matches any value in the provided list.
      – This is different from a **CONTAINS operator** (or the LIKE operator with wildcards), which would be used to check if a variable’s value includes a specified substring.

      ### Corrected Sentence

      Here is your corrected sentence:

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

      This accurately reflects the functionality of the IN operator in PROC SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *