topic Re: Generate date based on previous date in do loop in SAS Programming

Generate date based on previous date in do loop

_Zack_ — Sun, 13 Dec 2020 16:08:09 GMT

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

Re: Generate date based on previous date in do loop

Tom — Sun, 13 Dec 2020 16:41:09 GMT

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;

Re: Generate date based on previous date in do loop

Shmuel — Sun, 13 Dec 2020 16:49:53 GMT

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;

Re: Generate date based on previous date in do loop

andreas_lds — Sun, 13 Dec 2020 16:50:58 GMT

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;

Re: Generate date based on previous date in do loop

mkeintz — Sun, 13 Dec 2020 19:29:03 GMT

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.