Generate date based on previous date in do loop

[_Zack_]

Hi, I'm kind of stuck when try to generate a series of date within do loop. For iteration 0 and 1 the DATE is set with fixed formula, however starting from iteration > 1, two DATE should be generated within same month (EVENT_DAY_OF_MONTH, follow by end of month), and repeated this pattern until iteration < DURATION. 

data want;

format START_DT END_DT DATE DATE_LAG date9.;

START_DT = '19APR2018'd;
EVENT_DAY_OF_MONTH = 6;
END_DT = '05JAN2019'd;
DURATION = intck('month',START_DT,END_DT)*2;

do i=0 to DURATION by 1;
if i = 0 then DATE = START_DT;
if i = 1 then DATE = intnx('month',START_DT,i-1,'E');
DATE_LAG = LAG(DATE);
if 1 < i < DURATION and DAY(DATE_LAG) ne EVENT_DAY_OF_MONTH then DATE = mdy(month(intnx('month',START_DT,i-1,'E')),EVENT_DAY_OF_MONTH,year(intnx('month',START_DT,i-1,'E')));
if 1 < i < DURATION and DAY(DATE_LAG) = EVENT_DAY_OF_MONTH then DATE = intnx('month',START_DT,i-1,'E');
if i = DURATION then DATE = END_DT;

output;
end;

run;

run;

Sample expected result i'm looking for:

2018-04-19
2018-04-30
2018-05-06
2018-05-31
2018-06-06
2018-06-30
2018-07-06
2018-07-31
2018-08-06
2018-08-31
2018-09-06
2018-09-30
2018-10-06
2018-10-31
2018-11-06
2018-11-30
2018-12-06
2018-12-31
2019-01-05

[Tom]

Just use two calls to INTNX and two output statements.

data want;
  START_DT = '19APR2018'd;
  EVENT_DAY_OF_MONTH = 6;
  END_DT = '05JAN2019'd;
  DURATION = intck('month',START_DT,END_DT)*2;
  do offset = 0 to intck('month',start_dt,end_dt);
    date=intnx('month',start_dt,offset) + event_day_of_month -1 ;
    output;
    date=intnx('month',date,0,'e');
    output;
  end;
  format START_DT END_DT DATE date9.;
run;

[Shmuel · Accepted solution]

I don't see a SET or INFILE statement, nor OUTPUT prior to using the LAG()  function.

In a loop you should save the previous value by assigning it to a separate variable. TRY:

data want;

format START_DT END_DT DATE DATE_LAG date9.;

START_DT = '19APR2018'd;
EVENT_DAY_OF_MONTH = 6;
END_DT = '05JAN2019'd;
DURATION = intck('month',START_DT,END_DT)*2;

do i=0 to DURATION by 1;
	if i = 0 then DATE = START_DT;
	DATE_LAG = date; /* line inserted */
	if i = 1 then DATE = intnx('month',START_DT,i-1,'E');
	/* DATE_LAG = LAG(DATE); line canceled */
	if 1 < i < DURATION and DAY(DATE_LAG) ne EVENT_DAY_OF_MONTH then DATE = mdy(month(intnx('month',START_DT,i-1,'E')),EVENT_DAY_OF_MONTH,year(intnx('month',START_DT,i-1,'E')));
	if 1 < i < DURATION and DAY(DATE_LAG) = EVENT_DAY_OF_MONTH then DATE = intnx('month',START_DT,i-1,'E');
	if i = DURATION then DATE = END_DT;

	output;
end;

run;

[andreas_lds]

First attempt, not sure wheter this will always give the expected result:

data want(keep=date);
	start_date = '19APR2018'd;
	event_day_of_month = 6;
	end_date = '05JAN2019'd;
	
	format date yymmddd10.;
	date = start_date;
	
	do while (date <= end_date);
		output;
		if day(date) < 28 then do;
			date = intnx('month', date, 0, 'end');
		end;
		else do;
			date = intnx('month', date, 1, 'beginning');
			date = intnx('day', date, 5);
		end;
	end;
	
	date = end_date;
	output;
run;

[mkeintz]

You don't really need the DURATION value if you use a "while (date<end_dt)" condition in the do loop:

data want;
  format START_DT END_DT DATE date9.;
  START_DT = '19APR2018'd;
  EVENT_DAY_OF_MONTH = 6;
  END_DT = '05JAN2019'd;
  /** don't need: DURATION = intck('month',START_DT,END_DT)*2; */

  date=start_dt; output;
  do  while (date<end_dt);
    date=intnx('month',date,0,'E'); if date<end_dt then output;
	date=min(end_dt,date+event_day_of_month); output;
  end;

run;

Edited note:  The other thing this program avoids is the MDY function.  Since underlying date values are just the number of days after 01jan1960 (or before in the case of negative values), you merely need to add EVENT_DAY_OF_MONTH to the date value representing the end of the previous month.    The only exception would be if EVENT_DAY_OF_MONTH is greater than the day-of-month in END_DT.  Hence the MIN function.

Second editted note:  I ran a test with END_DT=15JAN2019 and EVENT_DAY_OF_MONTH=6.  It generated '06jan2019','31'jan2019', followed by the '15JAN2019'.  Assuming the op would want to drop the 31jan2019, I corrected the code.  You can see the new "if date<end_dt then" preceding one of the output statements.

@_Zack_ :  If I misinterpreted your expectation for END_DT=15JAN2019 and EVENT_DAY_OF_MONTH=6, please state what you would want.