I have a macro variable with multiple text values such as below.
data macro_variable_data;
input code $5.;
format code $5.;
datalines;
56849
98746
23654
12345
;
run;
proc sql;
select code
into :codes separated by " "
from macro_variable_data
;
quit;
What I want to do is use this macro variable in my array processing to check if any of the values in the macro variable exist in the columns making up my array. See example data below.
data have;
input code1 $6. code2 $6. code3 $6. code4 $6.;
datalines;
43501 93762 56849 10912
43502 12345 45678 90432
44563 92311 76320 13240
23450 28100 78910 23654
;
run;
data want;
input code1 $6. code2 $6. code3 $6. code4 $6. flag;
datalines;
43501 93762 56849 10912 1
43502 12345 45678 90432 1
44563 92311 76320 13240 0
23450 28100 78910 23654 1
;
run;
I have attempted to accomplish this like below.
data want;
set have;
array d code1-code4;
if &codes in d then flag=1;
else flag=0;
run;
But this produces the below error because it spells out all the elements of the macro variable at once.
25 GOPTIONS ACCESSIBLE;
26 data want;
27 set have;
28 array d code1-code4;
29 if &codes in d then flag=1;
NOTE: Line generated by the macro variable "CODES".
29 56489 98746 23654 12345
_____
388
200
ERROR 388-185: Expecting an arithmetic operator.
ERROR 200-322: The symbol is not recognized and will be ignored.
30 else flag=0;
31 run;
Any help or suggestions for other techniques is appreciated.
First start with non-macro code that works.
Your statement
if &codes in d then flag=1;
generates a line that looks like
if 56489 98746 23654 12345 in d then flag=1;
Does that look like valid code to you?
The IN operator takes a list of values, not variables so "in d" would not work in general.
Second, you created numeric values and your code variables are character, so you were attempting to compare numbers to characters and that often fares poorly because of behavior of implicit conversions.
So your SQL code would have to look like:
proc sql;
select quote(strip(code))
into :codes separated by " "
from macro_variable_data
;
quit;
to get quotes around the value and Strip to suppress the trailing blanks likely to appear for some if not all values in more complex data.
Which would allow something like:
data want; set have; array d code1-code4; flag=0; do i=1 to dim(d); if d[i] in ( &codes) then do; flag=1; leave; end;
end; drop i; run;
which tests each value for your array values one at a time to the list and then leaves the loop testing the first time a value match is found.
Your first flag in the WANT data set is wrong I believe (56849 vs 56489), if so, this should work.
1. You have character variables so you need to ensure your array and macro variables are properly formatted for character comparisons (ie quotes)
2. Loop through each value and check if it's present.
3. This is actually much easier in a long format - but I know many epidemiologists/statisticians prefer a wide format. Long format for data processing/wrangling, wide is for modeling/analytics.
data macro_variable_data;
input code $5.;
format code $5.;
datalines;
56489
98746
23654
12345
;
run;
data have;
input code1 $6. code2 $6. code3 $6. code4 $6.;
datalines;
43501 93762 56849 10912
43502 12345 45678 90432
44563 92311 76320 13240
23450 28100 78910 23654
;
run;
proc sql;
select quote(code)
into :codes separated by ", "
from macro_variable_data
;
quit;
data want;
set have;
array _codes (*) code1-code4;
flag=0;
do i=1 to dim(_codes) while(flag=0);
if whichc(_codes(i), &codes) >1 then flag=1;
end;
run;
@A_SAS_Man wrote:
I have a macro variable with multiple text values such as below.
data macro_variable_data; input code $5.; format code $5.; datalines; 56489 98746 23654 12345 ; run; proc sql; select code into :codes separated by " " from macro_variable_data ; quit;
What I want to do is use this macro variable in my array processing to check if any of the values in the macro variable exist in the columns making up my array. See example data below.
data have; input code1 $6. code2 $6. code3 $6. code4 $6.; datalines; 43501 93762 56849 10912 43502 12345 45678 90432 44563 92311 76320 13240 23450 28100 78910 23654 ; run; data want; input code1 $6. code2 $6. code3 $6. code4 $6. flag; datalines; 43501 93762 56849 10912 1 43502 12345 45678 90432 1 44563 92311 76320 13240 0 23450 28100 78910 23654 1 ; run;
I have attempted to accomplish this like below.
data want; set have; array d code1-code4; if &codes in d then flag=1; else flag=0; run;
But this produces the below error because it spells out all the elements of the macro variable at once.
25 GOPTIONS ACCESSIBLE; 26 data want; 27 set have; 28 array d code1-code4; 29 if &codes in d then flag=1; NOTE: Line generated by the macro variable "CODES". 29 56489 98746 23654 12345 _____ 388 200 ERROR 388-185: Expecting an arithmetic operator. ERROR 200-322: The symbol is not recognized and will be ignored. 30 else flag=0; 31 run;
Any help or suggestions for other techniques is appreciated.
Thank you for catching that, I have updated my question with corrected values. I seem to be having a little trouble after I update it like below. All I did was change the value in the macro variable data so it would match what's in the have set. But it doesn't flag the first line still for me, am I missing something here?
data macro_variable_data;
input code $5.;
format code $5.;
datalines;
56849
98746
23654
12345
;
run;
proc sql;
select quote(code)
into :codes separated by ", "
from macro_variable_data
;
quit;
data have;
input code1 $6. code2 $6. code3 $6. code4 $6.;
datalines;
43501 93762 56849 10912
43502 12345 45678 90432
44563 92311 76320 13240
23450 28100 78910 23654
;
run;
data want;
set have;
array _codes (*) code1-code4;
flag=0;
do i=1 to dim(_codes) while(flag=0);
if whichc(_codes(i), &codes) >1 then flag=1;
end;
run;
You can if you want keep the information on which of the CODE variables in HAVE is the first one that matched any of the search terms. And you can even keep track of which search term it matched.
proc sql noprint;
select quote(trim(code)) into :terms separated by ','
from macro_variable_data
;
%let n=&sqlobs;
quit;
data want ;
set have ;
array code code1-code4 ;
do index=1 to dim(code) until(found);
found=whichc(code[index], &terms);
end;
if not found then index=0;
run;;
Results:
Obs code1 code2 code3 code4 index found 1 43501 93762 56849 10912 3 1 2 43502 12345 45678 90432 2 4 3 44563 92311 76320 13240 0 0 4 23450 28100 78910 23654 4 3
First start with non-macro code that works.
Your statement
if &codes in d then flag=1;
generates a line that looks like
if 56489 98746 23654 12345 in d then flag=1;
Does that look like valid code to you?
The IN operator takes a list of values, not variables so "in d" would not work in general.
Second, you created numeric values and your code variables are character, so you were attempting to compare numbers to characters and that often fares poorly because of behavior of implicit conversions.
So your SQL code would have to look like:
proc sql;
select quote(strip(code))
into :codes separated by " "
from macro_variable_data
;
quit;
to get quotes around the value and Strip to suppress the trailing blanks likely to appear for some if not all values in more complex data.
Which would allow something like:
data want; set have; array d code1-code4; flag=0; do i=1 to dim(d); if d[i] in ( &codes) then do; flag=1; leave; end;
end; drop i; run;
which tests each value for your array values one at a time to the list and then leaves the loop testing the first time a value match is found.
I won't choose this as the solution as it seems less elegant/robust than the others provided here but I wanted show another method I figured out as I was working through this that was pretty simple. I just changed my macro variable creation sql statement to the following.
proc sql;
select code
into :codes separated by " in d or "
from macro_variable_data
;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: