proc sql filter for values that end in a list of strings-CodePudding

I have an example table:

data data;
    length code $30;
    input code$;
    datalines;
PPPES
PPPEW
pppESS
saf
xwq3
er32
ddES
ea9ESS
asesEo
ewlEa
;
run;

and I want to filter for rows that end in ES, ESS, or EW. I tried the following but it didn't work:

proc sql;
create table revised as
select * 
from data
where code like ("%ES", "%ESS", "%EW") 
quit;

Is there a way to filter if a variable ends in a possible list of string values?

This is my desired output:

data data1;
    length code $30;
    input code$;
    datalines;
PPPES
PPPEW
pppESS
ddES
ea9ESS
;
run;

CodePudding user response：

No.

Either explicitly test for each string.

where code like '%ES' or code like '%ESS' or code like '%EW'

In a data step you could use either of these:

if left(reverse(code)) in: ('SE','SSE','WE');
where left(reverse(code)) in: ('SE','SSE','WE');

PROC SQL does not support the truncated comparisons specified by the : modifier. But you could use the WHERE= dataset option

from data(where=(left(reverse(code)) in: ('SE','SSE','WE')))

CodePudding user response：

Using "or" and simple quotation marks:

    data data;
        length code $30;
        input code$;
        datalines;
    PPPES
    PPPEW
    pppESS
    saf
    xwq3
    er32
    ddES
    ea9ESS
    asesEo
    ewlEa
    ;
    run;
    
    proc sql;
        create table revised as
            select * 
            from data
            where code like ('%ES') or code like ('%ESS') or code like ('%EW');
    quit;