You can use the CONTAINS operator in the PROC SQL statement in SAS to only return rows where a variable in a dataset contains some string pattern.
The following examples show how to use the CONTAINS operator in practice with 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;
Example 1: Select Rows where Variable Contains One Pattern
We can use the CONTAINS 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 contains 'avs';
quit;
Notice that only the rows where the team contains ‘avs’ somewhere in the name are returned.
Example 2: Select Rows where Variable Contains One of Several Patterns
We can use the CONTAINS operator in PROC SQL to select only the rows where the team contains the pattern ‘avs’ or the pattern ‘ics’ somewhere in the name:
/*select all rows where team contains 'avs' or 'ics'*/
proc sql;
select *
from my_data
where team contains 'avs' or team contains 'ics';
quit;
Only the rows where the team contains ‘avs’ or ‘ics’ somewhere in the name are returned.
Example 3: Select Rows where Variable Does Not Contain Pattern
The opposite of the CONTAINS operator in PROC SQL is NOT CONTAINS, 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 CONTAINS 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 contains '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