How do i transform data into these? 2 different scenarios here

StickyRoll — Tue, 25 Mar 2025 14:03:23 GMT

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.

Re: How do i transform data into these? 2 different scenarios here

ChrisNZ — Sat, 29 Mar 2025 04:00:47 GMT

It looks like people have been overwhelmed by the wall of text, and have no time to spend deciphering your complex explanations.

Try simplifying what you want to achieve to increase uptake.

topic How do i transform data into these? 2 different scenarios here in SAS Programming

How do i transform data into these? 2 different scenarios here

Re: How do i transform data into these? 2 different scenarios here