Introducing the claimsdb package for teaching and learning
In the fields of health informatics and health services research, health insurance claims data are a valuable resource to help answer questions about health care access and financing. However, claims data in the real world often contains both sensitive (protected health information) and proprietary (trade secrets) elements. For most students and educators seeking opportunities to learn how to use claims data, there are few available sources for practice.
To help with this problem, claimsdb 📦 provides easy access to a sample of health insurance enrollment and claims data from the Centers for Medicare and Medicaid Services (CMS) Data Entrepreneurs’ Synthetic Public Use File (DE-SynPUF), as a set of relational tables or as an in-memory database using DuckDB. All the data is contained within a single package, so users do not need to download any external data. This package is inspired by and based on the starwarsdb package.
The data are structured as actual Medicare claims data but are fully “synthetic,” after a process of alterations meant to reduce the risk of re-identification of real Medicare beneficiaries. The synthetic process that CMS adopted changes the co-variation across variables, so analysts should be cautious about drawing inferences about the actual Medicare population.
The data included in claimsdb comes from 500 randomly selected 2008 Medicare beneficiaries from Sample 2 of the DE-SynPUF, and it includes all the associated claims for these members for 2008-2009. CMS provides resources, including a codebook, FAQs, and other documents with more information about this data.
To introduce claimsdb, this post covers the following topics:
- Installation of the claimsdb package
- How to setup a remote database with the included data
- Examples of how to use claimsdb for analysis
- An overview of the limitations of the data included in package
Installation and components of the claimsdb package
You can install the development version of claimsdb from GitHub with:
# install.packages("devtools")
devtools::install_github("jfangmeier/claimsdb")
You can then load claimsdb alongside your other packages. We will be using the tidyverse packages in our later examples.
library(tidyverse)
library(claimsdb)
The tables are available after loading the claimsdb package. This includes a schema that describes each of the tables and the included variables from the CMS DE-SynPUF. You can see that claimsdb includes five tables. One of the tables, bene contains beneficiary records, while the others include specific types of claims data.
schema
## # A tibble: 5 x 5
## TABLE TABLE_TITLE TABLE_DESCRIPTI~ UNIT_OF_RECORD PROPERTIES
## <chr> <chr> <chr> <chr> <list>
## 1 bene CMS Beneficiary Summary~ Synthetic Medic~ Beneficiary <tibble>
## 2 carrier CMS Carrier Claims DE-S~ Synthetic physi~ claim <tibble>
## 3 inpatient CMS Inpatient Claims DE~ Synthetic inpat~ claim <tibble>
## 4 outpatient CMS Outpatient Claims D~ Synthetic outpa~ claim <tibble>
## 5 pde CMS Prescription Drug E~ Synthetic Part ~ claim <tibble>
You can access details on the variables in each of the tables like in this example with the inpatient table. You can see that this table contains 35 fields, including a beneficiary code to identify members across tables as well as detailed information on each inpatient claim.
schema %>%
filter(TABLE == "inpatient") %>%
pull(PROPERTIES)
## [[1]]
## # A tibble: 35 x 3
## VARIABLE TYPE DESCRIPTION
## <chr> <chr> <chr>
## 1 DESYNPUF_ID string Beneficiary Code
## 2 CLM_ID string Claim ID
## 3 SEGMENT numeric Claim Line Segment
## 4 CLM_FROM_DT date Claims start date
## 5 CLM_THRU_DT date Claims end date
## 6 PRVDR_NUM string Provider Institution
## 7 CLM_PMT_AMT numeric Claim Payment Amount
## 8 NCH_PRMRY_PYR_CLM_PD_AMT numeric NCH Primary Payer Claim Paid Amount
## 9 AT_PHYSN_NPI string Attending Physician National Provider Ident~
## 10 OP_PHYSN_NPI string Operating Physician National Provider Ident~
## # ... with 25 more rows
Access claims data as a remote database
Many organizations store claims data in a remote database, so claimsdb also includes all of the tables as an in-memory DuckDB database. This can be a great way to practice working with this type of data, including building queries with dplyr code using dbplyr.
To setup the in-memory database, you need to create a database connection using claims_connect() and create connections to each of the tables you want to use.
library(dbplyr)
# Setup connection to duckDB database
con <- claims_connect()
# Setup connections to each of the enrollment and claims tables in the database
bene_rmt <- tbl(con, "bene")
inpatient_rmt <- tbl(con, "inpatient")
outpatient_rmt <- tbl(con, "outpatient")
carrier_rmt <- tbl(con, "carrier")
pde_rmt <- tbl(con, "pde")
You can then preview your connection to each of the remote tables.
# Preview the prescription drug event table in the database
pde_rmt
## # Source: table<pde> [?? x 8]
## # Database: duckdb_connection
## DESYNPUF_ID PDE_ID SRVC_DT PROD_SRVC_ID QTY_DSPNSD_NUM DAYS_SUPLY_NUM
## <chr> <chr> <date> <chr> <dbl> <dbl>
## 1 00E040C6ECE8F878 83014~ 2008-12-20 49288010404 30 30
## 2 00E040C6ECE8F878 83594~ 2009-04-25 52959037200 20 30
## 3 00E040C6ECE8F878 83144~ 2009-09-22 00083400141 80 30
## 4 00E040C6ECE8F878 83614~ 2009-10-03 63481043301 60 10
## 5 00E040C6ECE8F878 83014~ 2009-11-16 51129404301 60 30
## 6 00E040C6ECE8F878 83234~ 2009-12-11 58016096489 270 30
## 7 014F2C07689C173B 83294~ 2009-09-14 00904582062 90 30
## 8 014F2C07689C173B 83874~ 2009-10-11 59604053055 40 20
## 9 014F2C07689C173B 83314~ 2009-11-24 51079017760 30 30
## 10 014F2C07689C173B 83874~ 2009-12-29 51129370802 30 30
## # ... with more rows, and 2 more variables: PTNT_PAY_AMT <dbl>,
## # TOT_RX_CST_AMT <dbl>
Examples of using claimsdb for analysis
To analyze and explore the claims and beneficiary data, you can execute your own SQL code on the database using the DBI package.
DBI::dbGetQuery(
con,
paste0(
'SELECT "DESYNPUF_ID", "BENE_BIRTH_DT"',
'FROM "bene"',
'WHERE "BENE_SEX_IDENT_CD" = 1.0',
'LIMIT 10'
)
)
## DESYNPUF_ID BENE_BIRTH_DT
## 1 001115EAB83B19BB 1939-12-01
## 2 03ADA78C0FEF79F4 1923-02-01
## 3 040A12AB5EAA444C 1943-10-01
## 4 0507DE00BC6E6CD6 1932-07-01
## 5 05672CCCED56BCAD 1937-07-01
## 6 060CDE3A044F64BA 1943-02-01
## 7 061B5B3D9A459675 1932-04-01
## 8 08C8E0A0C6EAC884 1954-06-01
## 9 09EEB5C4C4FAEF10 1935-04-01
## 10 0A58C6D6B9BE67CF 1942-07-01
However, in the following examples, we will use the dbplyr package which translates dplyr code into SQL that can be executed against the database. You can see that the results below with dplyr functions match the results above that used a SQL query.
bene_rmt %>%
filter(BENE_SEX_IDENT_CD == 1) %>%
select(DESYNPUF_ID, BENE_BIRTH_DT)
## # Source: lazy query [?? x 2]
## # Database: duckdb_connection
## DESYNPUF_ID BENE_BIRTH_DT
## <chr> <date>
## 1 001115EAB83B19BB 1939-12-01
## 2 03ADA78C0FEF79F4 1923-02-01
## 3 040A12AB5EAA444C 1943-10-01
## 4 0507DE00BC6E6CD6 1932-07-01
## 5 05672CCCED56BCAD 1937-07-01
## 6 060CDE3A044F64BA 1943-02-01
## 7 061B5B3D9A459675 1932-04-01
## 8 08C8E0A0C6EAC884 1954-06-01
## 9 09EEB5C4C4FAEF10 1935-04-01
## 10 0A58C6D6B9BE67CF 1942-07-01
## # ... with more rows
We can use the show_query() function to see the SQL code that dbplyr created and that it closely matches the SQL code above.
bene_rmt %>%
filter(BENE_SEX_IDENT_CD == 1) %>%
select(DESYNPUF_ID, BENE_BIRTH_DT) %>%
show_query()
## <SQL>
## SELECT "DESYNPUF_ID", "BENE_BIRTH_DT"
## FROM "bene"
## WHERE ("BENE_SEX_IDENT_CD" = 1.0)
First, a note about working with dates/times in databases
dbplyr is an amazing tool for working with databases, especially if you want to use many functions from the dplyr and tidyr packages. However, it does not currently have SQL translations for all functions in the tidyverse family of packages. For example, the lubridate package’s date and time functions work on local dataframes but cannot be translated to work on remote tables at this time. In the example below, you can see that the lubridate function year() (for parsing the year from a date) works on the local dataframe but generates an error on the remote table with the same data.
bene %>%
transmute(
DESYNPUF_ID,
BENE_BIRTH_DT,
BIRTH_YEAR = lubridate::year(BENE_BIRTH_DT)
)
## # A tibble: 998 x 3
## DESYNPUF_ID BENE_BIRTH_DT BIRTH_YEAR
## <chr> <date> <dbl>
## 1 001115EAB83B19BB 1939-12-01 1939
## 2 0018A1975BC0EE4F 1935-11-01 1935
## 3 00E040C6ECE8F878 1932-11-01 1932
## 4 014F2C07689C173B 1935-07-01 1935
## 5 029A22E4A3AAEE15 1921-05-01 1921
## 6 03410742A11DDC52 1943-07-01 1943
## 7 03ADA78C0FEF79F4 1923-02-01 1923
## 8 040A12AB5EAA444C 1943-10-01 1943
## 9 043AAAE41C9A37B7 1924-03-01 1924
## 10 0507DE00BC6E6CD6 1932-07-01 1932
## # ... with 988 more rows
bene_rmt %>%
transmute(
DESYNPUF_ID,
BENE_BIRTH_DT,
BIRTH_YEAR = lubridate::year(BENE_BIRTH_DT)
)
## Error in lubridate::year(BENE_BIRTH_DT): object 'BENE_BIRTH_DT' not found
Fortunately, dbplyr allows you pass along SQL functions in your dplyr code, and it will include these functions in the generated query. For date/time functions, we need to consult the documentation from DuckDB on date functions. To parse a part of a date (e.g., the year), we need to use the date_part() function for DuckDB.
The function to do this task may vary across database backends, so if you are doing this with a different database (Oracle, SQL Server, etc.), you will need to read the documentation for that database management system.
bene_rmt %>%
transmute(
DESYNPUF_ID,
BENE_BIRTH_DT,
BIRTH_YEAR = date_part('year', BENE_BIRTH_DT)
)
## # Source: lazy query [?? x 3]
## # Database: duckdb_connection
## DESYNPUF_ID BENE_BIRTH_DT BIRTH_YEAR
## <chr> <date> <dbl>
## 1 001115EAB83B19BB 1939-12-01 1939
## 2 0018A1975BC0EE4F 1935-11-01 1935
## 3 00E040C6ECE8F878 1932-11-01 1932
## 4 014F2C07689C173B 1935-07-01 1935
## 5 029A22E4A3AAEE15 1921-05-01 1921
## 6 03410742A11DDC52 1943-07-01 1943
## 7 03ADA78C0FEF79F4 1923-02-01 1923
## 8 040A12AB5EAA444C 1943-10-01 1943
## 9 043AAAE41C9A37B7 1924-03-01 1924
## 10 0507DE00BC6E6CD6 1932-07-01 1932
## # ... with more rows
Example 1: which members had the highest prescription drug costs for 2008?
For this first example, we are going to identify the beneficiaries with the highest total prescription drug costs in 2008. We need to use the pde table that has claims on prescription drug events and the bene table that has beneficiary records. We create an object that is the aggregated costs for prescription drugs at the beneficiary level in 2008. Note that we had to use date_part() to parse the year from the service date.
# Calculate rx costs for utilizing members in 2008
rx_costs_rmt <- pde_rmt %>%
mutate(BENE_YEAR = date_part('year', SRVC_DT)) %>%
filter(BENE_YEAR == 2008) %>%
group_by(BENE_YEAR, DESYNPUF_ID) %>%
summarize(TOTAL_RX_COST = sum(TOT_RX_CST_AMT, na.rm = T), .groups = "drop") %>%
ungroup()
rx_costs_rmt
## # Source: lazy query [?? x 3]
## # Database: duckdb_connection
## BENE_YEAR DESYNPUF_ID TOTAL_RX_COST
## <dbl> <chr> <dbl>
## 1 2008 00E040C6ECE8F878 10
## 2 2008 03ADA78C0FEF79F4 5020
## 3 2008 040A12AB5EAA444C 120
## 4 2008 043AAAE41C9A37B7 810
## 5 2008 05672CCCED56BCAD 0
## 6 2008 061B5B3D9A459675 270
## 7 2008 08BB74BA9DFD5C06 20
## 8 2008 08C8E0A0C6EAC884 5860
## 9 2008 09EEB5C4C4FAEF10 1590
## 10 2008 0A58C6D6B9BE67CF 930
## # ... with more rows
Then we join the aggregated cost data to the beneficiary table. This is necessary because the pde table does not include beneficiaries who didn’t use any prescription drugs. After joining the table we reassign missing cost data to zero for those beneficiaries with no utilization. We can then use collect() to retrieve the results as a local dataframe.
# Join the rx costs data to the beneficiary file and include members with no costs
rx_bene_rmt <- bene_rmt %>%
filter(BENE_YEAR == 2008) %>%
select(
BENE_YEAR,
DESYNPUF_ID
) %>%
left_join(
rx_costs_rmt, by = c("BENE_YEAR", "DESYNPUF_ID")
) %>%
mutate(TOTAL_RX_COST = ifelse(is.na(TOTAL_RX_COST), 0, TOTAL_RX_COST)) %>%
arrange(desc(TOTAL_RX_COST))
rx_bene_rmt
## # Source: lazy query [?? x 3]
## # Database: duckdb_connection
## # Ordered by: desc(TOTAL_RX_COST)
## BENE_YEAR DESYNPUF_ID TOTAL_RX_COST
## <dbl> <chr> <dbl>
## 1 2008 484FAAB99C7F90F2 10410
## 2 2008 1187122DCAD04B48 9300
## 3 2008 5199E41C3B2B36AD 9110
## 4 2008 801D1702FC0C105F 8210
## 5 2008 92158DA24D7A8799 7910
## 6 2008 A705B8508EB5747D 7380
## 7 2008 A9142A5D9895479C 6780
## 8 2008 D20C21DD9F8177D0 6590
## 9 2008 08C8E0A0C6EAC884 5860
## 10 2008 EBE9CC1D92AA56C0 5690
## # ... with more rows
# You can use collect() to bring the results into a local dataframe
rx_bene_df <- rx_bene_rmt %>%
collect()
rx_bene_df
## # A tibble: 500 x 3
## BENE_YEAR DESYNPUF_ID TOTAL_RX_COST
## <dbl> <chr> <dbl>
## 1 2008 484FAAB99C7F90F2 10410
## 2 2008 1187122DCAD04B48 9300
## 3 2008 5199E41C3B2B36AD 9110
## 4 2008 801D1702FC0C105F 8210
## 5 2008 92158DA24D7A8799 7910
## 6 2008 A705B8508EB5747D 7380
## 7 2008 A9142A5D9895479C 6780
## 8 2008 D20C21DD9F8177D0 6590
## 9 2008 08C8E0A0C6EAC884 5860
## 10 2008 EBE9CC1D92AA56C0 5690
## # ... with 490 more rows
Example 2: what percent of beneficiaries received an office visit within 30 days of discharge from a hospital?
In the next example, we are identifying which beneficiaries had an office visit within 30 days of being discharged. We will start with the inpatient table that contains records for all inpatient stays, including when a beneficiary was discharged. We create an object that includes the beneficiary ID, the discharge date, and the date 30 days after discharge. Note that for DuckDB we need to coerce “30” to an integer to calculate the new date.
# Identify all member discharge dates and the dates 30 days after discharge from the inpatient table
ip_discharges <- inpatient_rmt %>%
transmute(
DESYNPUF_ID,
DSCHRG_DT = NCH_BENE_DSCHRG_DT,
DSCHRG_DT_30 = NCH_BENE_DSCHRG_DT + as.integer(30)) %>%
distinct()
ip_discharges
## # Source: lazy query [?? x 3]
## # Database: duckdb_connection
## DESYNPUF_ID DSCHRG_DT DSCHRG_DT_30
## <chr> <date> <date>
## 1 014F2C07689C173B 2009-09-20 2009-10-20
## 2 029A22E4A3AAEE15 2008-01-25 2008-02-24
## 3 060CDE3A044F64BA 2008-10-18 2008-11-17
## 4 08BB74BA9DFD5C06 2009-03-07 2009-04-06
## 5 08BB74BA9DFD5C06 2009-03-08 2009-04-07
## 6 08C8E0A0C6EAC884 2008-02-22 2008-03-23
## 7 08C8E0A0C6EAC884 2009-05-28 2009-06-27
## 8 08C8E0A0C6EAC884 2009-06-14 2009-07-14
## 9 08C8E0A0C6EAC884 2009-07-07 2009-08-06
## 10 08C8E0A0C6EAC884 2009-08-23 2009-09-22
## # ... with more rows
Next, we need to identify office visits from the carrier table. I created a vector of five office visit codes for this example. Since these codes must match the values in the “HCPCS” columns, we reshape the table with pivot_longer() then filter for the office visit codes.
# Create vector of office visit codes
off_vis_cds <- as.character(99211:99215)
# Identify members who had an office visit from the carrier table
off_visit <- carrier_rmt %>%
select(DESYNPUF_ID, CLM_ID, CLM_FROM_DT, matches("HCPCS")) %>%
pivot_longer(cols = matches("HCPCS"), names_to = "LINE", values_to = "HCPCS") %>%
filter(HCPCS %in% off_vis_cds) %>%
distinct(DESYNPUF_ID, CLM_FROM_DT) %>%
mutate(OFFICE_VISIT = 1)
off_visit
## # Source: lazy query [?? x 3]
## # Database: duckdb_connection
## DESYNPUF_ID CLM_FROM_DT OFFICE_VISIT
## <chr> <date> <dbl>
## 1 00E040C6ECE8F878 2009-01-17 1
## 2 00E040C6ECE8F878 2009-07-20 1
## 3 00E040C6ECE8F878 2009-07-26 1
## 4 029A22E4A3AAEE15 2008-01-25 1
## 5 029A22E4A3AAEE15 2008-01-30 1
## 6 029A22E4A3AAEE15 2008-08-28 1
## 7 029A22E4A3AAEE15 2008-11-19 1
## 8 029A22E4A3AAEE15 2009-04-01 1
## 9 029A22E4A3AAEE15 2009-05-13 1
## 10 029A22E4A3AAEE15 2009-10-02 1
## # ... with more rows
Finally, we join the office visits object to the discharges object. We can use the sql_on option in left_join() to inject some custom SQL to join when the office visit date is within 30 days of the discharge date.
# Join discharges data and office visit data
discharge_offvis <-
ip_discharges %>%
left_join(
off_visit,
sql_on = paste0(
"(LHS.DESYNPUF_ID = RHS.DESYNPUF_ID) AND",
"(RHS.CLM_FROM_DT >= LHS.DSCHRG_DT) AND",
"(RHS.CLM_FROM_DT <= LHS.DSCHRG_DT_30)"
)
)
discharge_offvis
## # Source: lazy query [?? x 6]
## # Database: duckdb_connection
## DESYNPUF_ID.x DSCHRG_DT DSCHRG_DT_30 DESYNPUF_ID.y CLM_FROM_DT OFFICE_VISIT
## <chr> <date> <date> <chr> <date> <dbl>
## 1 029A22E4A3AAE~ 2008-01-25 2008-02-24 029A22E4A3AA~ 2008-01-25 1
## 2 029A22E4A3AAE~ 2008-01-25 2008-02-24 029A22E4A3AA~ 2008-01-30 1
## 3 060CDE3A044F6~ 2008-10-18 2008-11-17 060CDE3A044F~ 2008-11-01 1
## 4 1187122DCAD04~ 2009-04-06 2009-05-06 1187122DCAD0~ 2009-04-07 1
## 5 1187122DCAD04~ 2009-04-06 2009-05-06 1187122DCAD0~ 2009-04-27 1
## 6 1187122DCAD04~ 2009-04-06 2009-05-06 1187122DCAD0~ 2009-05-04 1
## 7 12D6FF0C18764~ 2009-05-08 2009-06-07 12D6FF0C1876~ 2009-05-19 1
## 8 12D6FF0C18764~ 2009-05-08 2009-06-07 12D6FF0C1876~ 2009-05-24 1
## 9 144C187653FBB~ 2008-06-08 2008-07-08 144C187653FB~ 2008-07-04 1
## 10 1E14EA81B43B5~ 2009-12-06 2010-01-05 1E14EA81B43B~ 2009-12-20 1
## # ... with more rows
After the join is complete, we can calculate the share of discharges with a timely office visit.
# Find the percent of members with a office visit within 30 days of discharge
discharge_offvis %>%
distinct(
DESYNPUF_ID.x,
DSCHRG_DT,
OFFICE_VISIT
) %>%
mutate(OFFICE_VISIT = ifelse(is.na(OFFICE_VISIT), 0, OFFICE_VISIT)) %>%
summarize(
OFV_RATE = mean(OFFICE_VISIT, na.rm = T)
)
## # Source: lazy query [?? x 1]
## # Database: duckdb_connection
## OFV_RATE
## <dbl>
## 1 0.540
Example 3: how well are beneficiaries filling their hypertension drug prescriptions?
For this final example, we need to identify hypertension medications from the pde table and calculate medication adherence rates for each beneficiary. To isolate the hypertension medications, we can borrow from the HEDIS medication list for ACE inhibitors and ARB medications (which are commonly used to treat hypertension). This medication list is for 2018/2019, so it likely includes new drugs that did not exist in 2008/2009 and may not include older drugs that are no longer used (ideally it’s best to use external lists that match the time frame of the claims data).
hedis_wb <- tempfile()
download.file(
url = "https://www.ncqa.org/hedis-2019-ndc-mld-directory-complete-workbook-final-11-1-2018-3/",
destfile = hedis_wb,
mode = "wb"
)
hedis_df <- readxl::read_excel(
path = hedis_wb,
sheet = "Medications List to NDC Codes"
)
hyp_ndc_df <-
hedis_df %>%
filter(`Medication List` == "ACE Inhibitor/ARB Medications") %>%
select(
LIST = `Medication List`,
PRODUCTID = `NDC Code`
)
hyp_ndc_df
## # A tibble: 3,230 x 2
## LIST PRODUCTID
## <chr> <chr>
## 1 ACE Inhibitor/ARB Medications 00093512501
## 2 ACE Inhibitor/ARB Medications 00093512505
## 3 ACE Inhibitor/ARB Medications 00185005301
## 4 ACE Inhibitor/ARB Medications 00185005305
## 5 ACE Inhibitor/ARB Medications 00247213730
## 6 ACE Inhibitor/ARB Medications 00378044301
## 7 ACE Inhibitor/ARB Medications 00781189201
## 8 ACE Inhibitor/ARB Medications 13811062810
## 9 ACE Inhibitor/ARB Medications 13811062850
## 10 ACE Inhibitor/ARB Medications 21695032630
## # ... with 3,220 more rows
One of the cool features of dbplyr is that we can copy this local dataframe to the database as a temporary table using the copy_to() function.
hyp_ndc_rmt <- copy_to(con, hyp_ndc_df, overwrite = T)
hyp_ndc_rmt
## # Source: table<hyp_ndc_df> [?? x 2]
## # Database: duckdb_connection
## LIST PRODUCTID
## <chr> <chr>
## 1 ACE Inhibitor/ARB Medications 00093512501
## 2 ACE Inhibitor/ARB Medications 00093512505
## 3 ACE Inhibitor/ARB Medications 00185005301
## 4 ACE Inhibitor/ARB Medications 00185005305
## 5 ACE Inhibitor/ARB Medications 00247213730
## 6 ACE Inhibitor/ARB Medications 00378044301
## 7 ACE Inhibitor/ARB Medications 00781189201
## 8 ACE Inhibitor/ARB Medications 13811062810
## 9 ACE Inhibitor/ARB Medications 13811062850
## 10 ACE Inhibitor/ARB Medications 21695032630
## # ... with more rows
With the hypertension codes in the database, we can use inner_join() to find the matching drugs from the pde table.
pde_hyp_rmt <-
pde_rmt %>%
inner_join(
hyp_ndc_rmt, by = c("PROD_SRVC_ID" = "PRODUCTID")
)
pde_hyp_rmt
## # Source: lazy query [?? x 9]
## # Database: duckdb_connection
## DESYNPUF_ID PDE_ID SRVC_DT PROD_SRVC_ID QTY_DSPNSD_NUM DAYS_SUPLY_NUM
## <chr> <chr> <date> <chr> <dbl> <dbl>
## 1 03ADA78C0FEF79F4 83554~ 2008-02-01 51079098420 30 30
## 2 040A12AB5EAA444C 83974~ 2009-11-27 63874037930 90 30
## 3 043AAAE41C9A37B7 83024~ 2008-08-11 00247228590 200 90
## 4 0D540BEBC45ADCA7 83454~ 2009-02-18 60429007210 60 90
## 5 0D540BEBC45ADCA7 83874~ 2009-06-15 66105010309 30 30
## 6 0E0A0107787B32AA 83714~ 2008-06-30 66105050406 100 90
## 7 109D67D114778917 83064~ 2008-11-21 13411014302 30 60
## 8 109D67D114778917 83034~ 2009-07-03 66685070203 30 30
## 9 10D75CDD5B4AD3B0 83804~ 2008-07-01 00247202160 90 90
## 10 1183CA4884F8A0A8 83934~ 2009-01-25 00185002501 90 20
## # ... with more rows, and 3 more variables: PTNT_PAY_AMT <dbl>,
## # TOT_RX_CST_AMT <dbl>, LIST <chr>
We can now use collect() to retrieve the data as a local dataframe. As a dataframe, we can now use the AdhereR package to calculate the medication possession ratio (MPR) for members who filled a hypertension medication. MPR is a commonly used metric of medication adherence, measuring if beneficiaries have gaps between their prescription fills.
We can see the MPR for each member who filled one of the medications, and we can calculate the median and mean MPRs across these beneficiaries.
# install.packages("AdhereR")
library(AdhereR)
pde_hyp_df <- pde_hyp_rmt %>%
collect()
hyp_adhere <- CMA4(
data = pde_hyp_df,
ID.colname = 'DESYNPUF_ID',
event.date.colname = 'SRVC_DT',
event.duration.colname = 'DAYS_SUPLY_NUM',
date.format = "%Y-%m-%d"
)
hyp_adhere_cma <- hyp_adhere$CMA %>% tibble()
hyp_adhere_cma
## # A tibble: 134 x 2
## DESYNPUF_ID CMA
## <chr> <dbl>
## 1 03ADA78C0FEF79F4 0.0411
## 2 040A12AB5EAA444C 0.0411
## 3 043AAAE41C9A37B7 0.123
## 4 0D540BEBC45ADCA7 0.164
## 5 0E0A0107787B32AA 0.123
## 6 109D67D114778917 0.123
## 7 10D75CDD5B4AD3B0 0.123
## 8 1183CA4884F8A0A8 0.0274
## 9 11C45CF0DDD03ACE 0.0822
## 10 1226AD9944384B64 0.0411
## # ... with 124 more rows
hyp_adhere_cma %>%
summarize(
median_mpr = median(CMA),
mean_mpr = mean(CMA)
)
## # A tibble: 1 x 2
## median_mpr mean_mpr
## <dbl> <dbl>
## 1 0.0411 0.0848
Data Limitations
While data included in claimsdb is useful for many types of analyses, it does include a few notable limitations.
- As mentioned earlier, the data is a small sample (500 beneficiaries) and is not intended to be representative of the Medicare population. In addition, the data is synthetic and should not be used for drawing inferences on the Medicare population.
- Since the data is more than 10 years old, it doesn’t capture newer medications or procedures. It also includes procedure codes that have been retired or replaced. This is a challenge when applying external code lists that are much newer.
- The diagnosis fields in the data use the International Classification of Diseases, Ninth Revision (ICD-9), but the United States converted to ICD-10 in 2015. If you are interesting in a mapping between ICD-9 and ICD-10, CMS has resources to consider.
- The Medicare population is mostly Americans aged 65 and over, so the data will not have claims on certain specialties such as pediatrics or maternity care.