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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.