DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor DSW
New Contributor
Posts: 3
Accepted Solution

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
Respected Advisor
Posts: 3,124

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;

View solution in original post


All Replies
Solution
‎09-13-2016 08:34 AM
Respected Advisor
Posts: 3,124

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 DSW
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.

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

Discussion stats
  • 2 replies
  • 216 views
  • 2 likes
  • 2 in conversation