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!
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.
Search Lexjansen.com for some pre-written macro's for address cleaning and standardizing.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.