Using LIKE in proc SQL

[anoopm7]

Hello,

I am working on a request to find out more details of customers by searching a bigger dataset (let's call it address) which has multiple variables under it.

The search is using address data against the big dataset and in big dataset the address fields are split into like addr1 and addr2.

The below is the query I use and it's not able to successfully retrieve the matches for these address data though it's present in the big dataset. Do you have any thoughts on how I can fine tune the query to get the data I needed.

options obs=max;

libname dd "/customer/address/2015/05" ;

proc sql ;

select ACCOUNT,(cat(addr1,addr2)) as address,NAME1,NAME2,DOB,PHONE,PHONE2

from dd.addr ;

where address like ' %PO Box 935%Lola Park% ' or

address like '%45 SE 590TH ST%'  or

address like '%41 US Highway 28th North%Suite 14359%' or

address like '%PO Box 1700%' or

run ;

[Steelers_In_DC · Accepted solution]

Because you are generating the variable address in this same step your where clause will not work, you should use having instead.  I made some other minor changes that I think will help as well:

DATA have;

infile cards dsd;

length addr1 addr2 $10.;

input addr1$ addr2$ name1$ name2$ dob phone phone2;

cards;

po box 935,lola park,bob,smith,20051201,412,5551212

;

run;

proc sql;

create table want as

select catx(' ',addr1,addr2) as address,name1,name2,dob,phone,phone2

from have

having upcase(address) like '%PO BOX 935 LOLA PARK%';

[anoopm7]

Mark, thanks...That worked. The HAVING made the difference.