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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.