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 ;
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%';
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%';
Mark, thanks...That worked. The HAVING made the difference.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.