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?
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).
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).
Thank you very much! This is very helpfull.
@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.
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;
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!
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;
Thank you for your time and effort.
When I run your code it skips a couple off detects_dates --> 31mar2001 for instance.
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.
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.