Hello guys. I have a difficult task that needs help with.
So I am working on some contracts data with Policy as the key.
1 Police_ID can have multiple endorsement (AKA Version).
For example:
POLICE_ID END_ID POLICE_EFFECTIVE_DATE POLICE_EXPIRY_DATEEND_SER_EFFECTIVE_DATE END_SER_END_DATE
A 0 01Jan2024 31Dec2024 01Jan2024 05Mar2024
A 1 01Jan2024 31Dec2024 06Mar2024 31Dec2024
As we can see, a single POLICE_ID (Policy number) can have 2 END_ID (endorsement or we call it version) with different endorsement date (END_EFFDATE and END_EXPDATE).
Here is the task that i have been cracking my head. The data is not in the desired form at the moment and i am supposed to transform the data into that format as above.
This is what I got:
Below sample is all for a single POLICE_ID. In reality, I have more than 100,000 of POLICE_ID.
Scenario A (Extended Period)
| Originally | | | | |
| END_ID | END_SER_EFFECTIVE_DATE | END_SER_END_DATE | POLICE_EFFECTIVE_DATE | POLICE_EXPIRY_DATE |
| 0 | 1/1/2024 | 31/12/2024 | 1/1/2024 | 31/12/2024 |
| 1 | 1/2/2024 | 31/12/2024 | 1/1/2024 | 31/12/2024 |
| 2 | 1/1/2025 | 31/1/2025 | 1/1/2024 | 31/12/2024 |
It should be transformed into:
| END_ID | END_SER_EFFECTIVE_DATE | END_SER_END_DATE | POLICE_EFFECTIVE_DATE | POLICE_EXPIRY_DATE |
| 0 | 1/1/2024 | 31/1/2024 | 1/2/2024 | 31/1/2025 |
| 1 | 1/2/2024 | 31/12/2024 | 1/2/2024 | 31/1/2025 |
| 2 | 1/1/2025 | 31/1/2025 | | |
In the end result, for END_ID = 0, the value of "END_SER_END_DATE" is populated from "END_SER_EFFECTIVE_DATE"-1 in END_ID = 1. The logic for this is because there shouldn't be any date of "END_SER_XXX_DATE" overlap with the next line of "END_ID".
Moreover, since this is an extended scenario, the POLICE_EFFECTIVE_DATE and POLICE_EXPIRY_DATE is now changed. For POLICE_EFFECTIVE_DATE, it will be based on the new POLICE_EXPIRY_DATE - 1 year.
Data step to produce Scenario A sample data (original):
data insurance_data;
infile datalines dlm=',' dsd;
format END_SER_EFFECTIVE_DATE END_SER_END_DATE POLICE_EFFECTIVE_DATE POLICE_EXPIRY_DATE ddmmyy10.;
input POLICE_ID $ END_ID END_SER_EFFECTIVE_DATE :ddmmyy10. END_SER_END_DATE :ddmmyy10.
POLICE_EFFECTIVE_DATE :ddmmyy10. POLICE_EXPIRY_DATE :ddmmyy10.;
datalines;
AA,0,01/01/2024,31/12/2024,01/01/2024,31/12/2024
AA,1,01/02/2024,31/12/2024,01/01/2024,31/12/2024
AA,2,01/01/2025,31/01/2025,01/01/2024,31/12/2024
;
run;
Scenario B (Shifted Period)
| Originally | | | | |
| END_ID | END_SER_EFFECTIVE_DATE | END_SER_END_DATE | POLICE_EFFECTIVE_DATE | POLICE_EXPIRY_DATE |
| 0 | 1/1/2024 | 31/12/2024 | 1/1/2024 | 31/12/2024 |
| 1 | 15/2/2024 | 31/12/2024 | 1/1/2024 | 31/12/2024 |
| 2 | 1/2/2024 | 31/1/2025 | 1/1/2024 | 31/12/2024 |
It should be transformed into:
| END_ID | END_SER_EFFECTIVE_DATE | END_SER_END_DATE | POLICE_EFFECTIVE_DATE | POLICE_EXPIRY_DATE |
| 0 | 1/2/2024 | 14/2/2024 | 1/2/2024 | 31/1/2025 |
| 1 | 15/2/2024 | 31/1/2025 | 1/2/2024 | 31/1/2025 |
For this scenario, END_SER_EFFECTIVE_DATE of the final row of END_ID will become the new END_SER_EFFECTIVE_DATE for the first nearest date to 1/2/2024 and new POLICE_EFFECTIVE_DATE for the same POLICE ID of all END_ID.
Like wise, the END_SER_END_DATE in the final END_ID will become the new END_SER_END_DATE for the END_ID before the final END_ID (which in this example is END_ID = 1) and new POLICE_EXPIRY_DATE for the same POLICE ID of all END_ID.
Lastly, remove the final END_ID.
Data step to produce Scenario B sample data (original):
data insurance_data;
infile datalines dlm=',' dsd;
format END_SER_EFFECTIVE_DATE END_SER_END_DATE POLICE_EFFECTIVE_DATE POLICE_EXPIRY_DATE ddmmyy10.;
input POLICE_ID $ END_ID END_SER_EFFECTIVE_DATE :ddmmyy10. END_SER_END_DATE :ddmmyy10.
POLICE_EFFECTIVE_DATE :ddmmyy10. POLICE_EXPIRY_DATE :ddmmyy10.;
datalines;
AA,0,01/01/2024,31/12/2024,01/01/2024,31/12/2024
AA,1,15/02/2024,31/12/2024,01/01/2024,31/12/2024
AA,2,01/02/2024,31/01/2025,01/01/2024,31/12/2024
;
run;
I hope both scenarios are clear. Feel free to ask anything as I understand the logic may not be simple and that more illustrations can be provided if you need more info.
