topic Re: Insurance Exposure Over 12 month periods in SAS Programming

Insurance Exposure Over 12 month periods

tharan — Sat, 20 Feb 2021 08:56:04 GMT

Hello everyone! The coverage for an insurance product i'm dealing with could be paid in upfront from 1-12 months. For example, in the picture below, the first person had bought seven consecutive one month policies and the second guy has bought a 3 months policy followed by a 9 months policy (there could be gaps)(first column - ID, Second column gender(irrelevant), third column - start of coverage date, fourth column - end of coverage date).

Now I want to find someone who has paid for 12 months worth of coverage (regardless of how, could be 12 month in one go, 3+3+3+3 combination etc) and has proceeded with another 12 months worth of coverage. This is the solution I came up with.

Data workone.testingrenewal4;
Set workone.sorted2;

Format Inception_date Supposed_end date1 date2 date9.;

date1 = lag(Inception_date);
date2 = lag(Supposed_end);

By ic;

If first.ic then do;
Inception_date = Startdate;
Supposed_end = (INTNX('YEAR', startdate ,1, 'sameday')-1);
end;

else if date1<startdate<date2 then do;
Inception_date = date1;
Supposed_end = date2;
end;

else if startdate>=date2 then do;
Inception_date = Startdate;
Supposed_end = (INTNX('YEAR', startdate ,1, 'sameday')-1);
End;

Run;

essentially i want the codes to identify the first time a person has contributed and build a 12 month period using the startdate where the start of the 12 month coverage would =startdate (called Inception_date) and the end of the coverage would be 12 months from startdate (called supposed_end). If its not the first payment under the person then i want to observe whether the following payment was made within the 12 months period previously built. If yes, then I want the codes to assign the same Inception_date and supposed_end dates to the dataline. If the person had bought coverage after the pervious 12 months perios, then i want the codes to assign a new inception date based on the startdate of the newly purchased coverage. With that I'd be able to count the number of months in each built 12 month period.

The results are not coming out like how i want them and I'm kinda new to SAS programming, so would appreciate if anyone can help improve the codes. Thanks!

Re: Insurance Exposure Over 12 month periods

Shmuel — Sat, 20 Feb 2021 10:08:29 GMT

1) It seems to me you missed to RETAIN the lag(dates): Inception_date, Supposed_end

2) may I propose different approach:

- compute number of months per line:

months = intck('month', <end of coverage date>,<start of coverage date>);

- accumulate the computed months per ID.

- check whether the accumulated months >= 12 and last month is equal to

the supposed end date.

3) You mentioned there may be gaps, but you did not gave the rule how to relate

to the gap.

Re: Insurance Exposure Over 12 month periods

Kurt_Bremser — Thu, 25 Feb 2021 08:21:31 GMT

How do you want to deal with a situation where the end of a 12-month period falls right into an insured period?

E.g. 3 months, immediately followed by 12 months. Should the 12 months be split into 9 months, completing the first 12, and 3 months, the start of a (possible) new 12-month period?

Re: Insurance Exposure Over 12 month periods

tharan — Thu, 25 Feb 2021 01:44:23 GMT

Hi Kurt, your example is correct. By the looks of it seems like I'd have to split each payment by the coverage months before compiling them back into 12 month periods before trying to identify possible '12months renewals' thanks for pointing it out. Will have to bring this back to the table.