Sentry Page Protection
Proc SQL [7-14]
IN Operator
WHERE name IN
("Art" "Benita");
("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;
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;
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;
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.
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?
HINT:
Don't forget to use the comma to separate the numeric value when using the IN operator.
SOLUTION:
proc sql;
select *
from sashelp.electric
where customer in ("Residential" "Commercial")
and year in (1997, 1998, 1999);
quit;
Fill out my online form.