Finding keywords from one list in variable string

[JoanneR]

Hi all,

I have been trying to get SAS to read through a list of keywords that are saved in an excel spreadsheet and then search for those words in variables from a sas dataset. 

What I've been doing is using setting those words into a macro and then using the "scan" and "findw" functions to search for the words. (code below). This was working fine until I realized that some of my words in the list have comma's in between them. An example of this problem is we might have the word "1,3-monocytocane" and the variable may have the following reported: "Out of those present 1 had fun." In this case, the "1" would be captured as a keyword and new_var would then equal 0. 

I have over 100 words and I can't change the way they are written. I've tried creating a few proc sql to tackle the problem, but I'm not getting what I need. 

proc sql;
select words into:samplelist separated by ','
from wordlist
where words ne "" ;
quit;
%put &samplelist;

data temp2;
set temp1;
	new_var=1;
	i=1;
	do while ((scan("&samplelist",i,",") ne '') and new_var>0);		
	  i+1;															
	  name=scan("&samplelist",i-1,",");							
	  indx_var=findw(lowcase(old_var),compress(lowcase(name)));
	if indx_var>0 
		then new_var=0; 	
		else new_var=new_var+1;
	end;
run;

Any advice would be greatly appreciated! 

Thanks

[Patrick · Accepted solution]

If I understand this right then it's you who is populating a macro variable with a list of words. If so then why don't you just use something else than a comma as delimiter between the words. You could for example use a pipe character (|);

Then just use this pipe character in the scan() function.

scan("&samplelist",i,'|')

[PGStats]

This wouldn't work?

proc sql;
create table temp2 as
select
    old_var,
    words
from 
    temp1 as a left join 
    wordlist as b
on findw(old_var, words, " ", "sti")) > 0;
quit;