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;
%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%) |
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.
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.
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.
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.
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.
Ready to level-up your skills? Choose your own adventure.