Difference between dates

[dincoo]

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:

 

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

For 1389 and 1543 it is: 0

Thank you very much!

Onur

[Reeza]

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;

[dincoo]

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!

[Reeza]

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

[dincoo]

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