BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
nstdt
Quartz | Level 8

I need to search through the 15 variables of a dataset for a specific substring (the word "None"). When I use the code below for just one variable, I can get the results I want. However, when I try to use a Macro to loop through all 15 variables, the code does not detect the word None - although there are no errors in the log.

 Code without macro (just one variable):

data countvar;
set indata;
length word $ 25;
searchstr = var_1;
** Count occurrences of substring None
None_flag=0;
do i=1 to countw(searchstr);
word=scan(searchstr,i);
if findw("None",strip(word),'i')>=1 then do;
None_flag=1;
leave;
end;
end;
run;
Now when I try to create a macrovariable list of all the variables in the dataset 'Indata' inside 'list' and pass the above function into a macro "loopit", the code runs without errors but fails to detect the substring "None" - the variable None_flag is zero for all observations in the dataset.
%macro loopit(list);
	data countvar1;
	set Dat_q3_wide;
    %do i = 1 %to %sysfunc(countw(&list.));
    	%let word  = %scan(&list., &i.);
		None_flag=0;
	
   		%if %sysfunc(findw(None,strip(&word),ii))>=1 %then %do;
      		None_flag=1;
    	%end;
	%end;
	run;
%mend loopit;
%let list = Var_1 Var_2 Var_3 Var_4;
%loopit(list=&list)


 Here is some sample data: 

ID Var_1 Var_2 Var_3

11

 

None(0%) Nearly all(100%) None(0%)
12 Nearly all(100%) None(0%) None(0%)
13 None(0%) Some (50%) Nearly all(100%)
       
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

When the need is to iterate through variables in a data set and do a similar operation with each the proper tool is an ARRAY. The array definition statement assigns a shorthand name associated with the listed variables. Then another index variable can be used to select which element of the array is used.

So your code would become something like:

data countvar;
set indata;
array s (*) Var_1 Var_2 /*list the names*/;
/* note that if the names are "nice" you can use a list like
   Var_1 - Var_25 if the names are indeed sequentially numbered
*/
length word $ 25;
None_flag=0;
do j=1 to dim(s);
   searchstr = s[j];
      do i=1 to countw(searchstr); 
          word=scan(searchstr,i);
          if findw("None",strip(word),'i')>=1 then do;
              None_flag=1; 
           leave; 
          end; 
      end; 
end;
drop i j; /*unless you see a need for these variables later*/
run; 

Another approach for something with only one reason to search for fixed text might be to concatenate all the variables together and search a single string

data countvar;
set indata;
array s (*) Var_1 Var_2 /*list the names*/;
/* note that if the names are "nice" you can use a list like
   Var_1 - Var_25 if the names are indeed sequentially numbered
*/
 None_flag = findw("None",catx(' ',of S(*))) >=1;

run; 

This combines all of the variables into a string with spaces separating the elements and searches for the word one time in the long string.

SAS will return  a value of 1 for true and 0 for false for a comparison so the if/then isn't really needed if the idea is to have 0/1 values on each observation.

View solution in original post

2 REPLIES 2
Reeza
Super User
data have;
infile cards truncover dsd;
length id 8. var_1-var_3 $20.;
input ID  Var_1 $  Var_2  $ Var_3 $;
cards;
11, None(0%),    Nearly all(100%),    None(0%)
12,  Nearly all(100%),    None(0%),    None(0%)
13,  None(0%),    Some (50%) , Nearly all(100%)
14, Some(50%), Nearly all(50%), Some(20%)
;
run;

data want;
set have;
array _var(*) var_1-var_3;

if find(catx(" | ", of _var(*)), "None") > 0 then flag="1";
else flag = "0"; *probably use better names;

run;

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

 

Seems like a better usage for arrays than macros.

ballardw
Super User

When the need is to iterate through variables in a data set and do a similar operation with each the proper tool is an ARRAY. The array definition statement assigns a shorthand name associated with the listed variables. Then another index variable can be used to select which element of the array is used.

So your code would become something like:

data countvar;
set indata;
array s (*) Var_1 Var_2 /*list the names*/;
/* note that if the names are "nice" you can use a list like
   Var_1 - Var_25 if the names are indeed sequentially numbered
*/
length word $ 25;
None_flag=0;
do j=1 to dim(s);
   searchstr = s[j];
      do i=1 to countw(searchstr); 
          word=scan(searchstr,i);
          if findw("None",strip(word),'i')>=1 then do;
              None_flag=1; 
           leave; 
          end; 
      end; 
end;
drop i j; /*unless you see a need for these variables later*/
run; 

Another approach for something with only one reason to search for fixed text might be to concatenate all the variables together and search a single string

data countvar;
set indata;
array s (*) Var_1 Var_2 /*list the names*/;
/* note that if the names are "nice" you can use a list like
   Var_1 - Var_25 if the names are indeed sequentially numbered
*/
 None_flag = findw("None",catx(' ',of S(*))) >=1;

run; 

This combines all of the variables into a string with spaces separating the elements and searches for the word one time in the long string.

SAS will return  a value of 1 for true and 0 for false for a comparison so the if/then isn't really needed if the idea is to have 0/1 values on each observation.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 661 views
  • 2 likes
  • 3 in conversation