Help using Base SAS procedures

Using LIKE in proc SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Using LIKE in proc SQL

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 ;


Accepted Solutions
Solution
‎06-29-2015 11:50 AM
Valued Guide
Posts: 860

Re: Using LIKE in proc SQL

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

View solution in original post


All Replies
Solution
‎06-29-2015 11:50 AM
Valued Guide
Posts: 860

Re: Using LIKE in proc SQL

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

Occasional Contributor
Posts: 16

Re: Using LIKE in proc SQL

Posted in reply to Steelers_In_DC

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 243 views
  • 2 likes
  • 2 in conversation