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;
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;
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.
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;
thanks but I can't use a number. The start month and end month will be different
@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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.