Sentry Page Protection
Proc SQL [7-14]

IN Operator
WHERE name IN 
 ("Art" "Benita");

The IN operator is used in combination with a list of values in the WHERE clause.

It selects rows that match any of the listed values.

Let's take a look at an example.

Copy and run the SCHOOL table from the yellow box below if you haven't done so.

​Example
Proc sql;
select *
from school
where name in
  ('Art' 'Benita');
quit;

The WHERE clause selects the students whose names are either 'Art' or 'Benita':

Only these two students are selected:

The list of values does not have to contain character values. You can specify a list of numeric values as well.​

Example
Proc sql;
select *
from school
where age in (13, 16);
quit;

Unlike character values, numeric values must be separated by a comma

Only students who are either 13 or 16 years old are selected:

CONTAINS Operator

The CONTAINS operator selects rows where a character column contains a particular string.

Let's take a look at an example.

Copy and run the BESTPRODUCT table from the yellow box below:

The BESTPRODUCT table contains the awards given to a list of electronic products by a computer review website in 2013.

There are three columns:​
  • CATEGORY
  • AWARD
  • PRODUCT

Now, let's take a look at an example where you can select only Apple products from the PRODUCT column:
Proc sql;
select *
from bestproduct
where product
  contains 'Apple';
quit;
The CONTAINS operator selects only products that contain the word 'Apple'

Only Apple products are selected:
Exercise

Locate the ELECTRIC table from the SASHelp library.

The ELECTRIC table contains the revenue for four types of customers from 1994 to 2005.

Write a Proc SQL step that displays the revenue for Residential and Commercial customers in 1997, 1998 and 1999.
Need some help? 

Fill out my online form.