Can someone help me troubleshoot my macro code? Thanks!

[colettb11]

Hello,

I have a dataset that contains variables dt_1 through dt_7 and start date. 

I need to loop through the dt_ variables to see which one start_date is after.  I want to check dt_7, then dt_6, then dt_5, etc, and then assign the corresponding value of tier_.  (For example, if start date is >= dt_7 then I want to assign the value of tier_7 to applied_tier).

When I run this code, &i is resolving to the correct number, however it is not returning the value of dt_7, dt_6, etc.  It's just returning the string "dt_7",'dt_6", etc.

I'd appreciate some feedback on where I'm going wrong in my code.  My company doesn't allow uploading of files to this website so here is some sample ( I know it's hard to read).  In this case the macro should return the value of tier_5 because the first date that Start Date is after (going from right to left) is 24MAY2024

Start Date      dt_1               Tier_1    dt_2              Tier_2      dt_3                 Tier_3      dt_4               Tier_4      dt_5              Tier_5       dt_6             Tier_6       dt_7                Tier_7
9/30/2024    17-Nov-22    4           22-Mar-24    5             23-Mar-24      3              8-May-24      5               18-Oct-24    4               21-Jan-25    3               25-Mar-25     3

%macro test;

data test;

merge v (in=a)

  trans (in=b);

by site_number;

if a;

%let i = 7;

%do %while (&i > 0);

%if (dt_&i ne . and start_date ge dt_&i) %then %do;

applied_tier = tier_&i;

%end;

%let i = %eval(&i - 1);

%end;

run;

%mend test;

%test;

[Tom]

What do you mean by "the macro should return the value of tier_5"?

In general the macro language just generates code.  SAS itself is what runs the code.

In your case the macro is generating a DATA step. So what is the data step that you want to run?  Type that code out by hand and then you can begin to work on creating a macro that generates the code you want. 

Do you even need to generate any code?? (and hence do you even need to create a macro??)

Why not just use an ARRAY?

Perhaps something like this?

data test;
  merge v (in=a)
    trans (in=b)
  ;
  by site_number;
  if a;
  array dt_ [7];
  array Tier_ [7];
  do index=dim(dt_) to 1 by -1 until (not missing(applied_tier));
    if .Z < dt_[index] <= start_date then applied_tier = tier_[index];
  end;
run;

Note that you current macro logic is just going to generate 7 assignment statement as part of the data step:

MPRINT(TEST):   applied_tier = tier_7;
MPRINT(TEST):   applied_tier = tier_6;
MPRINT(TEST):   applied_tier = tier_5;
MPRINT(TEST):   applied_tier = tier_4;
MPRINT(TEST):   applied_tier = tier_3;
MPRINT(TEST):   applied_tier = tier_2;
MPRINT(TEST):   applied_tier = tier_1;

That is because the condition in your %IF statements is TRUE for all values of the macro variable I.  

dt_&i ne .

A string starting with the letters dt is never never going to match a period.

start_date ge dt_&i

And a string starting with s is always greater than a string starting with d.