topic Re: looping through dates in SAS Programming

looping through dates

CP2 — Tue, 13 Feb 2018 19:55:52 GMT

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;

Re: looping through dates

novinosrin — Tue, 13 Feb 2018 20:09:02 GMT

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;

Re: looping through dates

CP2 — Tue, 13 Feb 2018 20:27:12 GMT

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.

Re: looping through dates

ballardw — Tue, 13 Feb 2018 20:31:01 GMT

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;

Re: looping through dates

Astounding — Tue, 13 Feb 2018 20:36:01 GMT

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.

Re: looping through dates

CP2 — Tue, 13 Feb 2018 20:37:45 GMT

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

Re: looping through dates

Reeza — Tue, 13 Feb 2018 20:47:17 GMT

@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.