Difference between dates

Reply
SAS Employee
Posts: 20

Difference between dates

 

Hi,

 

There are the insurance policies with customer_id, policy_id, revision_id and policy start and end dates.

I want to calculate the day between the renewal of two policies for the same customer

 

Some policies may have revisions (added some extra coverage) with same policy_id and end_date.

 

Here is the sample data:

 

 Capture.JPG

 

For example for the policies 1247 and 1389, renewal day is: 365

For 1389 and 1543 it is: 0

 

 

 

Thank you very much!

Onur

 

Super User
Posts: 17,863

Re: Difference between dates

Use the lag function to obtain the previous value of the end date.

 

data want;
set have;
by customerID;

contract_end = lag1(end_date);

if first.customerID then do;
   renewal=.;
end;
else renewal = datepart(start_date)-datepart(contract_end);

run;
SAS Employee
Posts: 20

Re: Difference between dates

[ Edited ]

Hi Reeza,

 

Thank you for your reply.

The problem is many policies have revisions -as ı stated at example- with the same policy_id and end_date and different start_date (revision_date).

I want the date difference only between different policy_ids.

 

Do you have any idea?

 

Thank you!

Super User
Posts: 17,863

Re: Difference between dates

What do you want the output to look like? Do you keep the revisions in the file? If so, what should duration be.
SAS Employee
Posts: 20

Re: Difference between dates

I just want to choose the customers who's min 2 different policy_ids and the date between ex policy end date and new policy date are greater than 1 month
Ask a Question
Discussion stats
  • 4 replies
  • 366 views
  • 0 likes
  • 2 in conversation