topic Re: Replacing Multiple words in a field using TRANWRD in SAS Enterprise Guide

Replacing Multiple words in a field using TRANWRD

ismahero2 — Fri, 17 Oct 2014 14:57:51 GMT

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:

ID	ORIGINAL_NAME
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

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

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

Re: Replacing Multiple words in a field using TRANWRD

ballardw — Fri, 17 Oct 2014 15:51:21 GMT

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.

Re: Replacing Multiple words in a field using TRANWRD

KachiM — Fri, 17 Oct 2014 16:41:02 GMT

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;

Re: Replacing Multiple words in a field using TRANWRD

Tom — Fri, 17 Oct 2014 19:12:29 GMT

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;

Re: Replacing Multiple words in a field using TRANWRD

Ksharp — Sat, 18 Oct 2014 13:54:51 GMT

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

Re: Replacing Multiple words in a field using TRANWRD

Patrick — Sat, 18 Oct 2014 23:31:45 GMT

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)

Re: Replacing Multiple words in a field using TRANWRD

Ksharp — Sun, 19 Oct 2014 10:31:42 GMT

Yes. You are right. I overlook it .