topic Re: Can someone help me troubleshoot my macro code? Thanks! in SAS Programming

Can someone help me troubleshoot my macro code? Thanks!

colettb11 — Thu, 04 Sep 2025 20:28:12 GMT

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;

Re: Can someone help me troubleshoot my macro code? Thanks!

Tom — Fri, 05 Sep 2025 00:09:34 GMT

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.

Re: Can someone help me troubleshoot my macro code? Thanks!

PaigeMiller — Fri, 05 Sep 2025 10:42:40 GMT

@colettb11

I repeat what @Tom said, use an array.

Re: Can someone help me troubleshoot my macro code? Thanks!

Kathryn_SAS — Fri, 05 Sep 2025 12:18:28 GMT

Array that has been suggested is a good alternative.

The reason why your macro is not working is because you are using DATA step variables on a %IF condition which is not correct. If you change your code to use an IF statement, you should get the expected results.

%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;
options mprint mlogic symbolgen;
%test;