Date function in SAS LOOP

Question

I have a date function in EIS column (EX:05FEB2007), I want to loop the year from 31DEC2012 to 31DEC2022, but have to do like 31DEC2012-EIS up to 31DEC2022-EIS in a loop.

%MACRO NFORE;
   %LET UC=100;
   %LET YS=2012;
   %DO I = 0 %TO 10;
      %LET YRS=%EVAL(&YS+&I);
      proc sql;
         create table  FORECAST_&YRS  as
         select t.*,
            case when (31DEC&YRS-EIS)/365<=10 then  Segment_10
                 when (31DEC&YRS-EIS)/365<=20 then  Segment_20
                 when (31DEC&YRS-EIS)/365<=30 then  Segment_30
                 when (31DEC&YRS-EIS)/365<=99 then  Segment_35
                 else stat
            end as TSN_AGE_&YRS
         from F_AG t;
      quit;
   %END;
%MEND NFORE;
%NFORE;

score 0 · Accepted Answer · answered Sep 25 '13 at 15:41

I'll preface this with my standard "this is a bad idea, there is a better way to do (your problem) than producing lots of datasets in a macro loop", but that said:

%MACRO NFORE;
%LET  UC=100;
%LET YS=2012;

%DO yrs= &ys. %TO %eval(&ys.+10);

proc sql;
create table  FORECAST_&YRS  as
select  *
,case when ("31DEC&YRS."d-EIS)/365<=10 then  Segment_10
      when ("31DEC&YRS."d-EIS)/365<=20 then  Segment_20
      when ("31DEC&YRS."d-EIS)/365<=30 then  Segment_30
      when ("31DEC&YRS."d-EIS)/365<=99 then  Segment_35
      else stat  end as TSN_AGE_&YRS
from F_AG;
quit;
%END;
%MEND NFORE;
%NFORE;

A slightly superior solution in terms of calculating actual years would be to use the INTCK function to determine years between dates, which would handle leap years - or even use the YEAR function on EIS and just compare &yrs. to year(EIS), since you're using DEC 31 anyway.

I think it's time for my "I wish SAS had never invented `%do`, as it is almost certainly a net detriment to SAS programming" ... — Joe, Sep 25 '13 at 15:42

Date function in SAS LOOP

1 Answers1