02-13-2018 02:55 PM
I am trying to create a small database of months from one date to another, incrementing by month. I want three observations:
The following code just gives me 10/1/2017 and doesn't loop.
data addMonths ;
format month MMDDYY10. ;
put month ;
DO i = (month <= %unquote(%str(%')&maxdt.%str(%'d))) ;
proc print ; run;
02-13-2018 03:09 PM
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;
02-13-2018 03:27 PM
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.
02-13-2018 03:31 PM
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;
02-13-2018 03:47 PM
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.
02-13-2018 03:36 PM
do _n_=0 to 5000 until (date >= "&maxdt"d) ;
date = intnx('month', "&maxmth"d, _n_) ;
format date mmddyys10.;
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.