BookmarkSubscribeRSS Feed
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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;

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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;
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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.

ballardw
Super User

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;
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

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

Reeza
Super User

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 804 views
  • 0 likes
  • 5 in conversation