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

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

DSW — Tue, 06 Sep 2016 14:39:34 GMT

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!

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

Haikuo — Tue, 06 Sep 2016 15:12:59 GMT

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;

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

DSW — Tue, 06 Sep 2016 16:53:16 GMT

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