DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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;


Accepted Solutions
Solution
‎01-04-2012 02:16 PM
Super User
Super User
Posts: 7,076

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,076

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

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;

Occasional Contributor
Posts: 5

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


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;

Solution
‎01-04-2012 02:16 PM
Super User
Super User
Posts: 7,076

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

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

Occasional Contributor
Posts: 5

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

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

PROC Star
Posts: 7,492

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

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

Super Contributor
Posts: 1,636

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

%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

Occasional Contributor
Posts: 5

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 369 views
  • 3 likes
  • 4 in conversation