Solved
New Contributor
Posts: 3

# How do I replace words in a character string based on words in another data set?

Hi everyone,

I have a problem that I hope someone could help with.  I'm fairly new to SAS (9.4) so I apologize if my description is not clear and does not use the appropriate SAS terminology.  Here is the problem:

I have 2 SAS data sets - data set A contains over 500 words and data set B contains over 10,000 complex character strings.  I would like to write a program that will evaluate every word data set B to see if it exists in data set A.  If it does, then replace the word with something else (the replacement is abritrary) and put the final result in data set C.  For example, if the replacement word is HAT, then:

Data Set A

1  CAR

2  DOG

3  MAN

Data Set B

1  THE DOG RODE IN THE CAR WITH THE MAN

2  THIS CHEESEBURGER IS GREAT

Data Set C

1  THE HAT RODE IN THE HAT WITH THE HAT

2  THIS CHEESEBURGER IS GREAT

I've tried finding a solution to this problem in this message board and others but have not found a satisfactory solution.  Any help will be greatly appreciated.  Thanks in advance!

Accepted Solutions
Solution
‎09-13-2016 08:34 AM
Posts: 3,167

## Re: How do I replace words in a character string based on words in another data set?

[ Edited ]

I suppose it could be more efficient if we load table 'A' into a Hash object, but no comparison is done. Here is an old school way of doing it:

data a;
input id words\$;
cards;
1  CAR
2  DOG
3  MAN
;

data b;
infile cards truncover;
input b_id sentences\$100.;
cards;
1  THE DOG RODE IN THE CAR WITH THE MAN
2  THIS CHEESEBURGER IS GREAT
;

data want;
set b;

do i=1 to nobs;
set a nobs=nobs point=i;

sentences=prxchange(cats('s/\b',words,'\b/HAT/'),-1,sentences);
end;

keep b_id sentences;
run;

All Replies
Solution
‎09-13-2016 08:34 AM
Posts: 3,167

## Re: How do I replace words in a character string based on words in another data set?

[ Edited ]

I suppose it could be more efficient if we load table 'A' into a Hash object, but no comparison is done. Here is an old school way of doing it:

data a;
input id words\$;
cards;
1  CAR
2  DOG
3  MAN
;

data b;
infile cards truncover;
input b_id sentences\$100.;
cards;
1  THE DOG RODE IN THE CAR WITH THE MAN
2  THIS CHEESEBURGER IS GREAT
;

data want;
set b;

do i=1 to nobs;
set a nobs=nobs point=i;

sentences=prxchange(cats('s/\b',words,'\b/HAT/'),-1,sentences);
end;

keep b_id sentences;
run;
New Contributor
Posts: 3

## Re: How do I replace words in a character string based on words in another data set?

Wow, this is great.  Thank you so much, it works beautifully.

☑ This topic is solved.