Accepted Solutions
Hi @SunnySong , would prxmatch make the answer easier? The code and results are as follows.
However, the loc1=; statement does not work because it seems that the &strlist macro was not resolved. The loc=; statement worked because I copied the value of the &strlist into the statement manually. My furthur question is: how to make the loc1=prxmatch('/(&strlist)/',address); work, i.e., resolve the &strlist macro properly?
data street_name;
input st_nm $ typ 4.;
datalines;
ROAD 1
RD 1
STREET 1
ST 1
PLACE 1
PL 1
PO_BOX 3
UNIT 2
SUITE 2
;
run;
data address;
input address $20.;
datalines;
3 ARRON ROAD
15 BONNY RD
7 DAISY STREET
PO BOX 15
UNIT 6
89 ROSE PLACE
9 HOPE ST
;
run;
proc sql noprint;
select st_nm
into :strlist separated by '|'
from street_name
where typ=1;
quit;
%put &strlist;
/*ROAD|RD|STREET|ST|PLACE|PL*/
data str_address;
set address;
loc=prxmatch('/(ROAD|RD|STREET|ST|PLACE|PL)/',address);
if loc^=0 then found=1;
loc1=prxmatch('/(&strlist)/',address);
run;
proc print data=str_address;run;
Hi @Tom thanks for correct the syntax! Change the single quotation mark in prxmatch('//'); was the only thing that I did not try (I even tried something like prxmatch('/("&list")/') because I was not familar with perl (prxmatch) syntax and mistakenly believe that the single quotation mark was a fixed syntax in prxmatch() statement.
So now the most efficient and succinct answer (no array, no macro needed) for the question is as follows, and hi @SunnySong , what do you think😀?
data street_name;
input st_nm $ typ 4.;
datalines;
ROAD 1
RD 1
STREET 1
ST 1
PLACE 1
PL 1
PO_BOX 3
UNIT 2
SUITE 2
;
run;
data address;
input address $20.;
datalines;
3 ARRON ROAD
15 BONNY RD
7 DAISY STREET
PO BOX 15
UNIT 6
89 ROSE PLACE
9 HOPE ST
;
run;
proc sql noprint;
select st_nm
into :strlist separated by '|'
from street_name
where typ=1;
quit;
data str_address;
set address;
loc=prxmatch("/(&strlist)/",address);
if loc^=0 then found=1;
run;
proc print data=str_address;run;
Hi @SunnySong , I think this is the macro you are going to write. "PO_BOX" in the first dataset became "PO BOX" in the second dataset, so the value of the new column in the final output table is missing (it would be "3" if the "PO_BOX" value was the same in the first and second dataset). If you want to deal with this issue (the value in the second dataset is different from those in the first dataset) you need more coding step. You do not need any parameters for the macro. The macro can be simplified a bit because what you want is just the last output table. When finalize the macro do not forget to add the %local statement.
data street_name;
input st_nm $ typ 4.;
datalines;
ROAD 1
RD 1
STREET 1
ST 1
PLACE 1
PL 1
PO_BOX 3
UNIT 2
SUITE 2
;
run;
proc print data=street_name;run;
data address;
input address $20.;
datalines;
3 ARRON ROAD
15 BONNY RD
7 DAISY STREET
PO BOX 15
UNIT 6
89 ROSE PLACE
9 HOPE ST
;
run;
proc print data=address;run;
%macro addressloc;
proc sql;
select distinct typ
into :typlist
from street_name;
quit;
%put &typlist &sqlobs;
%let n=&sqlobs;
%put &n;
%do i=1 %to &n;
proc sql;
select st_nm
into :strlist separated by '|'
from street_name
where typ=&i;
quit;
%put &strlist;
data str_address;
set address;
set str_address;
loc=prxmatch("/(&strlist)/",address);
if loc^=0 then found=&i;
run;
proc print data=str_address;run;
%end;
%mend addressloc;
%addressloc;
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
1195 views
-
5 likes
-
-
