How One Can Use SAS to Easily Manage an Otherwise Unmanageable Task

1
How One Can Use SAS to Easily Manage an Otherwise
Unmanageable Task
Dr. Arthur S. Tabachneck Insurance Bureau of
Canada Statistical Research and Development
Department
2
IBCs Department of Statistical Research and
Development

• Manage databases which include all government and
  non-government auto insurers premium and claims
  information
• Build and maintain a database of Vehicle
  Information Numbers (VINs) and the vehicle
  characteristics they represent
• Assist other IBC divisions with all needed
  analytical services
• Conduct research to identify emerging trends
• Develop and apply statistical models to estimate
  anticipated claims and related costs
• Provide advisory make/model/model year-specific
  Collision, Comprehensive, Property Damage and
  Accident Benefit ratings for all Canadian insurers

3
Insurance 101
Data
IBC
MPI
ICBC
GAA
SGI
SAAQ
IncludingVehicle Information Number
(VIN)premium/vehicle/coverage of
claims/vehicle/coveragecost of
claims/vehicle/coverage
4
How much data is involved?
0.003 mil vehicles
0.021 mil vehicles
0.024 mil vehicles
4.2 mil vehicles
0.25 mil vehicles
0.076 mil vehicles
2.2 mil vehicles
0.53 mil vehicles
2.2 mil vehicles
0.66 mil vehicles
0.62 mil vehicles
0.45 mil vehicles
6.8 mil vehicles
5
How much data is involved?
Me 2 staff----gt
of Records 40 2,174,866,200 86,994,648,000
of Records 18,123,885 10 181,238,850
of Records 181,238,850 12 2,174,866,200
6
Insurance 101
Loss Cost
Total Claim Costs
------------------------
of Vehicles
of Claims ------------------- of Vehicles
Total Claim Costs -----------------------
--- of Claims
Likelihood of a claim
Average claim cost
7
Insurance 101
Cost of Insurance
Total Premium
Where One Lives
The Vehicle
CLEAR Rating
Ones Driving Record
Ones Age
How Much One Drives
8
The Problem
Collision coverage the costs involved when one
is at fault in an accident Cant just look at
how much a vehicle costs
Mitsubishi Lancer MSRP 15,555
Toyota Corolla MSRP 16,404
Relative Loss Cost 650
Relative Loss Cost 115
9
The Problem
Theft Frequency (the likelihood of a vehicle
being stolen) Cant just look at how much a
vehicle costs
Chevrolet Avalanche 1500 MSRP 35,938
Mercedes-Benz C230 MSRP 36,386
Relative Theft Frequency 74
Relative Theft Frequency 238
10
The Problem
The added cost of features which reduce insurance
claim costs should not result in higher premiums
Traction Control ABS
Restraint Systems
Seat Belt Pretensioners Theft Deterrent Systems
Stability Control
11
The Problem
Insurance rates must be available before
insurance experience is known
12
Canadian Loss Experience Automobile Rating (CLEAR)

• Body style
• Drivetrain
• Wheelbase
• Weight
• Engine displacement
• Engine horsepower
• MSRP
• Indexed MSRP
• Type of brakes
• Theft deterrent system
• Track width
• Height
• Types of airbags
• Manufacturer

• Seating capacity
• Brake assistance
• Ground clearance
• Traction control
• Stability control
• Types of headrestraints
• Seatbelt pretensioners
• Lane departure warning
• Tracking system
• Parts marking
• Engine type
• Engine placement
• Age
• General model and model

13
Canadian Loss Experience Automobile Rating (CLEAR)
14
Canadian Loss Experience Automobile Rating (CLEAR)
15
Canadian Loss Experience Automobile Rating (CLEAR)
16
CLEAR - How it works
17
Canadian Loss Experience Automobile Rating (CLEAR)
18
Canadian Loss Experience Automobile Rating (CLEAR)
19
Canadian Loss Experience Automobile Rating (CLEAR)
20
Canadian Loss Experience Automobile Rating (CLEAR)
21
Canadian Loss Experience Automobile Rating (CLEAR)
22
Canadian Loss Experience Automobile Rating (CLEAR)
23
Canadian Loss Experience Automobile Rating (CLEAR)
24
Canadian Loss Experience Automobile Rating (CLEAR)
25
Canadian Loss Experience Automobile Rating (CLEAR)
How?
26
How? Design appropriate directories and filename
structures
ARQC3001.sas7bdat
Type of Run
Version
Measure
Coverage
Province
27
How? Build SAS macros to accomplish each task
28
How? Use SAS AF to let users indicate
requirements
29
How? Use SAS AF to let users indicate
requirements
30
How? Incorporate SCL to run each macro
31
How?

• Understand the problem
• Planning
• Design
• SAS Macros
• SAS AF
• SAS Component Language

32
Questions?