DATA Step, Macro, Functions and more

Reverse engineer dates

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Reverse engineer dates

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?


Accepted Solutions
Solution
‎02-22-2017 10:31 AM
Super User
Posts: 6,938

Re: Reverse engineer dates

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎02-22-2017 10:31 AM
Super User
Posts: 6,938

Re: Reverse engineer dates

[ Edited ]

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 36

Re: Reverse engineer dates

Thank you very much! This is very helpfull.

Super User
Posts: 6,938

Re: Reverse engineer dates


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 75

Re: Reverse engineer dates

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;
Contributor
Posts: 36

Re: Reverse engineer dates

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!

Valued Guide
Posts: 797

Re: Reverse engineer dates

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;
Contributor
Posts: 36

Re: Reverse engineer dates

Thank you for your time and effort.

 

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 203 views
  • 1 like
  • 4 in conversation