2 inputs into do loop in SAS

Question

I need to create a sas do loop that will reference two moving values. This do loop will apply to a macro.

The Macro will look as follows:

%macro test (date1, date2);
DATA WANT;
SET HAVE;
IF INPUT_&DATE1. > INPUT_&DATE2. THEN R_&DATE1. = 1; ELSE R_&DATE2. = 0;
RUN;
%MEND;

I will then want to run this through a various dates so I would like to do something like this:

%MACRO APPLY_TEST;
%DO i = 4 %to 27 %by 1;
%test (&MMMYY&&i.);
%end;
%mend;

&APPLY_TEST;

Where &mmmyyy&i. will be dates so &mmmyy1 will be Sep23 for example and there are columns for all the dates I want to run through.

So as you can see the above will work when there's only one date inputting into the macro, but I would like it to input two dates. I'm not sure how to do this, would I need to include a

%DO j=5 %to 28 %by 1;

If so how would I add that to what I currently have?

Answer 1

I suspect you have created a series of macro variables with names that share a common prefix and have a number suffix. MMMYY1, MMMYY2, etc.

To use an index (or counter) variable to reference those you need to use a double &.

%let mmmyy1 = JAN2023 ;
%let mmmyy2 = FEB2023 ;
%let i=1;
%put The value or MMMYY&i is &&MMMYY&i ;

The && is converted to a single & and the macro process reminds itself to re-process the resulting string. So on the first pass &&MMMYY&i becomes &MMMYY1 which is then evaluated on the second pass to become JAN2023.

Example:

1295      %let mmmyy1 = JAN2023 ;
1296      %let mmmyy2 = FEB2023 ;
1297      %let i=1;
1298      %put The value or MMMYY&i is &&MMMYY&i ;
The value or MMMYY1 is JAN2023
1299      %let j=%eval(&i+1);
1300      %put The value or MMMYY&j is &&MMMYY&j ;
The value or MMMYY2 is FEB2023

Answer 2

I can't test this, since I do not have any sample data.

However, it seems that your problem is mainly to get the loop right. You can nest Macro Do Loops like below. I always find it easiest to get a hold of this first using the %Put Statement like this.

Hopefully this can get you going.

%macro APPLY_TEST;
%do i = 4 %to 27 %by 1;
  %do j = 5 %to 28 %by 1;
     %put &=i. &=j.;
  %end;
%end;
%mend;

%APPLY_TEST

Answer 3

Your approach will 'churn' the input data set and possibly require a lot of scaffolding to process the data and the flags.

You might be better off transposing (pivoting) the data so that the date values that are part of your variable names become actual data. Processing will be much easier from that point forward with regards to date-based grouping, classification and sub-setting.

Sample data

data make;
  do date = '01jan2023'd to '01jun2023'd-1;
    name = 'INPUT_' || put(date,monyy5.) || put(day(date),z2.);
    value + 1;
    output;  
  end;
run;
proc transpose data=make out=vars(drop=_name_);
  id name;
  var value;
run;
data have(keep=rowid input_:);
  length rowid 8;
  if 0 then set vars;
  do rowid = 1 to 100;
    array inputs input_:;
    do over inputs; k+1;inputs=k; end;
    output;
  end;
  stop;
run;

Tranpose

proc transpose data=have out=tall(rename=col1=value);
  by rowid;
  var input_:; 
run;
data want (keep=rowid date value);
  length rowid date 8.;
  set tall;
  datestring = scan(_name_,2,'_');
  date = input(substr(datestring,6,2)||substr(datestring,1,5), date9.);
  format date yymmdd10;
run;