BookmarkSubscribeRSS Feed
thanikondharish
Calcite | Level 5

data ex1;
set sashelp.class ;
where name like '%a_' ;
run;

Output:

Barbara
Thomas
William

 

if I use the above 3 records coming but 'Barbara' value should come from last to second position 'r' letter is there . remaining 2 values correct 'Thomas''William'
how to pick by using like operator only?

7 REPLIES 7
thanikondharish
Calcite | Level 5
Thank you for your answer but I have to use like operator only
Kurt_Bremser
Super User

How about

like '%a_ '

Mind that this will match any "word" where the next-to-last character is an "a", so the string

"Xxxax yyyyy"

will also match. And in the case of

length x $5;
x = "xxxax";

it won't match, as there is no space left in the variable for blanks.

 

The regular expression functions are MUCH better at detecting end of words under all circumstances.

 

If this is an exercise, it is a BAD exercise, and you need to tell your instructor and classmates.

Reeza
Super User
It doesn't look like your requirement isn't consistent. For the first record you want 'ra' and for the next two, Thomas/William you want s/m or nothing?

What is the general rule?

like %a_ looks for a_ but you have no underscore so that should never match.
ballardw
Super User

@thanikondharish wrote:

data ex1;
set sashelp.class ;
where name like '%a_' ;
run;

Output:

Barbara
Thomas
William

 

if I use the above 3 records coming but 'Barbara' value should come from last to second position 'r' letter is there . remaining 2 values correct 'Thomas''William'
how to pick by using like operator only?


Like matches

Patterns consist of three classes of characters:
underscore (_)
matches any single character.
percent sign (%)
matches any sequence of zero or more characters.
any other character
matches that character
 

So saying that you "have to use like" means that you do not actually care about 2nd to last position because Like doesn't use any  specific position.

PRX functions,

or Substr with the Length function to start searching at the second to last position, but not Like.

 

A_Kh
Lapis Lazuli | Level 10

Try this:

data ex1;
set sashelp.class ;
where strip(name) like '%a_' ;
proc print;run;

I'm assuming "Barabara" has a trailing blank, and the wildcard (_) picking this blank as a single character, so the last letter 'a' is being considered as the second letter backwards. 

Reeza
Super User
data ex1;
set sashelp.class ;
where name like '%a_ ' ;
run;

If you want it at the end, add a space at the end?

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 188 views
  • 0 likes
  • 5 in conversation