F_CREATE_CLAIM_METRICS_TABLE.sas

/* dataset work.step3_clean was created by the import process */
/* DATA work.STEP3_clean; */
/* 	SET WORK.STEP2; */
/* 	WHERE DELETE_THIS='N'; */
/*  */
/* 	YEAR_TEXT=PUT(AS_OF_DTE,YEAR4.); */
/* 	VEH_YR=VEHICLE_YEAR + 6; */
/* 	DT_AS_OF=intnx('year', AS_OF_DTE, 6, 'same'); */
/* 	FORMAT DT_AS_OF MMDDYY10.; */
/* 	DROP VEHICLE_YEAR AS_OF_DTE HOUSEHOLD_KEY REGION; */
/* RUN; */

proc sort data=work.STEP3_clean;
	by POLICY_ID DT_AS_OF;
RUN;

DATA WORK.STEP4;
	SET work.STEP3_clean;
	RETAIN DT_FNOL DT_RSV_OPEN DT_OF_LOSS COVERAGE JOIN_ID;
	BY POLICY_ID DT_AS_OF;

	LENGTH COVERAGE $15.;
	IF FIRST.POLICY_ID THEN
		DO;
			JOIN_ID=SUM(1,0,JOIN_ID);
			call streaminit(123);
			Max=28;
			u=rand("Uniform");
			IF u < .30 THEN COVERAGE = 'LIABILITY';
			ELSE IF u < .60 THEN COVERAGE = 'COLLISION';
			ELSE IF u < .90 THEN COVERAGE = 'COMPREHENSIVE';
			ELSE COVERAGE ='UNINSURED';

			IF UPCASE(POLICY_TYPE)='STND' THEN COVERAGE = 'LIABILITY';

			/* decimal values in (0,1)    */
			n=floor((1+Max)*u) +1;

			/* integer values in 0..Max   */
			DT_FNOL=INTNX('day', DT_AS_OF, -n, 'same');

			/* DT_FNOL=DT_AS_OF; */
			DT_RSV_OPEN=DT_AS_OF;
		END;

	IF FIRST.POLICY_ID THEN
		DO;
			call streaminit(834131);
			Max2=428;
			u=rand("Uniform");

			/* decimal values in (0,1)    */
			n=floor((1+Max2)*u) +1;

			/* integer values in 0..Max   */
			DT_OF_LOSS=INTNX('day', DT_FNOL, -n, 'same');
		END;
	FORMAT DT: MMDDYY10.;
	
	YEAR_FNOL=DT_FNOL;
	YEAR_OF_LOSS=DT_OF_LOSS;
	YEAR_AS_OF=DT_AS_OF;
	YEAR_RSV_OPEN=DT_RSV_OPEN;

	
	FORMAT YEAR_: YEAR4.;

DROP MAX2 MAX u n;
/* 	KEEP POLICY_ID DT: YEAR_FNOL; */
RUN;

%MACRO CREATE_MY_FILE;
	DATA WORK.TEMP1;
		SET %DO I=1 %TO 104;
			work.PRM_LSS_FNL(RENAME=(ADJUSTER=OLD_ADJUSTER)) %END;
		;
		WHERE RSV_AUTO > 0;

if office in ('Corrado Corners','Council House','Dahlgren') then office='Lakewood';
if office in ('Emerado','Evans','Grant City') then office='Springfield';
if office in ('Milton','Newburg','Norbourne Estates','Oakwood') then office='Gotham';
if office in ('Rockwell','Rolling Hills','Sedgwick','Wilmington','Windermere') then office='Home';

LENGTH ADJUSTER $11.;
ADJUSTER=TRIM(OLD_ADJUSTER);

LENGTH TEST2 8;

test=substr(adjuster,9,3);
test2=test * 1;


IF TEST2 < 3 THEN ADJUSTER = CATS('ADJUSTER_','3');
ELSE IF TEST2 < 5 THEN ADJUSTER = CATS('ADJUSTER_','5');
ELSE IF TEST2 < 7 THEN ADJUSTER = CATS('ADJUSTER_','7');
ELSE IF TEST2 < 9 THEN ADJUSTER = CATS('ADJUSTER_','9');
ELSE IF TEST2 < 11 THEN ADJUSTER = CATS('ADJUSTER_','11');
ELSE IF TEST2 < 13 THEN ADJUSTER = CATS('ADJUSTER_','13');
ELSE IF TEST2 < 15 THEN ADJUSTER = CATS('ADJUSTER_','15');
ELSE IF TEST2 < 17 THEN ADJUSTER = CATS('ADJUSTER_','17');
ELSE IF TEST2 < 19 THEN ADJUSTER = CATS('ADJUSTER_','19');
ELSE IF TEST2 < 21 THEN ADJUSTER = CATS('ADJUSTER_','21');
ELSE IF TEST2 < 23 THEN ADJUSTER = CATS('ADJUSTER_','23');
ELSE IF TEST2 < 25 THEN ADJUSTER = CATS('ADJUSTER_','25');
ELSE IF TEST2 < 27 THEN ADJUSTER = CATS('ADJUSTER_','27');
ELSE IF TEST2 < 29 THEN ADJUSTER = CATS('ADJUSTER_','29');
ELSE IF TEST2 < 31 THEN ADJUSTER = CATS('ADJUSTER_','31');
ELSE IF TEST2 < 33 THEN ADJUSTER = CATS('ADJUSTER_','33');
ELSE IF TEST2 < 35 THEN ADJUSTER = CATS('ADJUSTER_','35');
ELSE IF TEST2 < 37 THEN ADJUSTER = CATS('ADJUSTER_','37');
ELSE IF TEST2 < 39 THEN ADJUSTER = CATS('ADJUSTER_','39');
ELSE IF TEST2 < 41 THEN ADJUSTER = CATS('ADJUSTER_','41');
ELSE IF TEST2 < 43 THEN ADJUSTER = CATS('ADJUSTER_','43');
ELSE IF TEST2 < 45 THEN ADJUSTER = CATS('ADJUSTER_','45');
ELSE IF TEST2 < 47 THEN ADJUSTER = CATS('ADJUSTER_','47');
ELSE IF TEST2 < 49 THEN ADJUSTER = CATS('ADJUSTER_','49');
ELSE IF TEST2 < 51 THEN ADJUSTER = CATS('ADJUSTER_','51');
ELSE IF TEST2 < 53 THEN ADJUSTER = CATS('ADJUSTER_','53');
ELSE IF TEST2 < 55 THEN ADJUSTER = CATS('ADJUSTER_','55');
ELSE IF TEST2 < 57 THEN ADJUSTER = CATS('ADJUSTER_','57');
ELSE IF TEST2 < 59 THEN ADJUSTER = CATS('ADJUSTER_','59');
ELSE IF TEST2 < 61 THEN ADJUSTER = CATS('ADJUSTER_','61');
ELSE IF TEST2 < 63 THEN ADJUSTER = CATS('ADJUSTER_','63');
ELSE IF TEST2 < 65 THEN ADJUSTER = CATS('ADJUSTER_','65');
ELSE IF TEST2 < 67 THEN ADJUSTER = CATS('ADJUSTER_','67');
ELSE IF TEST2 < 69 THEN ADJUSTER = CATS('ADJUSTER_','69');
ELSE IF TEST2 < 71 THEN ADJUSTER = CATS('ADJUSTER_','71');
ELSE IF TEST2 < 73 THEN ADJUSTER = CATS('ADJUSTER_','73');
ELSE IF TEST2 < 75 THEN ADJUSTER = CATS('ADJUSTER_','75');
ELSE IF TEST2 < 77 THEN ADJUSTER = CATS('ADJUSTER_','77');
ELSE IF TEST2 < 79 THEN ADJUSTER = CATS('ADJUSTER_','79');
ELSE IF TEST2 < 81 THEN ADJUSTER = CATS('ADJUSTER_','81');
ELSE IF TEST2 < 83 THEN ADJUSTER = CATS('ADJUSTER_','83');
ELSE IF TEST2 < 85 THEN ADJUSTER = CATS('ADJUSTER_','85');
ELSE IF TEST2 < 87 THEN ADJUSTER = CATS('ADJUSTER_','87');
ELSE IF TEST2 < 89 THEN ADJUSTER = CATS('ADJUSTER_','89');
ELSE IF TEST2 < 91 THEN ADJUSTER = CATS('ADJUSTER_','91');
ELSE IF TEST2 < 93 THEN ADJUSTER = CATS('ADJUSTER_','93');
ELSE IF TEST2 < 95 THEN ADJUSTER = CATS('ADJUSTER_','95');
ELSE IF TEST2 < 97 THEN ADJUSTER = CATS('ADJUSTER_','97');
ELSE IF TEST2 < 101 THEN ADJUSTER = CATS('ADJUSTER_','99');
ELSE;



		RENAME ID=TEMP_ID IDNAME=TEMP_IDNAME ID1=TEMP_ID1 REGION=TEMP_REGION 
			ADJUSTER=TEMP_ADJUSTER OFFICE=TEMP_OFFICE MVR_POINTS=TEMP_MVR_POINTS 
			RISK_SCORE=TEMP_RISK_SCORE;
		KEEP PAID_AUTO RSV_AUTO ID IDNAME ID1 REGION ADJUSTER OFFICE MVR_POINTS 
			RISK_SCORE;
	RUN;

	DATA READY_TO_FORCE_JOIN_1;
		SET WORK.TEMP1 (OBS=941903);
	RUN;


%MEND CREATE_MY_FILE;

%CREATE_MY_FILE;


/* BY MANAGING RECORDS AND VARIABLES IN PREVIOUS STEPS WE ARE ABLE TO SIMPLY SET TWO TABLES SIDE BY SIDE IN THE SET STATEMENT BELOW */

DATA TESTJOIN;
SET 
WORK.STEP4;
SET
WORK.READY_TO_FORCE_JOIN_1;


RUN;

proc sort data=work.TESTJOIN;
	by POLICY_ID DT_AS_OF;
RUN;

DATA WORK.TESTJOIN_B;
	SET TESTJOIN;
	BY POLICY_ID DT_AS_OF;
	RETAIN OPEN_MONTHS;
	CLAIM_COUNTS=0;
	NEW_CLAIM=0;	 
	NEW_CLAIM_FLAG='N';	

	IF FIRST.POLICY_ID THEN
		DO;
		NEW_CLAIM=1;
		NEW_CLAIM_FLAG='Y';
		CLAIM_COUNTS=1;
		OPEN_MONTHS=0;
	END;
	OPEN_MONTHS=SUM(0,OPEN_MONTHS,1);
RUN;

proc sort data=work.TESTJOIN_B;
by  POLICY_ID DESCENDING DT_AS_OF ;
RUN;


DATA TESTJOIN2;
	SET WORK.TESTJOIN_B;
	by POLICY_ID DESCENDING DT_AS_OF;
	RETAIN DT_CLM_CLOSED ID IDNAME ID1 REGION ADJUSTER OFFICE MVR_POINTS 
		RISK_SCORE;
	
	LENGTH CLOSED_CLAIM_FLAG $1.;
	CLOSED_CLAIM_FLAG='N';
	LENGTH KEY_RECORD $1.;
	KEY_RECORD='N';
	
	IF (FIRST.POLICY_ID AND CLOSE_THIS='Y' )THEN
		DO;
			CLOSED_CLAIM_FLAG='Y';
			DT_CLM_CLOSED=DT_AS_OF;
			RSV_AUTO=0;
			TIME_TO_CLOSE=DT_CLM_CLOSED-DT_RSV_OPEN;
		END;

	IF FIRST.POLICY_ID THEN
		DO;
			KEY_RECORD='Y';
			ID=TEMP_ID;
			IDNAME=TEMP_IDNAME;
			ID1=TEMP_ID1;
			REGION=TEMP_REGION;
			ADJUSTER=TEMP_ADJUSTER;
			OFFICE=TEMP_OFFICE;
			MVR_POINTS=TEMP_MVR_POINTS;
			RISK_SCORE=TEMP_RISK_SCORE;
		END;
	CLOSED_CLAIMS=0;

	IF RSV_AUTO=0 THEN
		CLOSED_CLAIMS=1;
	DROP TEMP_:;
	FORMAT DT: MMDDYY10.;

IF CLOSED_CLAIMS =1 OR CLAIM_COUNTS=1 THEN KEY_RECORD='Y';
DROP ID ID1 IDNAME REGION;
RUN;


PROC SQL;
CREATE TABLE work.TESTJOIN3 AS
SELECT A.*,B.GEOID,B.GEOID10,B.STATE,B.STATECODE,B.COUNTY_NAME FROM TESTJOIN2 A INNER JOIN work.SCALE_ZIP2 B ON 
A.JOIN_ID=B.JOIN_TO_POL;
QUIT;



PROC DELETE DATA=PUBLIC.CLAIM_METRICS2021;

DATA PUBLIC.CLAIM_METRICS2021(PROMOTE=YES);
SET Work.TESTJOIN3;

YEAR_TEXT4_RSV=PUT(DT_FNOL,YEAR4.);

IF YEAR_TEXT4_RSV = '2015' THEN PAID_AUTO=PAID_AUTO*.54;

IF YEAR_TEXT4_RSV='2016' THEN DO;
PAID_AUTO=PAID_AUTO * .69;
RSV_AUTO=RSV_AUTO *.90;
END;

IF YEAR_TEXT4_RSV='2017' THEN DO;
PAID_AUTO=PAID_AUTO * .41;
RSV_AUTO=RSV_AUTO *.88;
END;

IF YEAR_TEXT4_RSV='2018' THEN DO;
PAID_AUTO=PAID_AUTO * .49;
RSV_AUTO=RSV_AUTO * 1.09;
END;

IF YEAR_TEXT4_RSV='2019' THEN DO;
PAID_AUTO=PAID_AUTO * .89;
RSV_AUTO=RSV_AUTO * 1.21;
END;

label
ADJUSTER = 'Adjuster'
CLAIM_COUNTS = 'Claim Counts'
CLOSED_CLAIMS = 'Closed Claims'
CLOSED_CLAIM_FLAG = 'Closed Flag'
COVERAGE = 'Coverage'
DT_AS_OF = 'As of Date'
DT_CLM_CLOSED = 'Date Closed'
DT_FNOL = 'First Notice of Loss'
DT_OF_LOSS = 'Date of Loss'
DT_RSV_OPEN = 'Date Reserve Opened'
MVR_POINTS = 'Driver Points Assessed'
NEW_CLAIM = 'New Claims'
NEW_CLAIM_FLAG = 'New Claim Flag'
OFFICE = 'Office'
OPEN_MONTHS = 'Months Claim Has Been Open'
PAID_AUTO = 'Paid Dollars'
RISK_SCORE = 'Triage Score'
RSV_AUTO = 'Reserve Dollars'
TIME_TO_CLOSE = 'Time To Close'
VEH_YR = 'Vehicle Year'
YEAR_AS_OF = 'As of Year'
YEAR_FNOL = 'First Notice Year'
YEAR_OF_LOSS = 'Accident Year'
YEAR_RSV_OPEN = 'Reserved Open Year'
bod_inj_limit = 'Bodily Injury Limit'
claim_cnt_mid = 'Count of Medium Claims'
claim_cnt_minor = 'Count of Minor Claims'
claim_cnt_severe = 'Count of Severe Claims'
collision_deductible = 'Collision Deductible'
comp_deductible = 'Comprehensive Deductible'
credit_score = 'Credit Score'
driver_age = 'Driver Age'
driver_marital = 'Marital Status of Driver'
drivers_cnt = 'Number of Drivers'
policy_ID = 'Claim Number'
policy_tenure_months = 'Customer Longevity'
policy_type = 'Policy Type'
premium_amt = 'Premium'
property_cov_ind = 'Property Coverage Flag'
Class_Description = 'Class Code Description'
umbrella_cov_ind = 'Umbrella Coverage Flag'
uninsured_limit = 'Uninsured Limit'
vehicle_age = 'Vehicle Age'
vehicle_base_price = 'Vehicle Base Price'
vehicle_class = 'Vehicle Class';

if UPCASE(ADJUSTER) = 'ADJUSTER_71' THEN
DO;
	RISK_SCORE=RISK_SCORE * 2.46;
	PAID_AUTO=PAID_AUTO * 2.47;
	RSV_AUTO=RSV_AUTO * 1.92;
END;

DROP  YEAR_TEXT4_RSV;
RUN;


/***********************   CLAIM_METRICS    ****************************************/
/* Save table in CAS */
proc casutil;
	save incaslib="PUBLIC" outcaslib="PUBLIC" casdata="CLAIM_METRICS2021" replace;
run;