topic Dynamic Dates in a Macro Do loop in SAS Programming

Dynamic Dates in a Macro Do loop

shidehrafigh — Fri, 22 Mar 2019 19:51:57 GMT

Hi everyone

I am struggling with creating a macro that loops through a date range that is specified.

I would like to give my macro a start date and end date and run a piece of macro to automatically create data sets.

the isse is where I have to define the second date where it starts the iteration:

%let date=%substr(%sysfunc(intnx(month,%substr(%sysfunc(inputn(&start,yymmn6.),yymmn6.),1,6),1,yymmn6.),yymmn6.),1,6);

I get the following warning:

WARNING: Argument 4 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set to a missing value.

options symbolgen;
%let start='20181101'd;
%let end='20190201'd;
data _null_;
datestart = input("&start",yymmn6.);
dateend = input("&end",yymmn6.);
counter = intck('month',datestart,dateend)+1;
call symput('counter',put(counter,best.));
run;

%macro loop;
%let date=%substr(%sysfunc(inputn(&start,yymmn6.),yymmn6.),1,6);
%do i=1 %to &counter;
data data_&date.;
set data_&date.;
run;
%let date=%substr(%sysfunc(intnx(month,%substr(%sysfunc(inputn(&start,yymmn6.),yymmn6.),1,6),1,yymmn6.),yymmn6.),1,6);
%put &i &counter &date;
%end;
%mend loop;
%loop

Re: Dynamic Dates in a Macro Do loop

Reeza — Fri, 22 Mar 2019 20:11:08 GMT

Maybe this can help.

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n01vuhy8h909xgn16p0x6rddpoj9.htm&docsetVersion=9.4&locale=en

I'm also assuming that's partial code, because what you're doing there won't change anything.

Re: Dynamic Dates in a Macro Do loop

Tom — Fri, 22 Mar 2019 20:41:41 GMT

First make sure you start with valid values. Date literals must be a a format that the DATE informat can understand.

%let start='01NOV2018'd;
%let end='01FEB2019'd;

If you want to loop in macro code then increment using a normal integer based %DO loop over the offset number and use it generate a valid date.

%macro test(from,to);
%local i date_raw date_fmt;
%do i=0 %to %sysfunc(intck(month,&from,&to));
  %let date_raw=%sysfunc(intnx(month,&from,&i));
  %let date_fmt=%sysfunc(intnx(month,&from,&i),date9);
  %put &=i &=date_fmt &=date_raw;
%end;
%mend test;

267   %test(&start,&end);
I=0 DATE_FMT=01NOV2018 DATE_RAW=21489
I=1 DATE_FMT=01DEC2018 DATE_RAW=21519
I=2 DATE_FMT=01JAN2019 DATE_RAW=21550
I=3 DATE_FMT=01FEB2019 DATE_RAW=21581

If you just want to use the value in code then just use the raw number of days. If you want to use a value like 01FEB2019 in code then you need to enclose it in quotes and add a D suffix.

"&date_fmt"d

Re: Dynamic Dates in a Macro Do loop

shidehrafigh — Sun, 24 Mar 2019 09:24:45 GMT

Hi Reeza

Thanks a lot! I looked for a documentation for quite a long time but I couldn't find something that would address the date issue.

I used the same code to create the previous month data for each month as well.

Regards,

Shideh