Problem Statement
Domain: Insurance
Business Context - A Car Insurance company wants to plan next financial year and setting up
average premium targets from different segments. Hence they want to analyze the last three
year data and want to set up the target. A thumb rule of next year target is taking the average of
last 3 year and increases it by 10 percent. Management wants to set a target for all the
segments of manufacturer cross rto..
Dataset description:
Year over Year policy base
Variable Name Variable Detail
Policy_num Policy num (Unique ID for our case study)
mobile_num Mobile number of customers
TxnID Transaction ID of payment
Year Year dependent on policy login date
Type New or Renewal policy
Base_Premium Basic premium without any add-on
No_Claim_bonus_discount Discount received because of no claim
road_side_assistence_addon premium for road side assistance
Tyre_addon Premium for tyre coverage
Electronic_addon Premium for electronic part coverage
thirt_party_liablity_premium Premium for third party liability coverage
Manufacturer Manufacturer of car
Party_type Type of policy: First or third party
registration_date registration date of car
Car_number Car number
RTO data
Variable Name Variable Detail
rto_name RTO office name tagged to the unique ID
rto_id Unique RTO id
You have been selected to perform this job. You have received 6 files, 5 files is of policy level
details of 5 years and 1 file for rto mapping
Below are the exact views required by management:
1. Calculate target of next year for manufacturer cross rto
2. What is the percentage distribution of new and renewal policy across manufacturer for latest
year
Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
3. Calculate the average age of car for manufacture cross rto
Learning Steps to perform:
Data Preparation Step
1. Import all the 6 files in SAS data environment
2. Append the 5 year files except rto mapping
3. Remove all the unwanted variables like mobile number and TxnID
4. Extract rto number from car number
5. Do a left join on the appended dataset with rto mapping to get rto information
6. Calculate total premium for all policy
7. Calculate age of car in year from registration date
Marks Distribution
Question 1:
1. Import all the 4 files in SAS data environment (8 Mark)
Question 2:
2. Create one dataset from all the 4 dataset? (8 Mark)
Question 3:
3. Remove all unwanted ID variables? (2 Mark)
Question 4:
4. Calculate annual premium for all customers? (4 Mark)
Question 5:
5. Calculate age and tenure as of 31 July 2020 for all customers? (4 Marks)
Question 6:
6. Create a product name by using both level of product information. And product name should be representable
i.e. no code should be present in final product name? (4 Marks)
Question 7:
7. After doing clean up in your data, you have to calculate the distribution of customers across product and policy
status and interpret the result (4+1 Marks)
Question 8:
8. Calculate Average annual premium for different payment mode and interpret the result? (4+1 Marks)
Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited
Question 9:
9. Calculate Average persistency score, no fraud score and tenure of customers across product and policy status,
and interpret the result? (4+1 Marks)
Question 10:
10. Calculate Average age of customer across acquisition channel and policy status, and interpret the result? (4+1
Marks)
Proprietary content. ©Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited