BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Matthijs
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

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

Matthijs
Obsidian | Level 7

Thank you very much! This is very helpfull.

Kurt_Bremser
Super User

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

nehalsanghvi
Pyrite | Level 9

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;
Matthijs
Obsidian | Level 7

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!

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Matthijs
Obsidian | Level 7

Thank you for your time and effort.

 

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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