/* PSTAT 130 Homework Winter 2020*/
/* By: */
/* Priscilla Lee */
/* Nivi Lakshminarayanan */
/* Deni Stoyanova */

/* TASK 1: COMPUTATION OF FUTURE COSTS */
/* Instructions 1-11:  */

/* 1 */
libname data "/home/u45009682/sasuser.v94/HOMEWORK";
proc contents data=data.ptf; run;

data work.ptf;
set data.ptf;
length age $8 default=8;
today = DATE();
days = today - birthdate;
agenum = floor(days/365);
if (agenum LE 21) then age = "-21";
if (agenum GT 21) and (agenum LE 35) then age = "21-35";
if (agenum GT 35) and (agenum LE 60) then age = "35-60";
if (agenum GT 60) then age = "+60";
drop days today agenum;
run;

/* test */
/* proc print data=ptf (obs=10); */
/* run; */


/* 2 */
proc import datafile="/home/u45009682/sasuser.v94/HOMEWORK/cars.csv"
out=work.cars
dbms=CSV replace;
getnames=yes;
datarow=2;
run;

data work.cars;
set work.cars;
informat hp:$15.;
length hp $10;
if (horsepower LE 150) then hp = "low";
if (horsepower GT 150) and (horsepower LE 300) then hp = "medium";
if (horsepower GT 300) then hp = "high";
run;



proc sort data=work.ptf;
by cars_id;
run;
proc sort data=work.cars;
by cars_id;
run;

data work.ptf;
merge work.cars work.ptf;
by cars_id;
run;

/* proc print data=ptf (obs=10); */
/* run; */

/* 3 */
proc import datafile="/home/u45009682/sasuser.v94/HOMEWORK/CA_ZIP_CODE.TXT"
out=work.CA_ZIP_CODE
dbms=dlm replace;
getnames=yes;
run;

/* proc print data=CA_ZIP_CODE (obs=10); */
/* run; */

data work.CA_ZIP_CODE;
set work.CA_ZIP_CODE;
informat density:$15.;
length density $10;
if (population LE 4000) then density = "low";
if (population GT 4000) and (population LE 30000) then density = "medium";
if (population GT 30000) then density = "high";
run;

proc sort data=work.ptf;
by zip_code;
run;

proc sort data=work.CA_ZIP_CODE;
by zip_code;
run;

data work.ptf;
merge work.CA_ZIP_CODE work.ptf;
by zip_code;
run;
/* density hp and age r importante :) */

/* 4 */

libname data "/home/u45009682/sasuser.v94/HOMEWORK";
proc contents data=data.ptf; run;

data data.claims;
set data.claims;
run;

proc sort data=work.ptf;
by POLICYHOLDER_ID POLICY_STARTING_DATE;
run;

proc sort data=data.claims;
by POLICYHOLDER_ID POLICY_STARTING_DATE;
run;

data work.claims;
merge data.claims (IN=A) work.ptf (IN=B);
by POLICYHOLDER_ID POLICY_STARTING_DATE;
IF A;
run;


/* 5 */
data work.claims;
set work.claims;
informat year 4.;
year=year(policy_starting_date);
run;

proc sort data=work.claims;
by year;
run;

proc means data=work.claims noprint nway;
var CLAIMS_COST;
class year age hp density;
output out=work.claims_summary (drop = _TYPE_ _FREQ_)
mean(claims_cost) = cost
N(claims_cost) = nb_claims;
/* types policyholder_id*year; */
run;

/* TODO how to do the above? */

/* merge this with the work.ptf database  */

/* 6  USE SUM?*/
data work.ptf;
set work.ptf;
format year 4.;
year=year(policy_starting_date);
run;

proc sort data=work.ptf;
by year;
run;

proc means data=work.ptf noprint nway;
var policyholder_id;
class year age hp density;
output out=work.ptf_summary (drop = _TYPE_ _FREQ_)
N(policyholder_id) = nb;
run;

/* 7 */
proc sort data=work.claims_summary;
by age density hp year;
run;

proc sort data=work.ptf_summary;
by age density hp year;
run;

data work.summary; /* in=a use */
merge work.claims_summary work.ptf_summary;
by age density hp year;
run;

data work.summary;
set work.summary;
informat freq:6.3;
freq=nb_claims/nb;
run;

/* 8 */
proc sort data=work.summary;
by descending freq age hp density;
run;

proc sort data=work.summary out=work.freq nodupkey;
by age hp density;
run;

proc sort data=work.freq;
by year age hp density;
run;

proc print data=work.freq;
run;

data work.freq;
set work.freq;
keep age hp density freq;
run;


/* 9 */
proc sort data=work.summary;
by descending cost age hp density;

proc sort data=work.summary out=work.cost nodupkey;
by age hp density;
run;

proc sort data=work.cost;
by year age hp density;
run;


/* 10 */
proc sort data=work.cost;
by age density hp;
run;

proc sort data=work.freq;
by age density hp;
run;

data work.pp;
merge work.cost work.freq;
by age density hp;
run;

data work.pp;
set work.pp;
informat pp:15.9;
pp=freq*cost;
run;

/* 11 */
data work.pp;
set work.pp;
informat lp: 15.9;
lp = pp*(1+0.05);
run;

/* TASK 2: DEFINITION OF THE BEST INSURANCE PRICE STRATEGY */
/* Instructions 12-19 */

/* 12 */
data work.price;
set work.pp;
keep age density hp lp;
run;

data work.price;
set work.price;
informat pA: 15.9 pB:15.9 pC:15.9;
pA = lp*(1+0.05);
pB = lp*(1+0.10);
pC = lp*(1+0.15);
run;


/* 13 */
PROC IMPORT OUT=work.prospect
DATAFILE = "/home/u45009682/sasuser.v94/HOMEWORK/Prospect.csv"
DBMS=CSV
REPLACE;
GETNAMES=YES ;
DATAROW=2;
delimiter=",";
RUN;

data work.prospect;
set work.prospect;
length age $8 default=8;
today = DATE();
days = today - birthdate;
agenum = floor(days/365);
if (agenum LE 21) then age = "-21";
if (agenum GT 21) and (agenum LE 35) then age = "21-35";
if (agenum GT 35) and (agenum LE 60) then age = "35-60";
if (agenum GT 60) then age = "+60";
drop days today agenum;
run;

proc sort data=work.prospect;
by cars_id;
run;

proc sort data=work.cars;
by cars_id;
run;

data work.prospect;
merge work.cars work.prospect;
by cars_id;
run;

proc sort data=work.prospect;
by zip_code;
run;

proc sort data=work.CA_ZIP_CODE;
by zip_code;
run;

data work.prospect;
merge work.CA_ZIP_CODE work.prospect;
by zip_code;
run;


/* 14 */
proc sort data=work.prospect;
by age density hp;
run;

proc sort data=work.price;
by age density hp;
run;

data work.prospect;
merge work.prospect work.price;
by age density hp;
run;

/* 15 */
data work.prospect;
set work.prospect;
informat prob_a:15.9 prob_b:15.9 prob_c:15.9;
prob_a = 1/(1+exp((-0.1*(lp/pA))+(0.002*(pA-lp))));
prob_b = 1/(1+exp((-0.1*(lp/pB))+(0.002*(pB-lp))));
prob_c = 1/(1+exp((-0.1*(lp/pC))+(0.002*(pC-lp))));
run;


/* 16 */
data work.prospect;
set work.prospect;
informat accept_a:15.9 accept_b:15.9 accept_c:15.9;
if prob_A GT 0.5 then accept_a=1;
else accept_a=0;
if prob_B GT 0.5 then accept_b=1;
else accept_b=0;
if prob_C GT 0.5 then accept_c=1;
else accept_c=0;
run;


/* 17 */
proc means data=work.prospect noprint;
var accept_a accept_b accept_c;
output out=work.table_volume (drop = _TYPE_ _FREQ_)
sum(accept_a) = A sum(accept_b) = B sum(accept_c) = C;
run;


/* 18 */
data work.prospect;
set work.prospect;
informat pi_a:15.9 pi_b:15.9 pi_c:15.9;
pi_a = (pA-lp)*accept_a;
pi_b = (pA-lp)*accept_b;
pi_c = (pA-lp)*accept_c;
run;


/* 19 */
proc means data=work.prospect noprint;
var pi_a pi_b pi_c;
output out=work.table_profit (drop = _TYPE_ _FREQ_)
sum(pi_a) = profit_A sum(pi_b) = profit_B sum(pi_c) = profit_C;
run;


/* TASK 3: CREATE A REPORT */
/* Instructions 20-29 */

/* 20 */
options nodate pdfpageview=fitpage;
ods noproctitle;
ods pdf style=journal file="/home/u45009682/sasuser.v94/HOMEWORK/final_report_lakshminarayanan_lee_stoyanova.pdf"
startpage=no;

title1 "Homework Project PSTAT 130";
title2 "Nivi Lakshminarayanan, Priscilla Lee, Deni Stoyanova";
title3 "nlakshminarayanan@ucsb.edu, priscilla_lee@ucsb.edu, denitza@ucsb.edu";

ods layout gridded;

/* 21 */
proc sort data=work.ptf;
by age hp density;
run;

proc sort data=work.pp;
by age hp density;
run;

data work.ptf_pp ;
merge work.ptf work.pp;
by age hp density;
run;

data work.ptf_pp;
set work.ptf_pp;
where year=2019;
run;


/* 22 */
title "Average Frequency by Age";
proc means data=work.ptf_pp mean nonobs;
var freq;
class age;
output out=work.average_claims_freq1 (drop= _FREQ_ _TYPE_);
types age;
run;

title "Average Frequency by Density";
proc means data=work.ptf_pp mean nonobs;
var freq;
class density;
output out=work.average_claims_freq2 (drop= _FREQ_ _TYPE_);
types density;
run;

title "Average Frequency by HP";
proc means data=work.ptf_pp mean nonobs;
var freq;
class hp;
output out=work.average_claims_freq3 (drop= _FREQ_ _TYPE_);
types hp;
run;

/* 23 */
title "Average Claims Cost by Age";
proc means data=work.ptf_pp mean nonobs;
var cost;
class age;
output out=work.average_claims_cost1 (drop= _FREQ_ _TYPE_);
types age;
run;

title "Average Claims Cost by Density";
proc means data=work.ptf_pp mean nonobs;
var cost;
class density;
output out=work.average_claims_cost2 (drop= _FREQ_ _TYPE_);
types density;
run;

title "Average Claims Cost by HP";
proc means data=work.ptf_pp mean nonobs;
var cost;
class hp;
output out=work.average_claims_cost3 (drop= _FREQ_ _TYPE_);
types hp;
run;

/* 24 */
title "Average Pure Premium of the Current Portfolio";
proc means data=work.ptf_pp mean nonobs;
var pp;
output out=work.average_pp (drop= _FREQ_ _TYPE_);
run;

/* 25 */
proc sort data=work.ptf_pp;
by pp;
run;

title "Age, Density, and Horsepower with Lowest Pure Premium";
proc print data=work.ptf_pp (obs=1) noobs; var age density hp; run;

/* 26 */
proc sort data=work.ptf_pp;
by descending pp;
run;

title "Age, Density, and Horsepower with Highest Pure Premium";
proc print data=work.ptf_pp (obs=1) noobs; var age density hp; run;

/* 27 */
title "Volume of New Business Generated By Each Strategy";
proc print data=work.table_volume label noobs;
label A="Strategy A" B="Strategy B" C="Strategy C";
run;

/* 28 */
title "Profit Generated By Each Strategy";
footnote "The Best Strategy is Strategy A";
proc print data=work.table_profit label noobs;
label profit_A="Strategy A" profit_B="Strategy B" profit_C="Strategy C";
run;

/* 29 */
data work.final_price;
set work.price;
keep age hp density pA; /* keeping pA since it is the highest profit based on the above instruciton*/
rename pA=best_price;
run;

title "Final Price";
proc print data=work.final_price noobs; run;

ods layout end;
ods PDF CLOSE;