topic Re: move multiple 'or' statements into one statement in SAS Programming

move multiple 'or' statements into one statement

GeorgeSAS — Thu, 16 Jan 2020 16:45:32 GMT

Hello Everyone,

Can I write a code make multiple 'OR' statements into one row? please see the code, the new method does not work.


proc sql;
create table old_method as
select * from sashelp.baseball
where 
upcase(name) like '%BI%'

or 
upcase(name) like '%AI%'



or
upcase(name) like '%AE%'
/* we have more or statements*/
;
quit;



*--the new method does not work;
proc sql;
create table new_method as
select * from sashelp.baseball
where 
upcase(name) like in('%BI%','%AI%','%AE%');
quit;

Thanks!

Re: move multiple 'or' statements into one statement

Krueger — Thu, 16 Jan 2020 17:06:40 GMT

EDIT: Looks like this doesn't work the same in SAS so you would have to update the logic below to work in SQL and then use SQL Pass through. I'm sure there are much better alternatives built-in to SAS however.

There is no combined like and in statement for SQL (may be wrong regarding SAS). However what I would do if using SQL would be something like this.

proc sql;
create table list as
select '%AB%' as var
union all
select '%AI%' as var
union all
select '%AE%' as var;
quit;

proc sql;
create table old_method as
select *
  from sashelp.baseball b
  join list l on upcase(b.name) like l.var;
quit;

Re: move multiple 'or' statements into one statement

FreelanceReinh — Thu, 16 Jan 2020 17:01:05 GMT

Hello @GeorgeSAS,

Yes, you can, but you need to use correct syntax. The LIKE and IN operators cannot be used together like this. You may want to use the PRXMATCH function instead:

where prxmatch('/BI|AI|AE/i', name);

The pipe character means "or", the "i" modifier requests case-insensitive search.

Re: move multiple 'or' statements into one statement

Reeza — Thu, 16 Jan 2020 17:34:12 GMT

@Krueger, you would need to remove the % and then I think it will work as expected. Or you could use the find()/INDEX() functions as well.

Re: move multiple 'or' statements into one statement

Krueger — Thu, 16 Jan 2020 18:48:38 GMT

I get a syntax error running as is(for the union all). If I add from table then union all works but at that point it's a completely different approach with just inserting the values into a table and then using the second part.

Yeah I don't know SAS is weird to me...

data list;
input var $;
datalines;
'AB'
'AE'
'AI'
'BE%'
;
run;

proc sql;
create table old_method as
select upcase(b.name), l.var
  from sashelp.baseball b
  join list l on upcase(b.name) like l.var;
quit;

If I swap l.var with 'BE%' it works just fine. Not sure what it's struggling with when I can do the exact same thing in SQL.