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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.