topic Re: Findw error with macro variable in New SAS User

Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 18:00:25 GMT

Hi everyone,

I'm trying to use FINDW to find a list of words in string variables. Since I have a long list of words that were originally taken from Excel, I put them all into a macro variable. So far I have the syntax below, but each time I run it, I get an error message.

I have a feeling it has to do with the fact some of the words in my list have spaces, parentheses and dashes, but I'm not sure how to account for that in my code.

Any help is greatly appreciated!

data test;
set data;
	if findw(lowcase(var1),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var2),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var3),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var4),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var5),&alldrugs, '|','I S') >0 	
	then drug='Yes' ;
	else drug='No'; 
run;

Re: Findw error with macro variable

novinosrin — Thu, 18 Apr 2019 17:59:55 GMT

data test;
set data;
	if findw(lowcase(var1),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var2),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var3),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var4),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var5),&alldrugs, '|','I S') >0 /*or	this is the error*/
	then drug='Yes' ;
	else drug='No'; 
run;

or then drug='Yes' ; /*is invalid*/

Re: Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 18:00:10 GMT

Sorry, that's a mistake in the example. In the original code there isn't an or at the end. I've corrected the question.

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 18:06:18 GMT

Can you show us a portion of &alldrugs?

One correction is that &alldrugs must be in double quotes

if findw(lowcase(var1),"&alldrugs", '|','I S') >0 or

etc.

If that's not it, then please paste the entire SAS log, including the code and the error message (not just the error message) as text and not as a screen capture, by clicking on the {i} icon and pasting the text into the window that appears. Do not paste the text into your message any other way.

Re: Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 18:14:51 GMT

Good point! I forgot to add those in. It removes the error message, but I still end up with no observations for drug='Yes' when there should be.

In terms of the &alldrugs variable, it was created with the following syntax:

proc sql;
select drug_name into: alldrugs separated by ' '
from druglist;
quit;
%put &alldrugs;

and gave the drugs output into a table. Below are 4 of the drugs listed from the top.

1-(3-CHLOROPHENYL)PIPERAZINE
1-(5-METHOXY-1H-INDOL-3-YL)-2-PROPANAMINE
10-HYDROXYCARBAZEPINE
10-OH CARBAZEPINE

I also should mention, some of the drugs have comma's so they are written as 1,3-dimethoxy. Which is why I have the data as pipe delimited.

Thanks for the help!

Re: Findw error with macro variable

Tom — Thu, 18 Apr 2019 18:16:45 GMT

Are expecting one value of VAR1 to have ALL of those drug names IN THAT EXACT ORDER?

Or did you want to test if the value of VAR1 is in that list of drug names?

Did you mean:

findw("&alldrug",var1,....)

@JoanneR wrote:

Good point! I forgot to add those in. It removes the error message, but I still end up with no observations for drug='Yes' when there should be.

In terms of the &alldrugs variable, it was created with the following syntax:
proc sql;
select drug_name into: alldrugs separated by ' '
from druglist;
quit;
%put &alldrugs;
and gave the drugs output into a table. Below are 4 of the drugs listed from the top.

1-(3-CHLOROPHENYL)PIPERAZINE
1-(5-METHOXY-1H-INDOL-3-YL)-2-PROPANAMINE
10-HYDROXYCARBAZEPINE
10-OH CARBAZEPINE

Thanks for the help!

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 18:23:51 GMT

@JoanneR wrote:

and gave the drugs output into a table. Below are 4 of the drugs listed from the top.

1-(3-CHLOROPHENYL)PIPERAZINE
1-(5-METHOXY-1H-INDOL-3-YL)-2-PROPANAMINE
10-HYDROXYCARBAZEPINE
10-OH CARBAZEPINE

These drugs are upper case. Your original code tested if lowcase(var1) etc. contains these drugs, and you have a case mismatch, you will never get "Yes" until you fix that.

Re: Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 18:30:39 GMT

I tried changing that to make both lower case using the two following forms, but both don't seem to work.

data test;
set data;
	if findw(lowcase(var1),lowcase("&alldrugs"),' ', 'I S') >0 
then drug= 'Yes'; 
else drug='No'; 
run;

data test;
set data;
	if findw(lowcase(var1),compress(lowcase("&alldrugs")),' ', 'I S') >0 
then drug= 'Yes'; 
else drug='No'; 
run;

Thank you!

Re: Findw error with macro variable

ballardw — Thu, 18 Apr 2019 18:40:53 GMT

@JoanneR wrote:

Hi everyone,

I'm trying to use FINDW to find a list of words in string variables. Since I have a long list of words that were originally taken from Excel, I put them all into a macro variable. So far I have the syntax below, but each time I run it, I get an error message.

I have a feeling it has to do with the fact some of the words in my list have spaces, parentheses and dashes, but I'm not sure how to account for that in my code.

Any help is greatly appreciated!
data test;
set data;
	if findw(lowcase(var1),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var2),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var3),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var4),&alldrugs, '|','I S') >0 or 
	   findw(lowcase(var5),&alldrugs, '|','I S') >0 	
	then drug='Yes' ;
	else drug='No'; 
run;

And your example data containing VAR1, Var2, Var3 etc is where?

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

And you should provide the actual value of that macro variable.

I suspect the basic problem is order of the variables. The first argument of FINDW is the value to search in and the second parameter is what to search for. Does that sound like what you are doing?

See this small example:

data example;
   longstring= "this is a string with several words";
   x ="string with";
   y= findw(longstring,x);
   z= findw(x,longstr);
run;

Your FINDW code I think looks more like the Z version above.

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 18:43:39 GMT

As @ballardw said, we need to see your data now — a portion of it where you expect to get a match with one of the drugs in &alldrugs.

Re: Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 19:03:49 GMT

Due to the sensitivity of this data I cannot provide further info. Instead, I will provide an example to better explain my question. Lets say I have an Excel spreadsheet with a list of fake flowers

Flowers:

1. Orchids

2. Lilies

3. Roses

4. 1,4,5-Daisy

5. Dahlia

6. 3,4-Lilac(2-Lilies)Daisy

7. Common lilac

8. False goats beard

Say these flowers were placed into &alldrugs. Now in my data I have an observation with the following report under var1:

"The house was surrounded with various objects. Some objects being boxes, shoes, and roses."

I want sas to read through that statement and see if the keywords from &alldrugs are mentioned in that statement. If not then to say "No".

My syntax was originally this, but it takes too long to run and I would like it to be more efficient by using the syntax I provided in the original question.

	drug=1;
	i=1;
	do while ((scan("&alldrugs",i,"|") ne '') and drug>0);		
	  i+1;																/*Number of the drug found on the keyword list -1 */
	  drug_word=scan("&alldrugs",i-1,"|");							
	  indx_drug1=findw(lowcase(var1),compress(lowcase(drug_word)));
	  indx_drug2=findw(lowcase(var2),compress(lowcase(drug_word)));
	  indx_drug3=findw(lowcase(var3),compress(lowcase(drug_word)));
	  indx_drug4=findw(lowcase(var4),compress(lowcase(drug_word)));
	  indx_drug5=findw(lowcase(var5),compress(lowcase(drugd_word)));
	  indx_drug6=findw(lowcase(var6),compress(lowcase(drug_word)));
	  indx_drug7=findw(lowcase(var7),compress(lowcase(drug_word)));

if indx_drug1>0 or indx_drug2>0 or .... indx_drug7>0 then drug=1;
else drug=drug+1;

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 19:16:45 GMT

It's fine if you give us a made up example, because real data is sensitive.

This works for me.

%let alldrugs=orchids|lillies|roses|daisy;

data a;
	var1="The house was surrounded with various objects. Some objects being boxes, shoes, and roses.";
	i=1;
	do while (scan("&alldrugs",i,"|") ne '');		
	  i+1;																/*Number of the drug found on the keyword list -1 */
	  drug_word=scan("&alldrugs",i-1,"|");							
	  indx_drug1=findw(lowcase(var1),compress(lowcase(drug_word)));
	  output;
	end;
run;

Re: Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 19:26:19 GMT

Do you know if there's a way to run that without having to write everything out? The keylist has about 200 words, and the variables have around 50k observations with a different response on each.

Thank you! I really appreciate all the input that's been provided.

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 19:41:17 GMT

Is this what you mean?

data a;
	array index_drug $ index_drug1-index_drug4;
	array drug_word $ drug_word1-drug_word4;
	var1="The house was surrounded with various objects. Some objects being boxes, shoes, and roses.";
	do i=1 to countw("&alldrugs",'|');
	  drug_word(i)=scan("&alldrugs",i,"|");							
	  index_drug(i)=findw(lowcase(var1),compress(lowcase(drug_word(i))));
	end;
	if sum(of index_drug:)>0 then index=1; else index=0;
	drop var1 i;
run;

You can drop additional variables from the data set if desired.

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 19:53:27 GMT

or this?

%let alldrugs=orchids|lillies|roses|daisy;
%let ndrugs=%sysfunc(countw(&alldrugs,|));
data customer;
	input var1 $100.;
	cards;
The house was surrounded with various objects. Some objects being boxes, shoes, and roses.
The house was surrounded with various objects. Some objects being boxes, lillies, and frogs.
The house was surrounded with various objects. Some objects being boxes, oranges and frogs.
;
data a;
	array index_drug $ index_drug1-index_drug4;
	array drug_word $ drug_word1-drug_word&ndrugs;
	set customer;
	cust_no=_n_;
	do i=1 to &ndrugs;
	  drug_word(i)=scan("&alldrugs",i,"|");							
	  index_drug(i)=findw(lowcase(var1),compress(lowcase(drug_word(i))));
	end;
	if sum(of index_drug:)>0 then index=1; else index=0;
	drop var1 i drug_word:;
run;

Re: Findw error with macro variable

PaigeMiller — Thu, 18 Apr 2019 20:14:31 GMT

Also, I was wrong earlier about the case of the text. Your FINDW command uses the modifier 'i' which then ignores the case of the text when finding matches.

Re: Findw error with macro variable

ballardw — Thu, 18 Apr 2019 22:29:20 GMT

@JoanneR wrote:

Due to the sensitivity of this data I cannot provide further info. Instead, I will provide an example to better explain my question. Lets say I have an Excel spreadsheet with a list of fake flowers

Flowers:

1. Orchids

2. Lilies

3. Roses

4. 1,4,5-Daisy

5. Dahlia

6. 3,4-Lilac(2-Lilies)Daisy

7. Common lilac

8. False goats beard

Say these flowers were placed into &alldrugs. Now in my data I have an observation with the following report under var1:

"The house was surrounded with various objects. Some objects being boxes, shoes, and roses."

I want sas to read through that statement and see if the keywords from &alldrugs are mentioned in that statement. If not then to say "No".

My syntax was originally this, but it takes too long to run and I would like it to be more efficient by using the syntax I provided in the original question.
	drug=1;
	i=1;
	do while ((scan("&alldrugs",i,"|") ne '') and drug>0);		
	  i+1;																/*Number of the drug found on the keyword list -1 */
	  drug_word=scan("&alldrugs",i-1,"|");							
	  indx_drug1=findw(lowcase(var1),compress(lowcase(drug_word)));
	  indx_drug2=findw(lowcase(var2),compress(lowcase(drug_word)));
	  indx_drug3=findw(lowcase(var3),compress(lowcase(drug_word)));
	  indx_drug4=findw(lowcase(var4),compress(lowcase(drug_word)));
	  indx_drug5=findw(lowcase(var5),compress(lowcase(drugd_word)));
	  indx_drug6=findw(lowcase(var6),compress(lowcase(drug_word)));
	  indx_drug7=findw(lowcase(var7),compress(lowcase(drug_word)));

if indx_drug1>0 or indx_drug2>0 or .... indx_drug7>0 then drug=1;
else drug=drug+1;

Do you actually need all of the different index variables for other work or do you just want the any are found? It isn't quite clear whether you need to count the number of drugs that appear in all of the phrases, one phrase or what. Which is why we ask for a concrete example. The values do not have to match your data as long as the behavior of the need can be demonstrated.

Re: Findw error with macro variable

JoanneR — Thu, 18 Apr 2019 23:24:28 GMT

This is a first step to a long process. As you said, my intent is to get the cases where at least one word from the keylist appeared under the variables for each observation.

The data is quite complex which is why it has been difficult to provide a concise example. I am looking at a total of 12 variables that each contain string variables (in the example they are vars1-7). The process in my original syntax was to find the words from the keylist and see if they are present in the responses for each observation. The word needs to match exactly as written in the keylist. Not every case will have a the keywords, and therefore will not be cases.

Re: Findw error with macro variable

ballardw — Thu, 18 Apr 2019 23:25:58 GMT

See if this also helps.

proc sql noprint;
   select quote(strip(name)) into : namelist separated by ','
   from sashelp.class
   ;
quit;
%let namecount = &sqlobs.;

%put Names are &namelist. count is &namecount.;

data example;
   infile datalines truncover;
   informat var1 var2 $35.;
   input var1 1-35
         /var2 1-35
   ;
datalines;
First var string with no name
and a second with no name
Example of one name 
that is John in two lines
And more names like Alice, Mary
and james
;
run;



data want;
   set example;
   array name{&namecount.} $20 _temporary_ (&namelist.);
   array v var1 var2;
   wordcount=0;
   do i= 1 to dim(name);
      do j=1 to dim(v);
         wordcount= wordcount+ (findw(v[j],strip(name[i]),' ','IPQRO')>0);
      end;
   end;
   drop i j;
run;

I am still not sure if you need an over all count of the drugs in all the phrases only, in each phrase, or some combination. This only does an over all count and only uses two variables to search as I am not going to spend a lot time making dummy data when I really don't know how well it might replicate your situation.

I does use a data set value to populate the list.

In your particular case you need to ensure that the length of the variable in the array name statement, the 20, is at least as long as the longest value in your word list.

The &sqlobs macro variable is an automatic count of the result from the last Proc SQL. It is also very likely to be resent so capture it into a known variable. The value is needed because you must specify the number of elements in the _temporary_ array. The SQL places quotes and commas so the single macro variable resolves properly for the _temporary_ array values.

Re: Findw error with macro variable

ballardw — Fri, 19 Apr 2019 16:44:51 GMT

@JoanneR wrote:

This is a first step to a long process. As you said, my intent is to get the cases where at least one word from the keylist appeared under the variables for each observation.

The data is quite complex which is why it has been difficult to provide a concise example. I am looking at a total of 12 variables that each contain string variables (in the example they are vars1-7). The process in my original syntax was to find the words from the keylist and see if they are present in the responses for each observation. The word needs to match exactly as written in the keylist. Not every case will have a the keywords, and therefore will not be cases.

If you need to know which variable found contained a key word then you could add and array to my example that has a Found value for each of the VAR variables. Instead of the single accumulator you would need to set the accumulator between the I and J loops to reset and then save after the end of the J loop if you need a NUMBER of matches, You can also use the I value when the count increments to keep identifiers of the found key words as that seems like it may be of interest.

Really providing some sort of example input data and the desired result for that is the way to ask questions. We are sort of wandering around with not knowing exactly the requirement such as :

Any of the key words found in any of the variables

How many of the key words found in any of the variables

Which of the key words found in any of the variables

Any of the key words found in each of the variables

How many of the key words found in each of the variables

Which of the key words found in each of the variables

This example creates a count per Var variable of the number of words matched and creates a delimited list of the words found from the key word list. If your values might contain commas then use a different delimiter in the CATX function call that you do not have in our key words.

proc sql noprint;
   select quote(strip(name)) into : namelist separated by ','
   from sashelp.class
   ;
quit;
%let namecount = &sqlobs.;

%put Names are &namelist. count is &namecount.;

data example;
   infile datalines truncover;
   informat var1 var2 $35.;
   input var1 1-35
         /var2 1-35
   ;
datalines;
First var string with no name
and a second with no name
Example of one name 
that is John in two lines
And more names like Alice, Mary
and james
;
run;



data want;
   set example;
   array name{&namecount.} $20 _temporary_ (&namelist.);
   array v var1 var2;
   /* the 2 below matches the number of VAR variables*/
   /* fcount will have the number of found words per var*/
   array fcount (2);
   /* this will hold a list of the found words*/
   array fwords (2) $ 200;
   
   do i= 1 to dim(name);
      do j=1 to dim(v);
         t= (findw(v[j],strip(name[i]),' ','IPQRO')>0);
         fcount[j]= sum(fcount[j], t);
         if t then fwords[j]= catx(',',fwords[j],strip(name[i]));
      end;
   end;
   drop i j t;
run;