Need help with %do in a macro

[cbig]

Hello community,

There is something not working right in this macro and I can't figure out where my issue is.  I have 11 variables (var, var2, var3, etc.  The first var doesn't have a '1') that I need to look through.  If any of the variables  equal the value of "Related" then flag should be set to "keep" for the observation.  The flag variable is retained, so I set it to missing at the beginning of the processing of an observation.  The result I am getting is that the flag = "keep" even for some rows where none of the variables equal "Related".  

I would love a 2nd pair of eyes on this if someone has time.  Thanks!

%macro check(varlist=,value=);

flag = "";

if var= "Related" then flag = "keep";

%do i = 2 %to 11;

%if %cmpres(%scan(&varlist,&i)) = "&value" %then flag = "keep";

%end;

%mend check;

%check(varlist=var2 var3 var4 var5 var6 var7 var8 var9 var10 var11,value=Related);

[Tom]

You probably do not need to generate any SAS code at all, so you probably don't need a macro at all.

It appears that FLAG should be set to keep when Related appears in any of those variables.

So you probably want something like this:

data want;
  set have;
  if whichc("Related",of var var2-var10) then FLAG="keep";
run;

If you do need to make a macro to help generate that code then perhaps you want something like this:

%macro mymac(in,out,value,varlist);
data &out;
  set ∈
  if whichc("&value",of &varlist) then FLAG="keep";
run;
%mend mymac;

Which you can use to generate the above data step by calling it like this:

%mymac(in=have,out=want,value=Related,varlist=var var2-var10);

[cbig]

Thank you Tom!  You are absolutely right, I just need to check the value of the variables.  I thought cycling through them via a macro would be the best way but I see I was wrong.

One question - doesn't WHICHC look for the string (kind of like an index)?  If so, it would consider values of 'Not Related' as a match, would it not, since it contains the string 'Related'?

[Tom · Accepted solution]

---

@cbig wrote:

Thank you Tom!  You are absolutely right, I just need to check the value of the variables.  I thought cycling through them via a macro would be the best way but I see I was wrong.

One question - doesn't WHICHC look for the string (kind of like an index)?  If so, it would consider values of 'Not Related' as a match, would it not, since it contains the string 'Related'?

---

No.  You are thinking of INDEX() or FIND().  The WHICHC() function is doing an EQUALITY test.  It returns the index number of the first value that matches.  So if VAR2 is the first variable with that value then it returns 2. When none match then it returns zero.  The IF statement will treat zero as FALSE and any other actual number as TRUE.

[PaigeMiller]

So @Tom  has provided a very good solution. Let me try to explain why your original code doesn't work.

The %IF statement cannot access data set variable values. So you want to test to see if, for example, VAR2, has a value that is equal to 'Related'. %IF cannot do this, it has no idea what the values of VAR2 at any point in time. Data step tools, such as ARRAYs, the WHICHC function, and the good old IF statement, can do this.

So had the code been written with data step tools it might have worked. Please keep this in mind in the future when you try to write macros.

[FreelanceReinh]

In addition, your %DO loop started with &i=2 and thus skipped the first element of &varlist, i.e., var2 in your example. It ended with &i=11, but there is no 11th element in the variable list you supplied to the macro. (You were probably thinking of code like if var&i = "&value" then ...)