using macro's, SQL, do loops to create SAS Variables for dates to run reports back 24 months

[Arum]

Hello this is my first post:

I want to create a date variable to run a report for each month going back 24 months without having to manually put in the dates. I wrote this code but I am getting a

Warning: Apparent symbolic reference D1 not resolve... etc.

%let  month2 = '01AUG11'd;

%macro

mr_T;

     %do i = 1 %to 24:

proc sql noprint;

     select intnx('month', &&month2, -&i) format=date7. into :d&&i from temp; quit;

%end;

%mend;

%mr_t;

[Tom]

Use DATE9 instead of DATE7 to avoid confusion.

You have a colon after your %DO instead of a semi-colon .

You have not defined the D1, D2, ... macro variables as global. If they do not exist with the macro MR_T starts then they will disappear when it ends.

You do not need macro code to do this.

data _null_;
    do i = 1 to 24 ;
       call symputx(cats('D',i),put(intnx('month',&month2,-1*i),date9.);
    end;
run;

[Arum]

Thanks Tom I mistyped the colon after the %do instead of semi-colon. Why does date9 work better? Also I am trying to run the example you sent me, but it gives an error of 79-322 (expecting a). I will look at how to use your method, as I have been relying on Proc Sql to do this always.

%let month2 = '01AUG11'd;

55 data _null_;

56 do i = 1 to 24 ;

57 call symputx(cats('D',i),put(intnx('month',&month2,-1*i),date9.);

-

79

ERROR 79-322: Expecting a ).

58 end;

59 run;

[Tom · Accepted solution]

Add in the missing right paren at the end of the statement to close the symputx function call.

call symputx( cats('D',i) , put( intnx('month',&month2,-1*i) , date9.) );

For 2 digit year problems check http://en.wikipedia.org/wiki/Year_2000_problem

[Arum]

This works great, thanks! I like the simplicity and it is perfect for creating the date variable needed.  I really like the trick to multiple i * -1. This just gave me an idea how to fix something else.

A

[art297]

You have to provide more info.  You have :s when you should have ;s, and you use && where you probably only need &.  The following works:

%let  month2 = 01AUG11;

data temp;

  informat date date7.;

  input date;

  cards;

01AUG11

01JUL11

01JUN11

01MAY11

01APR11

01FEB11

01JAN11

;

%macro mr_T;

  %do i = 1 %to 3;

    proc print data=temp

     (where=(date eq intnx('month', "&month2"d, -&i.)));

    run;

  %end;

%mend;

%mr_t

[Linlin]

%let  month2 = '01AUG11'd;

data temp;

do  i = 1 to 24;

v=intnx('month', &month2, -i);

format v date7.;

output;

end;

run;

data _null_;

  set temp;

  call symputx(cats('d',strip(_n_)),put(v,date7.));

  run;

%put _user_;

Linlin

[Arum]

Thanks this is very helpful. I am new to SAS so tricks like %put _user_ are good. This works well. I will be using this trick and others parts.