Search the site...

SASCRUNCH TRAINING
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
Sentry Page Protection
Please Wait...
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.
Picture

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':
Picture

Only these two students are selected:
Picture


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
Picture

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


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
Picture

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'
Picture

Only Apple products are selected:
Picture

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.
Next

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.
Already a member? Go to member's area.