topic Can SAS filter multiple possible LIKE conditions, without an OR? in SAS Procedures

Can SAS filter multiple possible LIKE conditions, without an OR?

paulkaefer — Mon, 11 Mar 2019 20:36:13 GMT

I have a query like so:

proc sql;
    create table test_query as
    select *
    from lib.dataset
    where something = 1
        and (description contains ("ABC")
          or description contains ("DEF")
          or description contains ("GHI")
          or description contains ("JKL")
          or description contains ("MNO")
          or description contains ("PQR"))
    ;
quit;

Is there a way to do something like "description LIKE IN ('%ABC%', '%DEF%', '%GHI%', ...)' that would avoid costly ORs within the query? Or something more efficient that I'm not thinking of?

Re: Can SAS filter multiple possible LIKE conditions, without an OR?

Tom — Mon, 11 Mar 2019 20:54:31 GMT

How about PRXMATCH() ?

proc print data=sashelp.class;
where prxmatch('/B|M/i',name);
run;

Obs    Name       Sex    Age    Height    Weight

  3    Barbara     F      13     65.3        98
  6    James       M      12     57.3        83
 14    Mary        F      15     66.5       112
 16    Robert      M      12     64.8       128
 18    Thomas      M      11     57.5        85
 19    William     M      15     66.5       112

Re: Can SAS filter multiple possible LIKE conditions, without an OR?

paulkaefer — Tue, 12 Mar 2019 16:38:46 GMT

Thanks, @Tom! I can't believe I didn't think of regexes.

Interestingly, the performance didn't seem much better, a sign that maybe the original filters aren't so bad.

But this definitely cleans-up the code quite a bit & gives more flexibility for automated query construction.