SAS: How to Use LIKE Operator in PROC SQL

---

You can use the LIKE operator in the PROC SQL statement in SAS to return rows where a variable in a dataset matches some string pattern.

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

Example: Using LIKE 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;
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 LIKE operator in PROC SQL to select only the rows where the team contains the pattern ‘avs’ somewhere in the name:

/*select all rows where team contains 'avs'*/ 
proc sql;
   select *
   from my_data
   where team like '%avs%';
quit;

Notice that only the rows where the team contains ‘avs’ somewhere in the name are returned.

The opposite of the LIKE operator in PROC SQL is NOT LIKE, which selects rows where some variable in a dataset does not contain a certain string pattern.

The following code shows how to use the NOT LIKE operator to select all rows where the team does not contain ‘avs’ in the name:

/*select all rows where team does not contain 'avs'*/
proc sql;
   select *
   from my_data
   where team not like '%avs%';
quit;

Notice that only the rows where the team does not contain ‘avs’ somewhere in the name 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 the IN Operator in PROC SQL
SAS: How to Use the WHERE Operator in PROC SQL