how to filter record second position of 'a' letter from last onwards by using like operator

[thanikondharish]

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?

[Kurt_Bremser]

I do not think LIKE can do that.

where substr(name,length(name)-1,1) = 'a';

You can also use the PRX* functions.

[thanikondharish]

Thank you for your answer but I have to use like operator only

[Kurt_Bremser]

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]

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]

---

@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]

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]

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

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