DATA Step, Macro, Functions and more

looping through dates

Reply
Frequent Contributor
Frequent Contributor
Posts: 139

looping through dates

I am trying to create a small database of months from one date to another, incrementing by month. I want three observations:

10/1/2017

11/1/2017

12/1/2017

 

The following code just gives me 10/1/2017 and doesn't loop.

 

Assume:

%Let maxmth=01SEP2017;

%Let maxdt=01DEC2017;

 

data addMonths ;
format month MMDDYY10. ;
month=intnx('month',%unquote(%str(%')&maxmth.%str(%'d)),1) ;
put month ;
DO i = (month <= %unquote(%str(%')&maxdt.%str(%'d))) ;
output ;
month=intnx('month',month,1) ;
put month;
END;
run;
proc print ; run;

PROC Star
Posts: 1,289

Re: looping through dates

Before we address the loop issue, I would like to know why the fancy macro syntax in a datastep? what's the need?Why call macro processor and play with tokens?

 

Why can't it be easy and simple like

%Let maxmth=01SEP2017;

%Let maxdt=01DEC2017;

data addMonths ;
format month MMDDYY10. ;
month=intnx('month',"&maxmth"d,1) ;
put month =;
run;
Frequent Contributor
Frequent Contributor
Posts: 139

Re: looping through dates

Posted in reply to novinosrin

The fancy macros aren't necessary. I've been switching back and forth between doing sql pass through which thinks anything in double quotes is a column thus the need to do something for a single quote when using a date in the where statement of sql pass through, so I just wasn't thinking for this particular data step task which can use the double quotes. This needs to be automated so the maxmth is a macro variable that will be called based on the last existing month we have data for and the maxdt macro variable will be any month in the future edited by a user. I still need a simple list of new months with the loop based on a start month and an end month. Thanks.

Super User
Posts: 12,996

Re: looping through dates

Here's one way. No macro needed, will allow for just about as many months as you are likely to want.

 

data dateloop;
   startdate='01SEP2017'd;
   do i= 1 to 3; /*3 being the number of months you want*/
      wantdate=startdate;
      output;
      startdate=intnx('month',startdate,1,'B');
   end;
   format wantdate date9.;
   drop startdate;
run;
Frequent Contributor
Frequent Contributor
Posts: 139

Re: looping through dates

thanks but I can't use a number. The start month and end month will be different 

Super User
Posts: 22,820

Re: looping through dates


CP2 wrote:

thanks but I can't use a number. The start month and end month will be different 


Use INTCK to figure out the number of months you need first. Then use a DO loop. 

Super User
Posts: 6,526

Re: looping through dates

My version:

 

data want;

do _n_=0 to 5000 until (date >= "&maxdt"d) ;

   date = intnx('month', "&maxmth"d, _n_) ;

   output;

end;

format date mmddyys10.;

run;

 

The loop won't really iterate 5000 times ... it's just a safeguard in case wonky dates are entered that would cause the Until condition to remain false.

Ask a Question
Discussion stats
  • 6 replies
  • 98 views
  • 0 likes
  • 5 in conversation