topic Re: 'Like' and 'Not Like' in a single statement in New SAS User

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

Barkat — Mon, 17 Aug 2020 23:38:24 GMT

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;

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

mkeintz — Tue, 18 Aug 2020 00:02:55 GMT

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

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

Barkat — Tue, 18 Aug 2020 00:36:10 GMT

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;

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

mkeintz — Tue, 18 Aug 2020 01:22:41 GMT

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.

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

PGStats — Tue, 18 Aug 2020 02:32:36 GMT

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

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

Ksharp — Tue, 18 Aug 2020 11:48:32 GMT

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;