Sentry Page Protection
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.
Specifying Patterns with Wildcard Characters
One way to specify a pattern is to use the special (wildcard) character, underscore (_).
Example
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;
select *
from bname
where name like 'B_n';
quit;
The pattern is specified in the form of 'B_n'.
The underscore (_) in 'B_n' represents any single character such as:
- a
- b
- $
- *
- Ben
- Ban
- Bzn
- B$n
In the BNAME table, only the name 'Ben' follows this pattern:
It is selected and displayed in the output:
The percentage (%) is another wildcard character that you can use to specify the pattern.
Example
Example
Proc sql;
select *
from bname
where name like 'B%n';
quit;
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:
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:
All of the names that follow this pattern are displayed:
It could be a single character such as:
- a
- b
- $
- *
- abcd
- abc123
- def%%%
- (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:
Let's take a look at some more examples.
Example 1
Example 1
Proc sql;
select *
from bname
where name like 'Br%';
quit;
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:
Example 2
Proc sql;
select *
from bname
where name like '%min';
quit;
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:
Again, only the names that end with 'min' are selected:
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'.
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:
Example 4:
Proc sql;
select *
from bname
where name like '%nn%';
quit;
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:
Benn, Bennjamin, Brandenn, Brandonn and Branndon are the five names that match this pattern:
Exercise
Write a Proc SQL step on the BNAME table to display the names that contain the string 'jam' (e.g. Benjamin, Benjamen).
Write a Proc SQL step on the BNAME table to display the names that contain the string 'jam' (e.g. Benjamin, Benjamen).
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.