ISO Property Data: Loss Ratio Analysis by State and ZIP Code

Aggregating earned premium and loss+LAE from ISO property data to compute loss ratios and peril compositions across states and ZIP codes.

mapping policy

unknow takes up a lot of portion in industry RMS, both for loss and TIV. But in-force data has almost zero percentage of unknown. thus. remove unknown occupancy from RMS.
remove occupancy = Miscellaneous and

next step:

daily

6/17

occupancy mapping

RMS preprocessing step added: discard O_DESC=

Water/Sewage treatment plants Petrochemical - Refineries Light Industrial - Biomedical Heavy Industrial - Cement Heavy Industrial - Pulp & Paper Light Industrial - Pharmaceutical Light Industrial - Semiconductor Electric Power - Nuclear Power Plant Electric Power - Nuclear power plant

joining keys

zip code and state is the key for joins, must be super clean

zip code

zip_mapping: zip is numeric, it discards zeros on the left. no records with zip=00000
iso_prop_18_22: zip is numeric, it discards zeros on the left. 221 records with zip=00000
RMS: postalcode is numeric, it discards zeros on the left
cinfin_master_adj: postalcode is char.
- there are 563 rows with postalcode=00000. 21 states, mostly around 200k per state, small relative to statewise total sum. I propose: discard these records.

bg1 mapping

zip_isoterr: derived from iso_prop_18_22. zipcd numeric. terr char3
mapping_isoterr_bg1 : terr char3 derived from excel file. one terr code has different states. thus we should use st-terr pair.
terrdatacube_bg1cic: terr char3

in iso_prop_18_22, there are some zip code that have multiple terr code. remedy: choose the terr that has maximum number of counts

Data

iso data: 10% sample of the whole commercial property insurance market. policy level. 2018-2022 5 years
rms data: zip-level simulation data of the whole (100%) comercial property insurance market for 2025
in-force exposure data: policy level data of cincinnati insurance company for 2025
cf datamart: compnay policy database

cf datamart

business_unit = MIDDLE MARKETS

in-force exposure data

market segements

nine categories in column MarketSegments:
- Commercial Key Construction
- Commercial Key General Business
- Commercial Middle
- Commercial Small
- Personal High Net Worth - Admitted
- Personal High Net Worth - Non-Admitted
- Personal Middle - Admitted
- Small Business For now we will only use commercial middle. We might revisit this later.

Peril column has the breakdown we need.

`Peril`	`PerilModel`	`SourcePeril`
CS	SCS	OW
CS	SCS	SCS
CS	SCS	TH
EQ	EQ	EQ
EQ	EQ	EQ CovC Only
EQ	EQ	EQ Full Coverage
FF	FF	FF
HU	HU	HU
WF	WF	WF
WF	WF	Wildfire
WT	SCS	Winter

TIV column sums up everything, so we will use that column

How to get non-cat exposure from

number of locnum $\approx$ 1mil
number of rows $\approx$ 4.7mil
thus, each property has 4-5 rows on average
we take maximum over loc, because the TIV

occupancy

occupancy_type_desc in cf_datamart and occupancy in cinfin_master_adj

Occupancy

Agriculture
Air
Chemicals Processing
Communication (Radio and TV)
Construction
Education
Electrical
Entertainment and Recreation
Food and Drugs Processing
Gasoline Service Stations
General Commercial
General Industrial
General Services
Group Institutional Housing
Health Care Service
Heavy Fabrication and Assembly
High Technology
Highway
Light Fabrication and Assembly
Metal and Minerals Processing
Mining
Miscellaneous
Natural Gas
Permanent Dwelling (multi family housing)
Permanent Dwelling (single family housing)
Personal and Repair Services
Petroleum
Professional, Technical and Business Services
Railroad
Religion and Nonprofit
Restaurants
Retail Trade
Sea/Water
Telephone & Telegraph
Temporary Lodging
Unknown
Wholesale Trade

Sea/water

minor_class_desc: Piers, Wharves, Bridges
Petroleum:
minor_class_desc in cf_datamart is all Oil Distributing, Oil Terminals and LPG Tank Farms, Excluding Stock I match

railroad

not manufacturers, but does repair and maintenance

MINOR_CLASS_DESC: Railroads
NAICS_2_DESC:
- Other Public Services (Except Public Administration)
- Professional, Scientific, and Technical Services
- Transportation and Warehousing
- Wholesale Trade
NAICS_3_DESC:
- Repair and Maintenance
- Professional, Scientific, and Technical Services
- Support Activities for Transportation
- Merchant Wholesalers, Durable Goods
NAICS_DESC:
- Commercial and Industrial Machinery and Equipment (Except Automotive and Electronic) Repair and Maintenance
- All Other Professional, Scientific, and Technical Services
- Process, Physical Distribution, and Logistics Consulting Services
- Support Activities for Rail Transportation
- Transportation Equipment and Supplies (Except Motor Vehicles) Merchant Wholesalers
NAICS_SUPERSECTOR:
- Other Services and Public Administration
- Professional and Business Services
- Trade, Transportation, and Utilities
NAICS_SUPERSECTOR_SUMMARY:
- Other Services and Public Administration
- Professional and Business Services
- Trade, Transportation, and Utilities

Parking

Classify this as a general commercial Reasons:
1. In datamart, the insured do not have manufacturing component (MINOR_CLASS_SPECIFIC_GROUP = NON_MANUFACTURING or ALL OTHER for all records).
2. The datamart records indicate that this occupancy is for parking garage for variety of businesses. Thus general is adequate.
3. The likely losses are minor and limited to low-speed vehicle collisions or incidents such as window breakage and theft. These risks are not related to catastrophic events. In the event of a catastrophe, the parking lot is typically not responsible for damage to parked vehicles.
4. Instead, potential losses would be limited to property such as gate equipment or possible employee injuries, which are not that expensive.
MINOR_CLASS_DESC:
- Auto Parking Garages, Car Washes
- Mill Yards
MINOR_CLASS_SPECIFIC_GROUP:
- NON_MANUFACTURING
- ALL OTHER
NAICS_SUPERSECTOR:
- Leisure and Hospitality
- Professional and Business Services
- Natural Resources and Mining
- Construction
- Education and Health Services
- Real Estate and Financial Services
- Information
- Manufacturing
- Trade, Transportation, and Utilities
- Other Services and Public Administration

Personal and Repair Services

Classify this as a general commercial Reasons:
1. In datamart, the insured do not have manufacturing component (MINOR_CLASS_SPECIFIC_GROUP = NON_MANUFACTURING or ALL OTHER for all records).
2. The datamart records indicate that this occupancy is for parking garage for variety of businesses. Thus general is adequate.
zip_mappings_20260116_sm: lookup table to map zip codes to territory variables (bg2_terr and scs_terr)

Religion and Nonprofit

all datamart records are minor_class_desc = churches and synagogues.
provide the money needed to continue your ministry during the restoration period.
legal defense costs and damages resulting from allegations or emotional injury due to religious communication, religious activity, or alleged discrimination.
https://churchinsurance.brotherhoodmutual.com/
Entertainment and Recreation

Bars and Taverns Bowling Alleys Boys’ and Girls’ Camps Dance Halls, Ballrooms & Discotheques Drive-in Theaters Golf Clubs, Tennis Clubs and Similar Sports Facilities without Cooking Halls and Auditoriums Motion Picture Studios Museums, Libraries, Art Galleries (non-profit) Recreational Facilities Noc Recreational Facilities, NOC Skating Rinks–Roller Rinks Theaters

Electrical

all records in datamart has minor_class_desc = Electric Generating Stations - Public Ut records in data mart do not have nuclear power plant

Group Institutional Housing

minor_class_desc = nursing and convalescnet homes or penal institution.

General services

minor_class_desc = Governmental Offices or Other Public Buildings, Fire Dept., Police, Water/Sewer

datamart do not have records

Communication (Radio and TV)
Telephone & Telegraph

ISO DataCube™

The dataset is stacked:
- Rows with nonzero EARNED_PREMIUM do not contain TOL and have zero LOSS_LAE_INCURRED.
- Rows with zero EARNED_PREMIUM contain TOL (categorical loss type) and have nonzero LOSS_LAE_INCURRED.

Variables

This information is provided by ISO DataCube™: Detail and Statistics of verisk % cite: https://www.verisk.com/4a79d9/siteassets/media/downloads/underwriting/iso-datacube-detail-and-statistics.pdf

Time: Latest available five calendar/accident years/quarters
- YEAR
- QTR: quarter
Space
- State (ST)
- ZIP code (ZIPCD)
- ISO rating territories(TERR)
- BIG_Territory, BGII_Territory, SCL_Territory: Territories for sublines
Causes of Loss
Subline ( and )
- SUB, Sub_Desc
- Basic Group I (SUB = 1): fire
- Basic Group II (SUB = 2): (Wind)
- Special (SUB = 3)
ISO classification detail
- CLASS, Class_Description
  - For example, CLASS=932 and Class_Description=’Gasoline Service Stations – 0932’
- Major_Class_Group
  - For example, Major_Class_Group=’Non-Manufacturing/Service’
- Rating_Class_Group
  - For example, Rating_Class_Group=’Other Habitational’
- Sub_Class_Group
  - For example, Sub_Class_Group=’Dwellings Written in Conjunction with Commercial Risks & Rated from the CLM’
Coverages (COV and Coverage)
- Building Coverage (COV=1)
- Contents Coverage (COV=2)
- Indivisible buildings and personal property (COV=3)
- Time Element (Business Interruption) Coverage (COV=4)
Construction type (Construction_Type)
- Construction Not Available
- Fire Resistive
- Frame
- Joisted Masonry
- Masonry Non-Combustible
- Modified Fire Resistive
- Non-Combustible
- Not Applicable
Type of loss (TOL): Property losses segmented by category
- All Other
- Fire & Lightning
- Not Available
- Water Damage
- Wind
- Burglary & Theft
- Mold
- Vandalism
- Freezing
- Sprinkler Leakage
- Collapse
- Explosion
- Riot & Civil Commotion
- Hail
exposures
- • Type of policy: Monoline versus commercial fire and allied lines business written on a package policy (other than businessowners)

• Rating: Class rated versus schedule (specific) rated, where applicable • Sprinklered: Sprinklered versus non-sprinklered, where applicable

• Size of loss range

Major catastrophes: Major catastrophes (e.g., hurricanes, wildfires, hail events) identified separately
- CAT_code: 308127 categories.

Codes

Code for category check

Coverage categories check

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

/* Count distinct categories */
proc sql;
    select count(distinct COV) as num_cov_categories
    from iso.iso_prop_20_24;
quit;

/* List all categories */
proc sql;
    select distinct Coverage
    from iso.iso_prop_20_24
    where Coverage is not missing
    order by Coverage;
quit;

Construction_Type categories check

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

/* Count distinct categories */
proc sql;
    select count(distinct Construction_Type) as num_categories
    from iso.iso_prop_20_24;
quit;

/* List all categories */
proc sql;
    select distinct Construction_Type
    from iso.iso_prop_20_24
    where Construction_Type is not missing
    order by Construction_Type;
quit;

CAT_code categories check

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

/* Count distinct categories */
proc sql;
    select count(CAT_code) as num_categories
    from iso.iso_prop_20_24;
quit;

/* List all categories */
proc sql;
    select distinct CAT_code
    from iso.iso_prop_20_24
    where CAT_code is not missing
    order by CAT_code;
quit;

CAT_event categories check

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

/* Count distinct categories */
proc sql;
    select count(CAT_event) as num_categories
    from iso.iso_prop_20_24;
quit;

/* List all categories */
proc sql;
    select distinct CAT_event
    from iso.iso_prop_20_24
    where CAT_event is not missing
    order by CAT_event;
quit;

Code for summary

claim row vs premium row

Geometric granularity

ISO data: zip code level (ZIPCD)
Doug’s data: bg2_terr

Task

Start working to aggregate EARNED_PREMIUM & LOSS_LAE_INCURRED by TOL (type of loss / peril), ZIPCD (zip code) and ST (state). Look through the rest of the fields.

terms

premium is the amount the insured pays for insurance coverage.
Written premium is the total premimum associated with policies that were issued during a specified period.
Earend premium represents the portion of the written premium for which coverage has laready been provied as of a certian point in time.
Losses: amount paid or owed to claimants under the provisions of an insurance contract
loss is amount of compensation, claim is demand for compensation
LAE: loss adjustment claims. amounts paid by the insurance company to investigate and settle claims
Losses + LAE takes up most of insurance cost and thus the premium.
loss ratio: the most common loss ratio metric is reported loss retio, or reported losses divided by earned premium. A loss ratio can be larger than 1 (or 100%).When the loss ratio exceeds 1, it indicates that the insurance company is paying out more in claims than it is collecting in earned premiums.

Capital Allocation Factor Workflow

Inputs

Peril mix for each region and statewise peril permissible loss ratios

Input 1: Peril Mix (Average Annual Loss)

Region	SCS AAL	HU AAL	EQ AAL	NC AAL	Total AAL
Region 1	450,000	30,000	15,000	600,000	1,095,000
Region 2	50,000	25,000	12,500	500,000	587,500
Region 3	300,000	20,000	160,000	400,000	880,000
Region 4	30,000	15,000	120,000	300,000	465,000
Region 5	20,000	240,000	5,000	200,000	465,000
Total	850,000	330,000	312,500	2,000,000	3,492,500

Input 2: statewise PLRS by perils

The following values are assumed given:

SCS PLR	HU PLR	EQ PLR	NC PLR
54.3%	37.6%	40.6%	62.9%

Research question

What is the best way to determine the peril mix for a given region (or perhaps for a given risk)?
Currently, we use the two-step method:
1. S tate-level AALs is provided by the commercial pricing team from their indications analysis.
We then allocate these AALs to the (within-state) region level based on assumed damage ratio relativities.

Damage Ratio = Average Annual Loss per $1000 Total Insured Value

AAL = Average Annual Loss

TIV = Total Insured Value

These relativities depend on the policies in a recent in-force book of business. Particularly in small states, our in-force book may not be credible.
Can a model-based approach help us generate more theoretically-reasonable peril mixes / capital charges?

in-force : currently in our contract historical data vs in-force modeled data vs industry modeled data we are currently doing the second we want to do the third

ISO Data pre-processing

Task

From the discussion earlier, please join Doug’s territory information onto the ISO data by zip code.

Based on the feedback from Doug yesterday, we can aggregate the ISO TOL (peril) data into two groups: CAT related & non-CAT. The types of CAT events we will be getting from the simulated data are

SCS = Severe convective storm – Wind + Hail
HU = Hurricane
EQ = Earthquake
WS = winter storm (includes freezing) In ISO, I think hurricane is included in the wind+hail peril. Figure out a grouping method that works to group TOL into CAT & Non-CAT using this information as context.

Lets produce a table that has the following fields:

Categorical
- Year
- State
- EQ Zone
- BGII Territory
- Coverage [Building Contents time element]
- Subline (maybe its labeled as SUB) [BGI BGII SCL]
- Grouped TOL [CAT vs Non-CAT]
Numeric
- Earned Exposure
- Earned Premium
- Loss+LAE

Don’t forget to add the filter for when exposures are included in the data. I will probably have more feedback after we review this data together.

Using this data, we can get the damage ratios for various segments. We will use these for the non-cat portion of the capital allocation factors.

Data preprocessing

Exposure

The “coverage” referenced in theCAF simplified example file actually represents exposure by subline (1:bg1, 2:bg2, 3:scl).
The cov variable in iso means building/content/indivisible/time element. Since the RMS data do not have this classification, we do not use cov for aggregation.

peril	subline	terr
WT	2	bg2_terr
SCS	2	scs_terr
HU	2	bg2_terr
eq	3	eq_terr
ff	3	eq_terr
non-cat	1	bg1_terr

Based on this, the exposure table to be created is:

geographical aggregation level	exposure
bg2_terr	sub=2
scs_terr	sub=2
eq_terr	sub=3
bg1_terr	sub=1

The first three rows are included solely to validate that catastrophic events are being separated correctly; they will not be used in the primary analysis.

Loss

the RMS model has EQ, FF, HU, SCS, WT (winterstorm)
In iso, we want to capture these peril, not to use them, but to exclude them and get non-cat portion.

Peril	Geographic Aggregation Level
EQ/FF	No records on ISO
HU	bg2_terr
SCS	bg2_terr
WT	bg2_terr
non-cat	bg1_terr

With/Without Exposure / Exposure Indicator

Exposure is not provided for the Time Element (Business Interruption) coverage. Additionally, certain records have had exposure removed due to data quality issues.

Validation of cat classification

the ground up loss (GU) financial perspective represents total loss to the exposure and excludes any insurance or reinsurance terms in the loss calculations.
The gross loss (GR) financial perspective represents the loss to the insurer, accounting for the application of all insurance terms but without consideration for any reinsurance recoveries.

Loss after reinsurance recoveries What the company actually retains Equivalent to

Joining

Data Source

The analysis is based on the dataset: /sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables/iso_prop_20_24.sas7bdat

Variable Selection

After creating the new categorical variables (is_CAT, CAT_type), retain the following fields for downstream analysis:

YEAR
ST
ZIPCD
BGII_Territory
COV
SUB
is_CAT
CAT_type
EARNED_PREMIUM
EARNED_EXPOSURE
TOL
EXPOSURE_VIEW
LOSS_LAE_INCURRED

ZIP Mapping

Join the main dataset with the ZIP mapping file: /sas/data/project/EG/jmun/zip_mapping.sas7bdat with join key:

ZIPCD (main dataset) = zip (mapping dataset) to add the variable:
- eq_risk_color

Creating tables

The data is noisy on the ZIPCD level. Therefore we will aggregate on a territory level. For the time being, let’s prepare two tables:

noncat, state level
wind hail hurricane

pseudocode for non-cat: create agg_iso_prop_18_22_noncat select year, ST, SUB, sum(earned_premium), sum(earned_exposure), sum(earned_premium)/ sum(earned_exposure) as damage ratio group by st, sub from /sas/data/project/EG/jmun/agg_iso_prop_18_22.sas7bdat where exposure_view = ‘with exposure’

aggregate over territory

for the scs hurrivan bg2 territory eq : eq terriorty non-cat: state

exposure separated by coverage:

swap covereage with subline

three tables

Scale comparisons between ISO and RMS

comparison in Ohio SCS

Variable	N	Mean	Std Dev	Minimum	1st Pctl	5th Pctl	25th Pctl	Median	75th Pctl	95th Pctl	99th Pctl	Maximum
iso earned_exposure	1338	464834.93	795836.04	0	45.8330000	903.0070000	11402.88	90412.69	642997.16	2019014.47	3170730.60	7744521.31
iso loss_incurred	507	90107.59	482882.05	3.0000000	321.0000000	1762.00	7500.00	21770.00	55288.00	265017.00	946214.00	9453910.00
iso damage_ratio	507	0.3786888	1.7265477	2.5143807E-6	0.000578392	0.0018185	0.0141434	0.0418223	0.1660173	1.4827292	5.6232747	31.0825358
rms tiv	1138	5242524800	7221842245	1386549.96	14425624.14	60350954.66	625804948	1956799737	7470847820	20396616531	30575829261	59773184221
rms Location_GU_AAL	1138	1258294.71	1734993.82	213.8191455	3835.15	16599.87	198519.40	523674.18	1641872.94	4804241.39	8157281.50	14463048.53
rms damage_ratio	1138	0.000285271	0.000101462	0.000049453	0.000093600	0.000130112	0.000207177	0.000277313	0.000356425	0.000463171	0.000530662	0.000649731

Ohio 2025 RMS data

obsevaion OH<AK

KA more variance

ground up and gross

iso ground up

in IL, there are observations where bgII_territory is blank. they have inignorable amount of exposures. maybe they correspond to zip code xxxxx- check it.

meeting 06-01

done so far:

recieved the model data from reinsurance group: perils are EQ+FF, HU, SCS, WT
recieved PLR by perils by state from Dan: categories are EQ, HU, SCS, WF, NC.
to ensure the non-cat part do not over- or under- represent when we compute the final peril mix, compared, for winterstorm in minnesota:
- loss and damage ratio, iso vs model

will do:

compare class/occupancy between ISO and the modeled dat

Codes

TOL Category Check 20_24

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

proc sql;
    select count(distinct TOL) as num_tol_categories
    from iso.iso_prop_20_24
    where EARNED_PREMIUM = 0 and TOL is not missing;
quit;

The TOL categories are:

TOL_All Other
TOL_Fire & Lightning
TOL_Not Available
TOL_Water Damage
TOL_Wind
TOL_Burglary & Theft
TOL_Mold
TOL_Vandalism
TOL_Freezing
TOL_Sprinkler Leakage
TOL_Collapse
TOL_Explosion
TOL_Riot & Civil Commotion
TOL_Hail

TOL Category Check 20_24

/* Assign library to the exact folder */
libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

/* Verify dataset exists and is readable */
proc contents data=iso.iso_prop_18_22;
run;

/* Your query using that dataset */
proc sql;
    select TOL,
           count(*) as N_obs
    from iso.iso_prop_18_22
    where TOL is not missing
    group by TOL
    order by N_obs desc;
quit;+

Catastrpohe classification for 20-24

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";


/* Step 1: Tag CAT_event into groups */
proc sql;
    create table work.cat_event_tagged as
    select 
        CAT_event,
        CAT_code,
        case
            when CAT_event is missing then 'Blank'
            when upcase(CAT_event) like '%NON-CATASTROPHE%' then 'Non-catastrophe'
            when upcase(CAT_event) like '%HURRICANE%' then 'Hurricane'
            when upcase(CAT_event) like '%FIRE' 
              or upcase(CAT_event) like '%FIRES' then 'Fire'
            when upcase(CAT_event) like 'RIOT%' 
              or upcase(CAT_event) like 'CIVIL%' then 'Riot/Civil'
            when upcase(CAT_event) like 'TROPICAL STORM%' then 'Tropical Storm'
            when upcase(CAT_event) like 'WIND%' 
              or upcase(CAT_event) like 'THUNDERSTORM%' 
              or upcase(CAT_event) like 'TORNADO%' then 'Wind/Thunderstorm/Tornado'
            when upcase(CAT_event) like 'WINTER STORM%' then 'Winter Storm'
            when upcase(CAT_event) like '%MUDSLIDE' then 'Mudslide'
            else 'Other'
        end as CAT_group
    from iso.iso_prop_20_24;
quit;


proc sql;
    select CAT_group,
           put(count(*), comma20.) as N_obs_format
    from work.cat_event_tagged
    group by CAT_group;
quit;

Catastrpohe classification for 18-22

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";


/* Step 1: Tag CAT_event into groups */
proc sql;
    create table work.cat_event_tagged as
    select 
        CAT_event,
        CAT_code,
        case
            when CAT_event is missing then 'Blank'
            when upcase(CAT_event) like '%NON-CATASTROPHE%' then 'Non-catastrophe'
            when upcase(CAT_event) like '%HURRICANE%' then 'Hurricane'
            when upcase(CAT_event) like '%FIRE' 
              or upcase(CAT_event) like '%FIRES' then 'Fire'
            when upcase(CAT_event) like 'RIOT%' 
              or upcase(CAT_event) like 'CIVIL%' then 'Riot/Civil'
            when upcase(CAT_event) like 'TROPICAL STORM%' then 'Tropical Storm'
            when upcase(CAT_event) like 'WIND%' 
              or upcase(CAT_event) like 'THUNDERSTORM%' 
              or upcase(CAT_event) like 'TORNADO%' then 'Wind/Thunderstorm/Tornado'
            when upcase(CAT_event) like 'WINTER STORM%' then 'Winter Storm'
            when upcase(CAT_event) like '%MUDSLIDE' then 'Mudslide'
            else 'Other'
        end as CAT_group
    from iso.iso_prop_18_22;
quit;


proc sql;
    select CAT_group,
           put(count(*), comma20.) as N_obs_format
    from work.cat_event_tagged
    group by CAT_group;
quit;

is there an earthquake?

proc sql;
    select distinct CAT_event
    from iso.iso_prop_18_22
    where CAT_code ne '0000'
          and CAT_event is not missing
          and upcase(CAT_event) like '%EARTHQUAKE%';
quit;

proc sql;
    select distinct CAT_event
    from iso.iso_prop_20_24
    where CAT_code ne '0000'
          and CAT_event is not missing
          and upcase(CAT_event) like '%EARTHQUAKE%';
quit;

tropical storm overview

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

/* view top 100. we can see that CAT_event is in the format of Tropical Storm XXX, for example, Tropical Strom Isaias */
proc sql outobs=100;
    select *
    from iso.iso_prop_20_24
    where CAT_event is not missing
          and upcase(CAT_event) like '%TROPICAL STORM%';
quit;


proc sql;
    select distinct CAT_event
    from iso.iso_prop_20_24
    where CAT_event is not missing
          and upcase(CAT_event) like '%TROPICAL STORM%'
    order by CAT_event;
quit;

corrleation between (wind or hail) and (thunderstorm and tornado)

libname iso “/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables”;

proc sql; select case when upcase(coalescec(TOL,’’)) like ‘%WIND%’ or upcase(coalescec(TOL,’’)) like ‘%HAIL%’ then 1 else 0 end as tol_flag,

    case
        when substr(upcase(coalescec(CAT_event,'')),1,4) = 'WIND'
          or substr(upcase(coalescec(CAT_event,'')),1,13) = 'THUNDERSTORM'
          or substr(upcase(coalescec(CAT_event,'')),1,7) = 'TORNADO'
        then 1 else 0
    end as cat_flag,

    count(*) as N_obs

from iso.iso_prop_18_22
where TOL is not missing
group by calculated tol_flag, calculated cat_flag
order by tol_flag, cat_flag ; quit;

proc sql; select case when upcase(coalescec(TOL,’’)) like ‘%WIND%’ or upcase(coalescec(TOL,’’)) like ‘%HAIL%’ then 1 else 0 end as tol_flag,

    case
        when substr(upcase(coalescec(CAT_event,'')),1,4) = 'WIND'
          or substr(upcase(coalescec(CAT_event,'')),1,13) = 'THUNDERSTORM'
          or substr(upcase(coalescec(CAT_event,'')),1,7) = 'TORNADO'
        then 1 else 0
    end as cat_flag,

    count(*) as N_obs

from iso.iso_prop_10_24
where TOL is not missing
group by calculated tol_flag, calculated cat_flag
order by tol_flag, cat_flag ; quit;

earned exposure 0 and positive

libname iso "/sas/data/project/EG/ActShared/ISO_DataCube/Scrubbed_DataCube_Tables";

proc sql outobs=10;
    select *
    from iso.iso_prop_18_22
    where EARNED_EXPOSURE > 0;
quit;

proc sql outobs=10;
    select *
    from iso.iso_prop_18_22
    where EARNED_EXPOSURE > 0
          and TOL is not missing;
quit;

Join

proc sql;
    create table work.grouping as
    select
        a.YEAR,
        a.ST,
        a.ZIPCD,
        a.BGII_Territory,
        a.COV,
        a.SUB,
        a.EARNED_PREMIUM,
        a.EARNED_EXPOSURE,
        a.TOL,
        a.EXPOSURE_VIEW,
        a.LOSS_LAE_INCURRED,

        b.eq_risk_color,

        case
            when upcase(coalescec(a.CAT_event,'')) like '%MUDSLIDE%' then 'Mudslide'
            when upcase(coalescec(a.CAT_event,'')) like 'RIOT%' 
              or upcase(coalescec(a.CAT_event,'')) like 'CIVIL%' then 'Civil'

            when upcase(coalescec(a.CAT_event,'')) like '%HURRICANE%' 
              or upcase(coalescec(a.CAT_event,'')) like '%TROPICAL STORM%' then 'HU'

            when upcase(coalescec(a.CAT_event,'')) like '%WINTER STORM%' then 'WS'

            when (upcase(coalescec(a.CAT_event,'')) like '%WIND%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%THUNDERSTORM%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%TORNADO%')
              or (upcase(coalescec(a.TOL,'')) like '%WIND%' 
                  or upcase(coalescec(a.TOL,'')) like '%HAIL%')
            then 'SCS'

            when upcase(coalescec(a.CAT_event,'')) like '%FIRE%' then 'Fire'

            else ''
        end as CAT_type length=10,

        case
            when upcase(coalescec(a.CAT_event,'')) like '%MUDSLIDE%' then 0
            when upcase(coalescec(a.CAT_event,'')) like 'RIOT%' 
              or upcase(coalescec(a.CAT_event,'')) like 'CIVIL%' then 0
            when a.CAT_code = '0000' then 0

            when upcase(coalescec(a.CAT_event,'')) like '%HURRICANE%' 
              or upcase(coalescec(a.CAT_event,'')) like '%TROPICAL STORM%' then 1

            when upcase(coalescec(a.CAT_event,'')) like '%WINTER STORM%' then 1

            when (upcase(coalescec(a.CAT_event,'')) like '%WIND%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%THUNDERSTORM%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%TORNADO%')
              or (upcase(coalescec(a.TOL,'')) like '%WIND%' 
                  or upcase(coalescec(a.TOL,'')) like '%HAIL%')
            then 1

            when upcase(coalescec(a.CAT_event,'')) like '%FIRE%' then 1

            else 0
        end as is_CAT

    from iso.iso_prop_20_24 as a
    left join mylib.zip_mapping as b
        on a.ZIPCD = put(b.zip, z5.)
;
quit;


proc sql;
create table mylib.agg_iso_prop_20_24 as
    select
        YEAR,
        ST,
        ZIPCD,
        BGII_Territory,
        COV,
        SUB,
        EXPOSURE_VIEW,
        TOL,
        CAT_type,
        is_CAT,
        eq_risk_color,

        sum(EARNED_PREMIUM) as EARNED_PREMIUM,
        sum(EARNED_EXPOSURE) as EARNED_EXPOSURE,
        sum(LOSS_LAE_INCURRED) as LOSS_LAE_INCURRED

    from work.grouping 

    group by
        YEAR,
        ST,
        ZIPCD,
        BGII_Territory,
        COV,
        SUB,
        EXPOSURE_VIEW,
        TOL,
        CAT_type,
        is_CAT,
        eq_risk_color
;
quit;

18-22

proc sql;
    create table work.grouping as
    select
        a.YEAR,
        a.ST,
        a.ZIPCD,
        a.BGII_Territory,
        a.COV,
        a.SUB,
        a.EARNED_PREMIUM,
        a.EARNED_EXPOSURE,
        a.TOL,
        a.EXPOSURE_VIEW,
        a.LOSS_LAE_INCURRED,

        b.eq_risk_color,

        case
            when upcase(coalescec(a.CAT_event,'')) like '%MUDSLIDE%' then 'Mudslide'
            when upcase(coalescec(a.CAT_event,'')) like 'RIOT%' 
              or upcase(coalescec(a.CAT_event,'')) like 'CIVIL%' then 'Civil'

            when upcase(coalescec(a.CAT_event,'')) like '%HURRICANE%' 
              or upcase(coalescec(a.CAT_event,'')) like '%TROPICAL STORM%' then 'HU'

            when upcase(coalescec(a.CAT_event,'')) like '%WINTER STORM%' then 'WS'

            when (upcase(coalescec(a.CAT_event,'')) like '%WIND%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%THUNDERSTORM%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%TORNADO%')
              or (upcase(coalescec(a.TOL,'')) like '%WIND%' 
                  or upcase(coalescec(a.TOL,'')) like '%HAIL%')
            then 'SCS'

            when upcase(coalescec(a.CAT_event,'')) like '%FIRE%' then 'Fire'

            else ''
        end as CAT_type length=10,

        case
            when upcase(coalescec(a.CAT_event,'')) like '%MUDSLIDE%' then 0
            when upcase(coalescec(a.CAT_event,'')) like 'RIOT%' 
              or upcase(coalescec(a.CAT_event,'')) like 'CIVIL%' then 0
            when a.CAT_code = '0000' then 0

            when upcase(coalescec(a.CAT_event,'')) like '%HURRICANE%' 
              or upcase(coalescec(a.CAT_event,'')) like '%TROPICAL STORM%' then 1

            when upcase(coalescec(a.CAT_event,'')) like '%WINTER STORM%' then 1

            when (upcase(coalescec(a.CAT_event,'')) like '%WIND%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%THUNDERSTORM%' 
                  or upcase(coalescec(a.CAT_event,'')) like '%TORNADO%')
              or (upcase(coalescec(a.TOL,'')) like '%WIND%' 
                  or upcase(coalescec(a.TOL,'')) like '%HAIL%')
            then 1

            when upcase(coalescec(a.CAT_event,'')) like '%FIRE%' then 1

            else 0
        end as is_CAT

    from iso.iso_prop_18_22 as a
    left join mylib.zip_mapping as b
        on a.ZIPCD = b.zip

;
quit;


proc sql;
create table mylib.agg_iso_prop_18_22 as
    select
        YEAR,
        ST,
        ZIPCD,
        BGII_Territory,
        COV,
        SUB,
        EXPOSURE_VIEW,
        TOL,
        CAT_type,
        is_CAT,
        eq_risk_color,

        sum(EARNED_PREMIUM) as EARNED_PREMIUM,
        sum(EARNED_EXPOSURE) as EARNED_EXPOSURE,
        sum(LOSS_LAE_INCURRED) as LOSS_LAE_INCURRED

    from work.grouping 

    group by
        YEAR,
        ST,
        ZIPCD,
        BGII_Territory,
        COV,
        SUB,
        EXPOSURE_VIEW,
        TOL,
        CAT_type,
        is_CAT,
        eq_risk_color
;
quit;

industry data statewide damage ratio

import pandas as pd
import os
import re

# ✅ Folder containing your files
folder_path = "RMS 2025 CAT"

results = []

# ✅ Loop through all CSV files
for file in os.listdir(folder_path):
    if file.endswith(".csv"):

        file_path = os.path.join(folder_path, file)

        # ✅ Extract catastrophe tag from filename
        # looks for EQ / FF / HU / SCS / WT
        match = re.search(r'\b(EQ|FF|HU|SCS|WT)\b', file)
        cat_event = match.group(1) if match else "UNKNOWN"

        print(f"Processing: {file} → {cat_event}")

        # ✅ Load data
        df = pd.read_csv(file_path)
        df.columns = df.columns.str.strip()

        # ✅ Convert numeric columns
        cols = ["TIV", "Location_GU_AAL", "Location_GR_AAL"]
        df[cols] = df[cols].apply(pd.to_numeric, errors="coerce")

        # ✅ Aggregate by state
        agg = (
            df.groupby("STATECODE", dropna=False)
              .agg(
                  TIV_sum=("TIV", "sum"),
                  GU_AAL_sum=("Location_GU_AAL", "sum"),
                  GR_AAL_sum=("Location_GR_AAL", "sum"),
              )
              .reset_index()
        )

        # ✅ Compute ratios
        agg["GU_damage_ratio"] = agg["GU_AAL_sum"] / agg["TIV_sum"]
        agg["GR_damage_ratio"] = agg["GR_AAL_sum"] / agg["TIV_sum"]

        # ✅ Add catastrophe event label
        agg["cat_event"] = cat_event

        # ✅ Store result
        results.append(agg)

# ✅ Combine all files
final_df = pd.concat(results, ignore_index=True)

# ✅ Optional: sort
final_df = final_df.sort_values(["cat_event", "GU_damage_ratio"], ascending=[True, False])

# ✅ Save output
output_path = os.path.join(folder_path, "state_damage_ratios_by_cat.csv")
final_df.to_csv(output_path, index=False)

print("Saved to:", output_path)

ISO Property Data: Loss Ratio Analysis by State and ZIP Code

mapping policy

daily

6/17

joining keys

zip code

bg1 mapping

Data

cf datamart

in-force exposure data

market segements

Peril related columns

exposure related variables

How to get non-cat exposure from

occupancy

Sea/water

Petroleum:

railroad

Parking

Personal and Repair Services

Religion and Nonprofit

Entertainment and Recreation

Electrical

Group Institutional Housing

General services

datamart do not have records

ISO DataCube™

Variables

Codes

Code for category check

Coverage categories check

Construction_Type categories check

CAT_code categories check

CAT_event categories check

Code for summary

claim row vs premium row

Geometric granularity

Task

terms

Capital Allocation Factor Workflow

Inputs

Input 1: Peril Mix (Average Annual Loss)

Input 2: statewise PLRS by perils

Research question

ISO Data pre-processing

Task

Data preprocessing

Exposure

Loss

With/Without Exposure / Exposure Indicator

Validation of cat classification

Joining

Data Source

Variable Selection

ZIP Mapping

Creating tables

Scale comparisons between ISO and RMS

Ohio 2025 RMS data

obsevaion OH<AK

meeting 06-01

Codes

TOL Category Check 20_24

TOL Category Check 20_24

Catastrpohe classification for 20-24

Catastrpohe classification for 18-22

is there an earthquake?

tropical storm overview

corrleation between (wind or hail) and (thunderstorm and tornado)

earned exposure 0 and positive

Join

18-22

industry data statewide damage ratio