Loop to replace text from a pre-defined abbreviation list

[Josers18]

Hi Guys, 

I'm creating an address file which I then need to format into columns based on number of characters. However, I am first trying to replace words within the address string to common abbreviations to cut down some of the length.

I have an address string on a file (work.addresses) like so:

123 maple court avenue, anycity, anystate

I have a separate list (work.abbrev) which gives me the abbreviations, it has 2 columns Word and Abbreviation:

Court | Ct

Avenue | Av

I want to know how I can do a tranwrd loop or something similar which will look at the words from the address string against the words on the abbreviations list and on the ones that match replace the matching word with the abbreviation. 

final result would be: 123 maple Ct Av, anycity, anystate

I'm not quite sure how to build the syntax..there was a similar topic on here however, it didn't really cover how to loop through a separate list of words to match

Any help would be much appreciated!

[Reeza]

Search Lexjansen.com for some pre-written macro's for address cleaning and standardizing. 

[nehalsanghvi · Accepted solution]

Here you go:

data addresses;
Address='123 maple court avenue, anycity, anystate';
run;

data abbrev;
input Word$ Abbrev$;
datalines;
Court Ct
Avenue Av
;
run;

proc sql;
select count(*) into :abbrevcnt trimmed
from abbrev;

select cats(upcase(word),","), cats(upcase(abbrev),",") into :word1-:word&abbrevcnt., :abbrev1-:abbrev&abbrevcnt.
from abbrev;
quit;

%macro findreplace;
data addresses2;
set addresses;
%do z=1 %to &abbrevcnt.;
	Address=tranwrd(upcase(Address),"&&word&z.","&&abbrev&z.");
%end;
run;
%mend;

%findreplace;

To prevent the 'Court' in the name 'Maple Court' from being replaced with Ct, the code above looks for a comma to be present right after the word to be replaced.

[Josers18]

Thanks! this seemed to have done the trick!