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.
... View more