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 [8-14]


LIKE Operator
WHERE name LIKE 'B_n';

The LIKE operator selects rows that match a specific pattern.

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

The BNAME table contains a list of names and their meaning.
Picture

Specifying Patterns with Wildcard Characters

One way to specify a pattern is to use the special (wildcard) character, underscore (_).​

​Example
Proc sql;
select *
from bname
where name like 'B_n';
quit;
The pattern is specified in the form of 'B_n'.
Picture

The underscore (_) in 'B_n' represents any single character such as:​
  • a​
  • b
  • $
  • *​
The pattern 'B_n' represents any 3-character string that starts with the capital 'B' and ends with the letter 'n' such as:
  • ​Ben
  • Ban
  • Bzn
  • ​B$n
​
In the BNAME table, only the name 'Ben' follows this pattern:
Picture

It is selected and displayed in the output:
Picture

The percentage (%) is another wildcard character that you can use to specify the pattern.

​Example
Proc sql;
select *
from bname
where name like 'B%n';
quit;
The percentage (%) in 'B%n' represents any number of characters.

It could be a single character such as: 
  • a
  • b
  • $
  • *​
or combination of characters such as:
  • abcd
  • abc123
  • def%%%
or even no character:
  • (blank)

The pattern in the form of 'B%n' represents any string that starts with the capital 'B' and ends with the letter 'n'.

Below are the strings that follow the 'B
%n' pattern:
  • Bn (no character)
  • Ban (1-character)
  • Bean (2-character)
  • Brian (3-character)
  • B##$$%%n (6-character)
  • Betty Chen (many)

All of the names that follow this pattern are displayed:
Picture

Let's take a look at some more examples.

Example 1
Proc sql;
select *
from bname
where name like 'Br%';
quit;

The 'Br%' represents any string starts with 'Br'.

Only the names that start with 'Br' are selected:
Picture

Example 2
Proc sql;
select *
from bname
where name like '%min';
quit;
The '%min' represents any string that ends with 'min'. 

Again, only the names that end with 'min' are selected:
Picture

​Example 3:
Proc sql;
select *
from bname
where name like 'B___n';
quit;

Note: there are three underscores (_) between the letter B and letter n in 'B___n'.
The 'B___n' represents any 5-letter name that starts with the letter B and ends with the letter n. Only the names Baron and Blain match the pattern:
Picture

​Example 4:
Proc sql;
select *
from bname
where name like '%nn%';
quit;
The '%nn%' represents any string that contains the characters 'nn'.

Benn, Bennjamin, Brandenn, Brandonn and Branndon are the five names that match this pattern:
Picture

Exercise

Write a Proc SQL step on the BNAME table to display the names that contain the string 'jam' (e.g. Benjamin, Benjamen).
Next

Need some help? 


HINT:

The percentage (%) is the correct wildcard character to use for this exercise.


SOLUTION:

Proc sql;
select *
from bname
where name like '%jam%';
run;


Fill out my online form.
Already a member? Go to member's area.