Desktop productivity for business analysts and programmers

Replacing Multiple words in a field using TRANWRD

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Replacing Multiple words in a field using TRANWRD

HI,


I have a variable for names and some of those names have some keywords I would like to take out.  I was trying using TRANWRD and it works, but I have to do so many TRANWRDS statements to eliminate all the possible keywords I have in my list.   I was trying to do a loop, but I am having problems.  Can anyone help?

Example Data:

IDORIGINAL_NAME
1Jose L Smith JR
2Angel P Jones SR
3Susan DECD White III Trust
4Henry West IV
5John Adams
6Jose DECD Hernandez


Words I want to eliminate: 'JR', 'SR', 'III', 'IV', 'DECD'.


This is the last code I tried:


DATA WORK.NEWTABLE (KEEP=ID ORIGINAL_NAME NEW_NAME);

SET LIBRARY.NAMES_ID;

NEW_NAME=ORIGINAL_NAME;

ARRAY KEYWORDS{*} $ _TEMPORARY_ ('JR', 'SR', 'III', 'IV', 'DECD');

DO i=1 TO 5;

     NEW_NAME{i+1}=TRANWRD(NEW_NAME{ i }, KEYWORDS{ i }, '');

     END;

RUN;

Desired Results

IDORIGINAL_NAMENEW_NAME
1Jose L Smith JRJose L Smitn
2Angel P Jones SRAngel P Jones
3Susan DECD White III TrustSusan White Trust
4Henry West IVHenry West
5John AdamsJohn Adams
6Jose DECD HernandezJose Hernandez

Accepted Solutions
Solution
‎10-17-2014 03:12 PM
Super User
Super User
Posts: 6,845

Re: Replacing Multiple words in a field using TRANWRD

You don't have to make an array since you can just specify the list in the DO loop.

data want;

   set have;

   n_name = o_name;

   length word $27 ;

   do word='JR', 'SR', 'III', 'IV', 'DECD' ;

      n_name = tranwrd(' '||n_name, ' '||strip(word)||' ', ' ');

  end;

   n_name = compbl(n_name);

   drop word ;

run;

View solution in original post


All Replies
Super User
Posts: 11,134

Re: Replacing Multiple words in a field using TRANWRD

I'll bet that you had an array reference error.

New_name is not an array so

NEW_NAME=TRANWRD(NEW_NAME, KEYWORDS{ i }, '');

would take 5 passes through the step.

You might want to use

Do I = 1 to dim(Keywords); If you have to add another item, such as 'II', to your list you don't have to change the 5 to 6.

Super Contributor
Posts: 290

Re: Replacing Multiple words in a field using TRANWRD

Hope this answers you.

data have;

input id o_name $27.;

datalines;

1  Jose L Smith JR      

2 Angel P Jones SR      

3 Susan DECD White III Trust

4 Henry West IV         

5 John Adams            

6 Jose DECD Hernandez

;

run;

data want;

length n_name $27;

array k[5] $4 _temporary_ ('JR', 'SR', 'III', 'IV', 'DECD');

   set have;

   n_name = o_name;

   do i = 1 to dim(k);

      word = k;

      n_name = tranwrd(n_name, compress(word), '');

   end;

   n_name = compbl(n_name);

   output;

keep n_name;

run;

Solution
‎10-17-2014 03:12 PM
Super User
Super User
Posts: 6,845

Re: Replacing Multiple words in a field using TRANWRD

You don't have to make an array since you can just specify the list in the DO loop.

data want;

   set have;

   n_name = o_name;

   length word $27 ;

   do word='JR', 'SR', 'III', 'IV', 'DECD' ;

      n_name = tranwrd(' '||n_name, ' '||strip(word)||' ', ' ');

  end;

   n_name = compbl(n_name);

   drop word ;

run;

Super User
Posts: 9,875

Re: Replacing Multiple words in a field using TRANWRD

You just want to get rid of them all ? That is it ?

data have;
input id o_name $27.;
datalines;
1  Jose L Smith JR      
2 Angel P Jones SR      
3 Susan DECD White III Trust
4 Henry West IV         
5 John Adams            
6 Jose DECD Hernandez
;
run;
data want;
 set have;
 new_name=prxchange('s/\s+(JR|SR|III|IV|DECD)\s+/ /o',-1,o_name);
run;

Xia Keshan

Respected Advisor
Posts: 4,137

Re: Replacing Multiple words in a field using TRANWRD

I would use '\b' instead of '\s' so the unwanted words can also be at the beginning or the very end of a string.

new_name=prxchange('s/\b(JR|SR|III|IV|DECD)\b/ /o',-1,o_name)

Super User
Posts: 9,875

Re: Replacing Multiple words in a field using TRANWRD

Yes. You are right. I overlook it  .

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 7138 views
  • 0 likes
  • 6 in conversation