'Like' and 'Not Like' in a single statement

[Barkat]

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;

[mkeintz]

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".

[Barkat]

Here I pasted the original statement. It is not working though

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;

[mkeintz]

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.

[PGStats]

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%'

[Ksharp]

Use sub-query .

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;