Medical Large Claims Experience Study (MLCES)

Build Status Build status

The Medical Large Claims Experience Study (MLCES) might be the best private health insurance claims data available to the public. This data should be used to calibrate other data sets, and probably nothing more.

  • One table with one row per individual with nonzero total paid charges.

  • A convenience sample of group (employer-sponsored) health insurance claims from seven private health insurers in the United States.

  • 1997 thru 1999 with no expected updates in the future.

  • Provided by the Society of Actuaries (SOA).

Simplified Download and Importation

The R lodown package easily downloads and imports all available MLCES microdata by simply specifying "mlces" with an output_dir = parameter in the lodown() function. Depending on your internet connection and computer processing speed, you might prefer to run this step overnight.

library(lodown)
lodown( "mlces" , output_dir = file.path( path.expand( "~" ) , "MLCES" ) )

Analysis Examples with base R

Load a data frame:

mlces_df <- readRDS( file.path( path.expand( "~" ) , "MLCES" , "mcles1997.rds" ) )

Variable Recoding

Add new columns to the data set:

mlces_df <- 
    transform( 
        mlces_df , 
        
        claimant_relationship_to_policyholder =
            ifelse( relation == "E" , "covered employee" ,
            ifelse( relation == "S" , "spouse of covered employee" ,
            ifelse( relation == "D" , "dependent of covered employee" , NA ) ) ) ,
            
        ppo_plan = as.numeric( ppo == 'Y' )
    )

Unweighted Counts

Count the unweighted number of records in the table, overall and by groups:

nrow( mlces_df )

table( mlces_df[ , "claimant_relationship_to_policyholder" ] , useNA = "always" )

Descriptive Statistics

Calculate the mean (average) of a linear variable, overall and by groups:

mean( mlces_df[ , "totpdchg" ] )

tapply(
    mlces_df[ , "totpdchg" ] ,
    mlces_df[ , "claimant_relationship_to_policyholder" ] ,
    mean 
)

Calculate the distribution of a categorical variable, overall and by groups:

prop.table( table( mlces_df[ , "patsex" ] ) )

prop.table(
    table( mlces_df[ , c( "patsex" , "claimant_relationship_to_policyholder" ) ] ) ,
    margin = 2
)

Calculate the sum of a linear variable, overall and by groups:

sum( mlces_df[ , "totpdchg" ] )

tapply(
    mlces_df[ , "totpdchg" ] ,
    mlces_df[ , "claimant_relationship_to_policyholder" ] ,
    sum 
)

Calculate the median (50th percentile) of a linear variable, overall and by groups:

quantile( mlces_df[ , "totpdchg" ] , 0.5 )

tapply(
    mlces_df[ , "totpdchg" ] ,
    mlces_df[ , "claimant_relationship_to_policyholder" ] ,
    quantile ,
    0.5 
)

Subsetting

Limit your data.frame to persons under 18:

sub_mlces_df <- subset( mlces_df , ( ( claimyr - patbrtyr ) < 18 ) )

Calculate the mean (average) of this subset:

mean( sub_mlces_df[ , "totpdchg" ] )

Measures of Uncertainty

Calculate the variance, overall and by groups:

var( mlces_df[ , "totpdchg" ] )

tapply(
    mlces_df[ , "totpdchg" ] ,
    mlces_df[ , "claimant_relationship_to_policyholder" ] ,
    var 
)

Regression Models and Tests of Association

Perform a t-test:

t.test( totpdchg ~ ppo_plan , mlces_df )

Perform a chi-squared test of association:

this_table <- table( mlces_df[ , c( "ppo_plan" , "patsex" ) ] )

chisq.test( this_table )

Perform a generalized linear model:

glm_result <- 
    glm( 
        totpdchg ~ ppo_plan + patsex , 
        data = mlces_df
    )

summary( glm_result )

0.28 Analysis Examples with dplyr

The R dplyr library offers an alternative grammar of data manipulation to base R and SQL syntax. dplyr offers many verbs, such as summarize, group_by, and mutate, the convenience of pipe-able functions, and the tidyverse style of non-standard evaluation. This vignette details the available features. As a starting point for MLCES users, this code replicates previously-presented examples:

library(dplyr)
mlces_tbl <- tbl_df( mlces_df )

Calculate the mean (average) of a linear variable, overall and by groups:

mlces_tbl %>%
    summarize( mean = mean( totpdchg ) )

mlces_tbl %>%
    group_by( claimant_relationship_to_policyholder ) %>%
    summarize( mean = mean( totpdchg ) )