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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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