topic Re: Reverse engineer dates in SAS Programming

Reverse engineer dates

Matthijs — Wed, 22 Feb 2017 15:13:49 GMT

Hi all,

So here's a question that's been bugging me for a while.

Let's say we have a script (in our backoffice application) wich determines when to renew a policy. It has to do so on each day, a month in advance.

Examples:

on 1jan2017 it renews the policy's with renewaldate 1feb2017

on 15mar2017 it renews the policy's with renewaldate 15apr2017

BUT

on 28feb2017 --> renewaldate 28mar2017

on 1mar2017 --> renewaldates 29mar2017, 30mar2017, 31mar2017 and 1apr2017

For our planning I want to reverse engineer this in a simple and effective way. I have the policy's and the renewaldates in 2017 and 2018. I can't figure out how to deal with the exceptions.

First I thought of INTNX and 'month' -1 and 'same day'. But that doesn't work. 29mar2017 turns into 28feb2017. I need it to be 1mar2017.

Is there a way to reverse engineer this?

Re: Reverse engineer dates

Kurt_Bremser — Wed, 22 Feb 2017 15:28:53 GMT

Try this:

data renewals;
input day_date yymmdd10.;
format day_date renewal yymmddd10.;
if day(day_date) = 1
then do renewal = intnx('month',intnx('month',day_date,-1,'end'),1,'same') + 1 to intnx('month',day_date,1,'same');
  output;
end;
else do;
  renewal = intnx('month',day_date,1,'same');
  output;
end;
cards;
2017-01-01
2017-03-15
2017-02-28
2017-03-01
2017-04-01
2017-05-01
run;

I added some additional "firsts" to illustrate the behaviour with 30 and 31-day months.

Edit: changed the if condition to day(day_date) = 1. Original version was too complicated (unnecessarily).

Re: Reverse engineer dates

Matthijs — Wed, 22 Feb 2017 15:32:40 GMT

Thank you very much! This is very helpfull.

Re: Reverse engineer dates

nehalsanghvi — Wed, 22 Feb 2017 15:34:05 GMT

Is this what you're looking for? If there is a rule for which days are always considered exceptions, then we could try programming that, but without that knowledge, I've hardcoded the exceptions:

data RenewalDates;
input RenewalDt date9.;
format RenewalDt date9.;
cards;
28mar2017
29mar2017
30mar2017
31mar2017
01apr2017
;
run;

data AlertDates;
set RenewalDates;
format AlertDt date9.;
if RenewalDt not in ('29mar2017'd,'30mar2017'd,'31mar2017'd) then do;
	AlertDt=intnx('month',RenewalDt,-1,'s');
end;
else do;
	AlertDt=intnx('month',RenewalDt,0,'b');
end;
run;

Re: Reverse engineer dates

mkeintz — Wed, 22 Feb 2017 15:35:23 GMT

I use the term detect_date and renewaldate, where (generally) renewaldate= 1 month after detect_date, which the adjustments you explained. Data set HAVE has a year of detect_date (01jan2001 to 31dec2001) and corresponding renewaldate's.

The data step creating WANT uses the series of renewaldates to generate detect_date_est, which matches the original detect_date:

data have (keep=renew_date detect_date);
  do detect_date='01feb2001'd to '31dec2001'd;
    base_renewaldate=intnx('month',detect_date,1,'same');
    do renew_date=coalesce(lag(base_renewaldate)+1,base_renewaldate) to base_renewaldate; output; end;
  end;
  format detect_date base_renewaldate renew_date date9.;
run;
	
data want;
  set have;
  detect_date_est=intnx('month',renew_date,-1,'same');
  if day(detect_date_est)^=day(renew_date) then detect_date_est=detect_date_est+1;
  format detect_date_est date9.;
run;

Re: Reverse engineer dates

Matthijs — Wed, 22 Feb 2017 15:47:11 GMT

Thank you for your time and effort.

In this solution I have to determine and 'name' the dates that are exceptions. In Kurts solution I don't have to do that. But again, thank you for taking the time!

Re: Reverse engineer dates

Matthijs — Wed, 22 Feb 2017 15:53:00 GMT

Thank you for your time and effort.

When I run your code it skips a couple off detects_dates --> 31mar2001 for instance.

Re: Reverse engineer dates

Kurt_Bremser — Wed, 22 Feb 2017 16:04:13 GMT

@Matthijs wrote:

Thank you very much! This is very helpfull.

I guess your original problem came from the fact that you asked yourself 'How do I deal with the last of the month?'

Instead I took the approach 'How do I deal with the first of a month', which is easier to detect and maybe handle.

It has often happened to me that approaching a problem from the "reverse side" led me to an easier solution. ie using last. instead of first. or vice-versa.