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

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

/* 1 */
libname data "/folders/myfolders/Project/Database";
/* 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="/folders/myfolders/Project/Database/cars.csv"
out=work.cars
dbms=CSV replace;
getnames=yes;
/* datarow=2; */
run;

data work.cars;
set work.cars;
format 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="/folders/myfolders/Project/Database/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;
format 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 */
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;
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;
run;


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

/* 6 */
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;
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;
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;

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; */


/* 8 */
data work.freq;
set work.freq;
keep age hp density freq;
run;

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

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;

proc print data=work.cost;
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 */



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

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