I would like to include all the observations with %LA%, but I would like to avoid selecting %LAKE% and %LAND%. How can I do that?
I tried the following but it does not work
proc sql;
create want as
select * from have
where
city like '%L.A%' or city like '%LOS%'
and city not like '%LAKE%' and city not like '%LAN%'
order by city;
quit;
Isn't this a matter of properly nesting the logical conditions? Something like (put intended):
where
(city like '%L.A%' and city not like '%LAKE%' and city not like '%LAN')or
city like '%LOS%'
BTW, you apparently typed (rather the cut and pasted) your code, since you had "create want" instead of "create table want".
You apparently didn't consistently apply my comment about nesting. You are using an extra or city like '%LA%' (in bold italics below)
(city like '%L.A%' and city not like '%LAKE%' and city not like '%LAN%')or
city like '%COMPTO%' or city like '%LA%' or city like '%LOS%'
or city like '%BEL%' or city like '%COM%' or city like '%LYN%' or city like '%GAT%'
or city like '%WALN%' or city like '%CUD%' or city like '%MAY%' or city like '%VERN%' or city like '%VARN%'
or city like '%HUNT%' or city like '%WILL%' or city like '%DOM%' or city like '%FLO%'
The condition totally defeats all the "not like" conditions.
This is a general programming issue - it isn't a programming problem unique to SAS.
And BTW, "not working" is next to a useless description. Let us know what you expected vs.what you actually produced. Help us help you.
Just exchange %L A% and %LA%
(city like '%LA%' and city not like '%LAKE%' and city not like '%LAN%')
or city like '%COMPTO%' or city like '%L A%' or city like '%LOS%'
or city like '%BEL%' or city like '%COM%' or city like '%LYN%' or city like '%GAT%'
or city like '%WALN%' or city like '%CUD%' or city like '%MAY%' or city like '%VERN%' or city like '%VARN%'
or city like '%HUNT%' or city like '%WILL%' or city like '%DOM%' or city like '%FLO%'
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.