topic Re: Macro to iterate through variables and apply String matching function in SAS Programming

Macro to iterate through variables and apply String matching function

nstdt — Thu, 09 Nov 2023 16:52:02 GMT

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%)

Re: Macro to iterate through variables and apply String matching function

Reeza — Thu, 09 Nov 2023 17:02:56 GMT

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.

Re: Macro to iterate through variables and apply String matching function

ballardw — Thu, 09 Nov 2023 17:11:48 GMT

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.