How to convert data from underwriting year basis to calendar year basis?

[Ad_1993]

Dear All,

I would like to seek your assistance in guiding me on how to convert/transform the data from underwriting year to calendar year as shown below:

Underwriting Year basis:

What I have now is splitting the data up to 2 years of exposure with the following SAS code:

DATA CALYR_SPLIT (DROP=Z_BEGPRD Z_ENDPRD BEGPRD1 BEGPRD2 BEGPRD3 
		ENDPRD1 ENDPRD2 ENDPRD3 VARX DURATION1 DURATION2 DURATION3 DURATIOND 
		PORTION1 PORTION2 PORTION3 YEARD);

	SET ININ.TEST;

	FORMAT BEGPRDX ENDPRDX DDMMYY10.;
	FORMAT ADJ_AMOUNT ADJ_EXPOSURE 10.2;
		
	FORMAT Z_BEGPRD Z_ENDPRD DDMMYY10.;
	FORMAT PORTION1 PORTION2 PORTION3 PERCENT10.2;

	YEARD = YEAR(END_DATE) - YEAR(START_DATE);

/*********************	FOR 1-YEAR COVERAGE*************************************/
	IF YEARD = 1 THEN DO;	
		Z_BEGPRD = INTNX('YEAR', START_DATE, 0, 'END');
		Z_ENDPRD = INTNX('YEAR', END_DATE, 0, 'BEGINNING');

		DURATION1 = INTCK('DAY', START_DATE, Z_ENDPRD);
		DURATION2 = INTCK('DAY', Z_BEGPRD, END_DATE);
		DURATION3 = .;
		DURATIOND = DURATION1 + DURATION2;

	/******	USED IN ARRAYS********/
		BEGPRD1 = START_DATE;
		BEGPRD2 = INTNX('YEAR', END_DATE, 0, 'BEGINNING');
		ENDPRD1 = INTNX('YEAR', START_DATE, 0, 'END');
		ENDPRD2 = END_DATE;
		PORTION1 = DURATION1/DURATIOND;
		PORTION2 = DURATION2/DURATIOND;

		VARX = 1;
		ADJ_AMOUNT = 0;
		ADJ_EXPOSURE = 0;
			
		ARRAY PORT1 [2] PORTION1 PORTION2;
		ARRAY BEG1 [*] BEGPRD1 BEGPRD2;
		ARRAY END1 [*] ENDPRD1 ENDPRD2;

		DO UNTIL (VARX GT 2);
			BEGPRDX = BEG1[VARX];
			ENDPRDX = END1[VARX];
			ADJ_AMOUNT = AMOUNT*PORT1[VARX];
			ADJ_EXPOSURE = EXPOSURE*PORT1[VARX];
			VARX = VARX + 1;
		OUTPUT;
		END;
	END;

/*********************	FOR COVERAGES THAT ARE MORE THAN 1 YEAR LONG*********************/
	ELSE IF YEARD = 2 THEN DO;
		IF (MOD((YEAR(END_DATE) - 1),4)) = 0 THEN DURATION2 = 366;
		ELSE DURATION2 = 365; 
			
		Z_BEGPRD = INTNX('YEAR', START_DATE, 1, 'END');
		Z_ENDPRD = INTNX('YEAR', END_DATE, -1, 'BEGINNING');

		DURATION1 = INTCK('DAY', START_DATE, Z_ENDPRD);
		DURATION3 = INTCK('DAY', Z_BEGPRD, END_DATE);
		DURATIOND = DURATION1 + DURATION2 + DURATION3;
			
		BEGPRD1 = START_DATE;
		BEGPRD2 = INTNX('YEAR', END_DATE, -1, 'BEGINNING');
		BEGPRD3 = INTNX('YEAR', END_DATE, 0, 'BEGINNING');

		ENDPRD1 = INTNX('YEAR', START_DATE, 0, 'END');
		ENDPRD2 = INTNX('YEAR', END_DATE, -1, 'END');
		ENDPRD3 = END_DATE;

		PORTION1 = DURATION1/DURATIOND;
		PORTION2 = DURATION2/DURATIOND;
		PORTION3 = DURATION3/DURATIOND;

		VARX = 1;
		ADJ_AMOUNT = 0;
		ADJ_EXPOSURE = 0;
			
		ARRAY PORT2 [*] PORTION1 PORTION2 PORTION3;
		ARRAY BEG2 [*] BEGPRD1 BEGPRD2 BEGPRD3;
		ARRAY END2 [*] ENDPRD1 ENDPRD2 ENDPRD3;

		DO UNTIL (VARX GT 3);
			BEGPRDX = BEG2[VARX];
			ENDPRDX = END2[VARX];
			ADJ_AMOUNT = AMOUNT*PORT2[VARX];
			ADJ_EXPOSURE = EXPOSURE*PORT2[VARX];
			VARX = VARX + 1;
			OUTPUT;
		END;
	END;

/*********************	FOR COVERAGE 1 YEAR (1 JAN YYYY TO 31 DEC YYYY)*****************/
	ELSE DO;		
		BEGPRDX = START_DATE;
		ENDPRDX = END_DATE;
		ADJ_AMOUNT = AMOUNT;
		ADJ_EXPOSURE = EXPOSURE;
		OUTPUT;
	END;	
RUN;

and my current output is this:

Please advise on how to split for the observations with > 2 years of exposure as well.

Thank you.

[PeterClemmensen]

What does your desired result look like given the input data ?

[Ad_1993]

Hi Peter,

From my current output attached beneath the SAS code I shared, you will see that the ID with 2 years of exposure is successfully split into calendar year basis. I would like the same result like this for the ID with 6 years and 4 years of exposure respectively.

Perhaps something like this (The following sample output was computed in Excel and imported into SAS for your reference):

[Shmuel]

Is  that what you want:

data want;
 set have;
     save_end_date = end_date;
	 start_year = year(start_date);
	 end_year = year(end_date);
	 drop save_end_date start_year end_year;
	 
     if end_year = start_year then output;
	 else do;
	    end_year = start_year;
     	do util (end_year = year(save_end_date));
	    if end_year = year(save_end_date)
		   then end_date = save_end_date;
	       else end_date = mdy(12,31,start_year +1);
		end_year = end_year +1;
		output;
	    start_date = mdy(01,01,end_year);
		start_year = year(start_date);
	 end;
run;

[Kurt_Bremser · Accepted solution]

You are overcomplicating things, all you need is a DO loop and some cleanup for the final year:

data have;
input id $ (start_date end_date) (:date9.) exposure amount;
format start_date end_date yymmdd10.;
datalines;
1111 01jul2015 20jun2021 6 5000
1112 01jan2017 31dec2020 4 3500
1113 01mar2018 29feb2020 2 7000
;

data want;
set have;
format
  begprdx endprdx yymmdd10.
  adj_amount 20.2
  adj_exposure 5.2
;
endprdx = min(end_date,intnx('year',start_date,0,'e'));
do while (endprdx lt end_date);
  begprdx = max(start_date,intnx('year',endprdx,0,'b'));
  adj_exposure = ((endprdx - begprdx) / 365);
  adj_amount = (amount / (end_date - start_date + 1)) * (endprdx - begprdx + 1);
  output;
  endprdx = min(end_date,intnx('year',endprdx,1,'e'));
end;
begprdx = max(start_date,intnx('year',end_date,0,'b'));
adj_exposure = round(endprdx - begprdx) / 365;
adj_amount = (amount / (end_date - start_date + 1)) * (endprdx - begprdx + 1);
output;
run;

Please note that data has to be presented in a data step with datalines; we can't write SAS programs for pictures.

Please provide your example data in this form in the future, see it as a basic courtesy for those who are intended to help you.

And you'll get answers quicker, because we do not have to waste time writing the data step ourselves.

[Ad_1993]

Sincere apologies on the missing datalines from my code, I will take note on this. And thank you very much for your time and guidance, the code works fine now.