Dear all, I am doing some address data cleaning for my company. I managed to get the job done but I am still not satisfied as I know the script can be even simpler. Below is what I have achieved. I used values in street_name table to search in the address column in dataset (address). If there is a match the script will give me a value 1. BTW, the script is copied from our forum. The script works perfectly fine. But my curiosity pushes to go one step further - adding it in a macro statement. So when I run %addr_clean(macro_nm, 1) it will give me the same result. However, I am stuck at converting array syntax to a macro-understanding format. Please help me out. 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 "'" || " "|| STRIP(ST_NM) || "'" INTO: STREET SEPARATED BY ', ' FROM STREET_NAME WHERE TYP = 1.; QUIT; %PUT &STREET.; DATA STREET_ADDRESS; SET ADDRESS; ARRAY NN (%sysfunc(countw(QUOTE(&STREET.), %STR(,)))) $ 10 _TEMPORARY_ (&STREET.); DO I=1 TO DIM(NN); IF FIND(ADDRESS,STRIP(NN[i]),'i') GT 0 THEN DO; FOUND=1; MATCH=NN[i]; LEAVE; END; END; DROP I; RUN; ========================================================= %MACRO ADDR_CLEAN(ST_MACRO, TYP); PROC SQL NOPRINT; SELECT "'" || " "|| STRIP(ST_NM) || "'" INTO: &ST_MACRO. SEPARATED BY ', ' FROM STREET_NAME WHERE TYP =&TYP..; QUIT; /*I am stuck here and not able to do the rest to fit the code in a macro statement*/; %MEND; %ADDR_CLEAN(ST, TYP);
... View more
