Check array for list of values in macro variable

[A_SAS_Man]

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.

[Reeza]

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.

---

[A_SAS_Man]

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;

[Tom]

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

[ballardw · Accepted solution]

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.

[A_SAS_Man]

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;

[Reeza]

You probably need to just add the TRIM as indicated by BALLARDW to your macro variables. It probably has spaces but it also worked for me, so there's something else that could be the issue as well.