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

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

Arum — Wed, 04 Jan 2012 17:32:21 GMT

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;

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

Tom — Wed, 04 Jan 2012 18:43:34 GMT

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;

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

art297 — Wed, 04 Jan 2012 18:44:43 GMT

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

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

Linlin — Wed, 04 Jan 2012 19:05:52 GMT

%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

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

Arum — Wed, 04 Jan 2012 19:07:19 GMT

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.);

ERROR 79-322: Expecting a ).

58 end;

59 run;

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

Tom — Wed, 04 Jan 2012 19:16:20 GMT

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

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

Arum — Wed, 04 Jan 2012 19:26:54 GMT

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.

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

Arum — Wed, 04 Jan 2012 22:33:32 GMT

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.