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:

data bname; infile datalines dlm='09'x missover dsd; input name : $12. meaning : $60.; datalines; Barden Barley valley Baron Nobleman, baron Barron A form of baron Barton Barley farm or bart's town. the epon.. Bayron A form of baron Beaman Beekeeper Ben Right-hand son Benjamen Son of my right Benjamin Son of my right hand Benjammin Son of my right hand Benn Son of the south, son of the right hand Bennjamin Son of my right hand Benson Ben's son, excellent son Blain A form of blaine Boston A form of bosley Bradden Resembling salmon / from the wide valley Bradyn A form of braden Braedan A form of braden Braeden A form of braden Braedon A form of bradon Braedyn A form of braden Braiden A form of braden Braidon A form of bradon Brandan A form of brandon Branddon Beacon hill Branden Beacon valley Brandenn From the broom or gorse hill Brandin A form of branden Brandon Beacon hill Brandonn Beacon hill Brandyn A form of brandon Branndon Beacon hill Bransen A form of branson ; run;

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

Proc sql;
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
• $
• *​

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:

It is selected and displayed in the output:

---

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:

---

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:

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:

​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:

​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:

---

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? 

---

Fill out my online form.