PSTAT130: SAS Base Program
Project: Actuarial Science with SAS
Claire Mouminoux
Winter 2020
Instructions: Please note that you are responsible for the work sharing. Each
group will submit two files on GauchoSpace
• Your SAS code in .txt format - Please follow the libname name, vari-
able names and database names instruction to facilitate correction.
• The report produced using the OUTPUT DELIVERY SYSTEM (ODS)
as .pdf file
The code must be appropriately commented to refer to the instruction num-
ber, and the output customized with title so that we understand which
problem you are treating. Do not forget to add your names at the top of
the code.
1 Context
You have just been hired by a Californian Insurance compagny CA SUN INSURANCE
as a statistical analyst and SAS programer in the technical department of motor insur-
ance. The company wants to renew their motor insurance price strategy for the 2020
new business 1. To do so, you will have to analyze their past data to propose an ade-
quate price regarding:
- expected costs of motor claims for the different profile of customers;
- the volume of new business (number of new contracts);
- overall expected profit generated by the 2020 new business.
In addition, you will colaborate with the marketing department which will give you
some information about customers’ behavior and their probability to underwrite a
contract regarding their quotes (i.e. the price that you will determine).
The company has high expectation from your work and you will have to generate an
easely understandable SAS report to explain your price strategy. Also, you will have to
deal with some constraints imposed by your managers and demonstrate to them that
you have followed their recommendations.
1The 2020 new business corresponds to all new insureds who will underwrite a contract for the first
time at CA SUN INSURANCE throughout 2020
1
2 All you need to know
2.1 Actuarial Science: the art of estimating future costs
2.1.1 Pure Premium: Frequency × Severity
To estimate futur claim costs for a cohort i 2, actuaries and statistical analysts use the
past data collected over years to determine two important components:
- The frequency: corresponds to the number of claims of a cohort over the number
of individuals within this cohort: Freqi , it could be interpreted as the probability to
have a claim.
- The severity: corresponds to the average cost of claims within the cohort i: Costi .
Example - Step 1:
Name Age Gender Claims Claim Cost
Pierre 18 H Yes 500
Paul 50 H Yes 800
Luke 18 H No
Claire 50 F No
Anna 50 F Yes 200
Maria 18 F Yes 400
Jude 18 F Yes 500
Peter 50 H No
Sylvia 18 F Np
Henri 18 H Yes 300
Oliver 18 H Yes 900
Suppose we define a cohort according to the following characteristics: Age and Gen-
der. In this example, we have 4 different cohorts corresponding to 4 profiles. We need
to determine:
- the number of individuals within each cohort (number of individuals having the
same profile): NB Individuals;
- the number of claims recorded within each cohort: NB Claims;
- the sum of claim costs within each cohort: Sum of Claim Costs.
2A cohort is a group of individuals having the same characteristics such as the same age and the same
car for instance. It corresponds to the profile of an individual.
2
Cohort i Age Gender NB Individuals NB Claims Sum of Claim Cost
1 18 H 4 3 1700
2 50 H 2 1 800
3 18 F 3 2 900
4 50 F 2 1 200
We are now able to compute the frequency Freqi and the severity Costi of claims for
each cohort i = {1,2,3,4}. These two elements allow us to compute the P ure P remiumi
(P Pi) for each individual given his/her profile (i.e. given the cohort i in which he/she
belongs to). The pure premium is defined by:
P Pi = Freqi ×Costi .
It corresponds to the expected average claim costs of an individual belonging to cohort
i. Indeed, it takes into account both: the probability of an individual with a given
profile to have a claim and the expected costs of the claim if any.
Cohort i Age Gender Freqi Costi P Pi
1 18 H 0.75 1700 425
2 50 H 0.5 800 400
3 18 F 0.67 900 300
4 50 F 0.5 200 100
2.1.2 The Loaded Premium: Pure Premium × (1+Loading Factor)
The costs of claims is not the only cost to take into account for an insurance com-
pany. Besides the price of repairing a car after an accident, for instance, the insurance
company has to remunerate specialists (such as actuaries, accident experts, accounting
officers,...): theses costs are called the handling costs.
In general, we consider these costs as proportional to the expected average costs of
claims of an insured (i.e. the pure premium). In other words, policyholders who have
more claims or/and more costly claims generate higher handling costs (i.e. need of an
expert, take more processing time, ...). The Loaded P remiumi (LPi) for each individual
belonging to a cohort i is equal to:
LPi = P Pi × (1 +λ)
where λ > 0 is the Loading Factor. The loading factor is usually given by the account-
ing department of the insurance company.
Example - Step 2:
Let’s return to our example. We have already determined the P Pi for each profile/cohort
i. Suppose that the accounting department of the insurance company gives you the fol-
lowing information:
3
For a claim cost of $500 we also have additional handling costs of $25.
We thus estimate the loading factor at 5% = 25/500
We are now able to estimate the future costs (including claim costs and handling costs)
for each profile.
Cohort i Age Gender P Pi (1 +λ) LPi
1 18 H 425 1.05 446
2 50 H 400 1.05 420
3 18 F 300 1.05 315
4 50 F 100 1.05 105
2.2 The Insurance Price Strategy
2.2.1 Insurance Price: Loaded Premium × (1+Margin Rate)
The Insurance P ricei (Pi) is the final price proposed to a new prospect 3. To make prof-
its, the insurance company cannot define: Insurance Price = Loaded Premium. They
need to generate a margin on each contract and will define a price strategy by defining
a Margin Rate.
Therefore, the Insurance P ricei (Pi) is :
Pi = LPi × (1 + β).
where β > 0 is the Margin Rate.
Example - Step 3:
Let’s return to our example! Suppose that the managers are hesitating between two
price strategies:
- Strat A: βA = 0.01
- Strat B: βB = 0.05
We first compute the Insurance Price for each profile i and each strategy A or B:
Cohort i Age Gender LPi Pi,A = LPi × (1 + βA) Pi,B = LPi × (1 + βB)
1 18 H 446 451 469
2 50 H 420 424 441
3 18 F 315 318 331
4 50 F 105 106 110
3A prospect is a customer who asks for a quote, but who is not already a client/insured of the com-
pany. He/She will decide to become a client if he/she agrees with the Insurance Price.
4
2.2.2 Determining the Best Strategy
At this stage, we are not able to determine which of A or B price strategy is the best. A
highest price means a largest margin BUT a highest price also means a lowest proba-
bility that a prospect (the customer who receives the quote) accepts it and becomes a
new client!
We need more information about customers’ behaviors and that’s a good news since
an entire department is studying that! Indeed, the quantitative marketing departe-
ment of an insurance company studies the behavior of prospects with respect to an
insurance price quote. In other words, they compute the probability of a prospect to
underwrite the insurance contract given its price.
To do so, they generally use a logistic regression where :
P rob(Accept|Pj) = 11 + exp(f (Pj))
Here P rob(Accept|Pj) is the probability that a prospect j receiving an insurance price
Pj accepts it and becomes a new clients: a New Business contract.
To determine the best strategy you will need two elements from the marketing depart-
ment:
- The function f (Pj) that they have estimated;
- A database of prospects to compute the effect of your two insurance price strategies.
Example - Step 4:
The marketing department gives you the following information.
Please find below our estimated function and the database of prospect clients for 2020
P rob(Accept|Pj) = 11 + exp(f (Pj)) with f (Pj) = −0.45×
LPj
Pj
+ 0.001× Pj
we consider that a prospect becomes a new business client if P rob(Accept|Pj) > 0.5.
5
Prospect_ID Age Gender
1 18 F
3 18 F
5 18 F
6 18 F
7 18 F
12 50 F
14 50 F
2 18 H
4 18 H
8 18 H
9 18 H
10 50 H
11 50 H
13 50 H
15 50 H
Now we have to determine:
- what price the prospect will receive depending on each strategy A or B;
- what is the probability that a prospect accepts depending on each strategy A or B and
the information given by the marketing department ;
- who accepts depending on each strategy A or B and the rule: acceptance if P rob(Accept|Pj) >
0.5.
Prospect_ID Age Gender LP P_A P_B P(Accept |P_A) P(Accept |P_B) Accept | P_A Accept | P_B
1 18 F 315 318 331 0.532 0.524 Yes Yes
3 18 F 315 318 331 0.532 0.524 Yes Yes
5 18 F 315 318 331 0.532 0.524 Yes Yes
6 18 F 315 318 331 0.532 0.524 Yes Yes
7 18 F 315 318 331 0.532 0.524 Yes Yes
12 50 F 105 106 110 0.584 0.579 Yes Yes
14 50 F 105 106 110 0.584 0.579 Yes Yes
2 18 H 446 451 469 0.499 0.490 No No
4 18 H 446 451 469 0.499 0.490 No No
8 18 H 446 451 469 0.499 0.490 No No
9 18 H 446 451 469 0.499 0.490 No No
10 50 H 420 424 441 0.505 0.497 Yes No
11 50 H 420 424 441 0.505 0.497 Yes No
13 50 H 420 424 441 0.505 0.497 Yes No
15 50 H 420 424 441 0.505 0.497 Yes No
Now we are able to determine the best strategy to adopt and the insurance price to
offer depending on the prospect customer profile.
The expected profit is equal to:
Π(A) =
j=15∑
j=1
(Pj,A −LPj)×1j,A, for strategy A
6
or
Π(B) =
j=15∑
j=1
(Pj,B −LPj)×1j,B, for strategy B.
Here 1j,A = 1 if the prospect j accepts the insurance price PA and 0 otherwise. Simi-
larly, 1j,B = 1 if the prospect j accepts the insurance price PB and 0 otherwise.
Prospect_ID LPj Pj,A Pj,B Pj,A −LPj Pj,B −LPj 1j,A 1j,B (Pj,A −LPj )× 1j,A (Pj,B −LPj )× 1j,B
1 315 318 331 3 13 1 1 3 13
3 315 318 331 3 13 1 1 3 13
5 315 318 331 3 13 1 1 3 13
6 315 318 331 3 13 1 1 3 13
7 315 318 331 3 13 1 1 3 13
12 105 106 110 1 4 1 1 1 4
14 105 106 110 1 4 1 1 1 4
2 446 451 469 5 18 0 0 0 0
4 446 451 469 5 18 0 0 0 0
8 446 451 469 5 18 0 0 0 0
9 446 451 469 5 18 0 0 0 0
10 420 424 441 4 17 1 0 4 0
11 420 424 441 4 17 1 0 4 0
13 420 424 441 4 17 1 0 4 0
15 420 424 441 4 17 1 0 4 0
Sum 11 7 33 73
The best insurance price strategy is PB with a total profit of $73 and a new business
volume of 7.
2.2.3 The Final Price table
We determined the best price strategy to adopt to maximise the profit regarding to
the risk of each profile, under the constraints and recommandations of managers and
the help of other departments in the insurance company. Besides a report to sum
up your statistical analysis, your work ouput (a datatable) is the final insurance price
table depending on each profile (i.e. combination of prospects’ characteristics). This
insurance price table will be used to determine which price to propose to each new
prospects.
Example - Step 5:
In our exemple the final output correspond to the table bellow:
Age Gender Final Price
18 H 469
50 H 441
18 F 331
50 F 110
7
2.3 Others comments
Of course an insurance company has more data and this is the reason why SAS is es-
sential to manage big database. The following sections of this document will explain
you, step by step, how to do it using SAS!
Good luck and Enjoy!
3 Step by Step Project Instruction
3.1 Computation of future costs
In this section you will compute the frequency and severity for the different profile
based on previous claims data of the company. As a reminder, for each new database
it is important to use the proc contents procedure to have database information.
Since 2015, the company keep all the information about the insureds in their portefolio
and the claims that occured. You have at your disposal two database from the historical
data of the company:
• ptf.sas7bdat: corresponds to the portfolio of clients of the company from 2015 to
2019 and their characteristics: cars type, birthdate, zip code, their policyholder
id and the ongoing contract starting date (i.e. a policyholder can be present sev-
eral time in the portfolio database if it stay several years in the insurance com-
pany but will have a different ongoing contract date).
• claims.sas7bdat: corresponds to each claim registered by the company from
2015 to 2019 and includes: the policyholder id, the ongoing contract date and
the cost of the claim.
3.1.1 Cohorts’ profile
The company want to create cohorts according to the following information:
- age: the age of the client with the following level : age ≤ 21: "-21", 21 < age ≤ 35:
"21-35", 35 < age ≤ 60: "35-60" and 60 < age: "+60";
- hp: the horsepower of the car with the following level: horsepower ≤ 150: "low",
150 < horsepower ≤ 300: "medium", 300 < horsepower : "high";
- density: the density of the ZIP code where is living with the following level: population ≤
4000: "low", 4000 < population ≤ 30000: "medium", 30000 < population : "high";
Therefore, a profile correspond to the combinaison of these three variables. All in
all there are 36 different profiles: 4(age)× 3(hp)× 3(density) = 36.
Instruction 1: Import the ptf.sas7bdat database using the LIBNAME procedure with
"data" as the name of the path. Using the information of the data.ptf database, compute
the age of each policyholder and create the character variable ’age’ (with the informat
8
$15.) according to levels described before. The output should contain all the previous
variable already exisiting in data.ptf database and the new variable age. The output is
work.ptf: it is a database stored in the temporary library "work" and named ptf.
Hint: Be carefull, for each row we want to have the age of the policyholder with respect
to the date when the contract was ongoing. The if statement if the simplest way to
create the variable with the different level of age.
In addition, insurers often use external data to improve their statistical analysis. CA
SUN INSURANCE, uses two different external database:
- cars.csv: each row includes a cars type ("id" column) and its different features (in-
cluding horsepower).
- CA_ZIP_CODE.TXT: each row includes a californian zip code ("zip_code" column)
and the corresponding population ("population" column).
Instruction 2: Import the cars.csv in sas as work.cars using Proc import procedure
and create the character variable "hp" in the work.cars database (with the informat
$15.) according to levels decribed before. Merge it with the work.ptf database accord-
ing to the "id_cars" variable. The output is again the work.ptf database.
Instruction 3: Import the CA_ZIP_CODE.TXT in sas as work.CA_ZIP_CODE using
Proc import procedure and create the character variable "density" in work.cars (with
the informat $15.) according to levels decribed before. Merge it with the work.ptf
database according to the "Zip_code" variable. The output is again the work.ptf database.
For each row of the ptf database you have now the three characteristics variables in
order to define the policyholder profile.
3.1.2 Computation of the loaded premium
You have to determine the profile of policyholders having a claim.
Instruction 4: Import the claims.sas7bdat database using the LIBNAME procedure with
"data" as the name of the path. Merge the data.claims and the work.ptf by "policy-
holder_id" and "policy_starting_date" to recover the characterics variable of each pol-
icyholder having a claim for each year of contract. The ouput should be a database
named "claims" and stored in the temporary library work.
Hint: Be carefull we want to have in the output database only the rows present on the
claims database.
We now want to know the number of claims and the average cost of claim (severity)
for each profile and for each contract year.
Instruction 5: Create a variable "year" in the work.claims database corresponding to
the year of the ongoing contract and create a database work.claims_summary includ-
ing the number of claims and the average cost of claims for each year and profile. The
average cost of claim variable should be named "cost" and the number of claims vari-
able should be named "nb_claims".
9
Hint: the PROC MEANS procedure is the easiest way to do it (do not forget the output
out statement).
You need to know the number of policyholder on in each cohort and for each year
in order to determine the frequency of claim for each profile and for each year.
Instruction 6: Create a variable "year" in the work.ptf database corresponding to the
year of the ongoing contract and create a database work.ptf_summary including the
number of policyholders for each year and profile. The number of policyholder vari-
able should be named "nb".
Hint: the PROC MEANS procedure is the simplest way to do it (do not forget the output
out statement).
Now you have to compute the frequency of claim for each year and profile.
Instruction 7: Create a work.summary database corresponding to the merge between
work.claims_summary and work.ptf_summary by "age", "density", "population" and
"year" and compute the frequency of claims for each years and profile using variables
"nb_claims" and "nb". The frequency variable is named "freq".
For now, you have analyzed the frequency and the average cost of claim for each profile
and for each year of contract. Indeed, to define frequency to consider (over the years)
for the computation of the pure premium, the manager asks you to be "conservative"
and keep for each profile, the year where the frequency is the highest.
Instruction 8: Create a work.freq database corresponding to the highest frequency
for each profile. Keep on this database only the "age", "density", "hp" and "freq" vari-
ables.
Hint: the PROC SORT procedure is the simplest way to do it (do not forget the nodupkey
and be carefull on the order of by variables).
Similarly, to define averale claim costs to consider (over the years) for the computa-
tion of the pure premium, the manager asks you to be "conservative" and keep for each
profile, the year where the cost is the highest.
Instruction 9: Create a work.cost database corresponding to the highest average cost
of claims for each profile. Keep on this database only the "age", "density", "hp" and
"cost" variables.
Hint: the PROC SORT procedure is the simplest way to do it (do not forget the nodupkey
and be carefull on the order of by variables).
Now you can easely determine the pure premium since you have for each profile the
cost and frequency of claim.
Instruction 10: Create a work.pp database corresponding to the merge of the work.cost
and work.freq database by "age", "density" and "hp" variables. Create a new variable
"pp" corresponding to the pure premium.
Hint: the "pp" database should have 36 observations corresponding to the 36 possible
10
profiles.
Instruction 11: Create a new variable "lp" in the work.pp database corresponding to
the loaded premium according to the following information given by the accounting
department:
For a claim cost of $500 we also have additional handling costs of $25.
We thus estimate the loading factor at 5% = 25/500
3.2 Definition of the Best Insurance Price Strategy
Your managers want to use one of the following strategies:
- Strategy A: loading factor equal to 5%;
- Strategy B: loading factor equal to 10%;
- Strategy C: loading factor equal to 15%;
and they need you to determine which want generate the highest profit.
3.2.1 Define the quote received for each strategy and for each prospect
Instruction 12: Create a new database work.price using the work.pp database. In this
database keep only the "age", "density", "hp", "lp" variables and create three new vari-
ables "pA", "pB", "pC", corresponding respectively to the price of strategy A, B and C .
From the example at the beginning of this document, we know that the price is not
sufficient to determine the profit generated by a stratgegy. You receive the following
information from the marketing department:
Please find below our estimated function and the database of prospect clients for 2020
(Prospect.csv)
P rob(Accept|Pj) = 11 + exp(f (Pj)) with f (Pj) = −0.1×
LPj
Pj
+ 0.002× (Pj −LPj)
we consider that a prospect becomes a new business client if P rob(Accept|Pj) > 0.5.
Instruction 13: Import the Prospect.csv database given by the marketing department
as work.prospect using Proc import procedure Similarly to Instruction 1, 2 and 3, re-
covered the three characteristics to determine the profile of each prospect. The output
should be a database stored in the temporary work library named prospect.
Hint: the work.cars and work.CA_ZIP_CODE database are already existing.
Instruction 14: Merge the work.prospect database with the work.price database by
"age", "density" and "hp" in order to determine the price that they will receive for each
strategy A, B and C. The output should be the work.prospect database.
11
3.2.2 Compute the profit and volume generated by each strategy
Instruction 15: Create for each row three new variable "prob_a", "prob_b" and "prob_c"
in work.prospect database, corresponding to the probability that the prospect receiv-
ing respectively the price "pa", "pb" and "pc" accepts the offer.
Hint: of course you need to take into account the information given by the marketing
department.
Instruction 16: Create for each row three new variables "accept_a", "accept_b" and
"accept_c" in work.prospect database equal to 1 if the prospect accepts respectively
the price "pa", "pb" and "pc" and 0 otherwise.
Hint: the if statement is the simplest way to do it.
Instruction 17: Create a new database work.table_volume corresponding to the vol-
ume of new business for each strategy.
Hint: the Proc MEANS is the simplest way to do it.
Instruction 18: Compute the profit for each row of the work.prospect database and
call the variable respectively "pi_a", "pi_b" and "pi_c" in the work.prospect database
for strategy a, b and c.
Instruction 19: Create a new database work.table_profit corresponding to the profit
for each strategy.
Hint: the Proc MEANS is the simplest way to do it.
You are now able to determine the best strategy to implement to maximise the
profit for the new business in 2020 of this motor insurance company!
3.3 Create a report
You job is almost finished but you need to provide the key elements of your reasoning
and the final output (i.e. the table of the insurance price by profile that you recom-
mand in order to optimize the new business profit).
Your manager ask you to create a pdf report with the following information (in this
order):
• On the top of each page your name and ucsb email adress
• A summary table with the average claim frequency (used in the pp) by age of the
current portfolio
• A summary table with the average claim frequency (used in the pp) by density of
the current portfolio
• A summary table with the average claim frequency (used in the pp) by horse
power of the current portfolio
• A summary table with the average claim costs (used in the pp) by age of the
current portfolio
12
• A summary table with the average claim costs (used in the pp) by density of the
current portfolio
• A summary table with the average claim costs (used in the pp) by horse power of
the current portfolio
• A summary table with the average pure premium of the current portfolio
• The profile with the lowest pure premium
• The profile with the highest pure premium
• A summary table with the volume of new business of each strategy
• A summary table with the total profit of each strategy
• The final pricing strategy table
Instruction 20: Create a pdf report using ODS procedure named "final_report_lastnames.pdf".
To avoid to have one page for each output (corresponding to each procedure in the STATEMENT>) and to add your name at the top of each page use the following ODS op-
tions:
options nodate pdfpageview=FITPAGE;
ods NOPROCTITLE;
ODS PDF style=journal File="...final_report_lastname.pdf"
startpage=no;
title1 "Homework Project PSTAT130";
title2 "Your names;
title3 "Your email adresses";
ods layout gridded;

ods layout end;
ODS PDF CLOSE;
This ODS procedure can generate a warning in the log windows : "WARNING: Layout
is not supported in the RTF(WEB) destination. Layout will be ignored." do
not take it into account. Complete the with the following instruc-
tions.
Instruction 21: Create a work.ptf_pp database by merging the work.ptf database and
the work.pp database previously created. Keep only the current portefolio (i.e. the
policy ongoing date equal to 2019).
Instruction 22: Using Proc mean procedure on the work.ptf_pp database, display
three consecutive tables for the average claim frequency by age, density and horse-
power.
13
Hint: do not forget to add a title to each procedure
Instruction 23: Using Proc mean procedure on the work.ptf_pp database, display
three consecutive tables for the average claim costs by age, density and horsepower.
Hint: do not forget to add a title to each procedure
Instruction 24: Using Proc mean procedure on the work.ptf_pp database, display the
average pure premium of the current portfolio.
Instruction 25: Display only the three variables of the profile with the lowest pure
premium.
Hint: Use Proc sort procedure and proc print procedure with obs option.
Instruction 26: Display only the three variables of the profile with the highest pure
premium.
Hint: Use Proc sort procedure and proc print procedure with obs option.
Instruction 27: Using the database work.table_volume previsouly created , display
the volume of new business generated by each strategy. Add a temporary label to re-
place the name column with Strategy A, Strategy B and Strategy C.
Hint: Use Proc print procedure
Instruction 28: Using the database work.table_profit previsouly created , display the
profit generated by each strategy. Add a temporary label to replace the name column
with Strategy A, Strategy B and Strategy C. Add a footnote to give the best strategy
chosen.
Hint: Use Proc print procedure
Instruction 29: Create a database work.final_price using "work.price database. Keep
only the characteristics variables used to determine profiles and rename the price strat-
egy selected by "best_price". Print this database without the observation index .
14