- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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".
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table bll_18_15ct as
select *
from bll_18
where
(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%'
order by city;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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%'
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create want as
select * from
(
select * from have where city not like '%LAKE%' and city not like '%LAN%'
)
where city like '%L.A%' or city like '%LOS%'
order by city;
quit;