DATA Step, Macro, Functions and more

Loop to replace text from a pre-defined abbreviation list

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Loop to replace text from a pre-defined abbreviation list

[ Edited ]

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!

 

 


Accepted Solutions
Solution
‎02-21-2017 04:57 PM
Frequent Contributor
Posts: 75

Re: Loop to replace text from a pre-defined abbreviation list

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.

View solution in original post


All Replies
Super User
Posts: 19,785

Re: Loop to replace text from a pre-defined abbreviation list

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

 

Solution
‎02-21-2017 04:57 PM
Frequent Contributor
Posts: 75

Re: Loop to replace text from a pre-defined abbreviation list

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.

Occasional Contributor
Posts: 15

Re: Loop to replace text from a pre-defined abbreviation list

Posted in reply to nehalsanghvi
Thanks! this seemed to have done the trick!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 157 views
  • 3 likes
  • 3 in conversation