The goal of ‘healthdb’ is to provide a set of tools for identifying diseases or events from healthcare database and preparing data for epidemiological studies. It features abilities that are not natively support by database, such as matching strings by ‘stringr’ style regular expression and using ‘LIKE’ operator with multiple patterns in a vector. Three types of functions are included: interactive functions – for customizing complex definitions; call building functions – for batch execution of simple definition; miscellaneous functions – for data wrangling, computing age and comorbidity index, etc.
The package is tested only on SQL Server and SQLite as we do not have access to other SQL dialects. Please report bugs if you encounter issues with other dialects.
Administrative health data are often stored on SQL database with strict security measures which may disable permission to write temporary tables. Writing queries without being able to cache intermediate results is challenging, especially when the data is too large to be downloaded from database into R (i.e., local memory) without some filtering process.
This package leverages ‘dbplyr’, particularly its ability to chain subqueries, in order to implement a common disease definition as a one-shot big query. Outputs are fully compatible with ‘dplyr’ functions.
Common disease definitions often are in the form of having n primary care/hospitalization/prescription records with some International Classification of Diseases (ICD) codes within some time span. See below for an example of implementing such case definition.
Install from CRAN:
You could also install the development version from GitHub with:
We are going to implement the following case definition:
One or more hospitalization with a substance use disorder (SUD) ICD-9 diagnostic code, OR Two or more physician claims with a substance use disorder ICD-10 diagnostic code within one year.
Before we get started, please see how to connect to a database and how to write query with ‘dbplyr’ if you don’t have experience of working with database in R.
First, let’s make a demo data sets for the two sources:
Physician claims
library(healthdb)
library(tidyverse)
# make_test_dat() makes either a toy data.frame or database table in memory with known number of rows that satisfy the query we will show later
claim_db <- make_test_dat(vals_kept = c("303", "304", "305", "291", "292", str_glue("30{30:59}"), str_glue("29{10:29}"), noise_val = c("999", "111")), type = "database")
# this is a database table
# note that in-memory SQLite database stores dates as numbers
claim_db %>% head()
#> # Source: SQL [6 x 6]
#> # Database: sqlite 3.45.2 [:memory:]
#> uid clnt_id dates diagx diagx_1 diagx_2
#> <int> <int> <dbl> <chr> <chr> <chr>
#> 1 87 1 17740 999 <NA> 999
#> 2 66 1 18375 999 999 999
#> 3 2 2 18546 2920 3041 999
#> 4 21 3 17345 2917 2916 999
#> 5 28 3 18167 111 3035 <NA>
#> 6 92 3 18528 999 999 999Hospitalization
hosp_df <- make_test_dat(vals_kept = c(str_glue("F{10:19}"), str_glue("F{100:199}"), noise_val = "999"), type = "data.frame")
# this is a local data.frame/tibble
hosp_df %>% head()
#> uid clnt_id dates diagx diagx_1 diagx_2
#> 1 57 1 2020-09-19 999 <NA> <NA>
#> 2 91 2 2015-02-15 999 <NA> 999
#> 3 92 2 2016-09-03 999 999 999
#> 4 66 2 2018-07-02 999 999 999
#> 5 89 2 2018-11-03 999 999 <NA>
#> 6 62 2 2019-03-14 999 <NA> 999Here’s how you could use healthdb to implement the SUD definition above:
Identify rows contains the target codes in the claim database
result1 <- claim_db %>%
identify_row(
vars = starts_with("diagx"),
match = "start",
vals = c(291:292, 303:305)
)
#> ℹ Identify records with condition(s):
#> • where at least one of the diagx, diagx_1, diagx_2 column(s) in each record
#> • contains a value satisfied SQL LIKE pattern: 291% OR 292% OR 303% OR 304% OR 305%
#> ℹ To see the final query generated by 'dbplyr', use dplyr::show_query() on the output.
#> To extract the SQL string, use dbplyr::remote_query().Restrict the number of records per client
result2 <- result1 %>% restrict_n(
clnt_id = clnt_id,
n_per_clnt = 2,
count_by = dates,
# here we use filter mode to remove records that failed the restriction
mode = "filter"
)
#> ℹ Apply restriction that each client must have at least 2 records with distinct
#> dates. Clients/groups which did not met the condition were excluded.
result2 %>% head()
#> # Source: SQL [6 x 7]
#> # Database: sqlite 3.45.2 [:memory:]
#> # Ordered by: dates
#> uid clnt_id dates diagx diagx_1 diagx_2 flag_restrict_n
#> <int> <int> <dbl> <chr> <chr> <chr> <int>
#> 1 21 3 17345 2917 2916 999 1
#> 2 28 3 18167 111 3035 <NA> 1
#> 3 35 4 16955 3038 3033 999 1
#> 4 11 4 18473 3040 2920 999 1
#> 5 36 11 16752 3047 3047 999 1
#> 6 12 11 17752 3046 3057 999 1Restrict the temporal pattern of diagnoses
result3 <- result2 %>% restrict_date(
clnt_id = clnt_id,
date_var = dates,
n = 2,
within = 365,
uid = uid,
# here we use flag mode to flag records that met the restriction instead of removing those
mode = "flag"
)
#> ℹ Apply restriction that each client must have 2 records that were within 365
#> days. Records that met the condition were flagged.
result3 %>% head()
#> # Source: SQL [6 x 8]
#> # Database: sqlite 3.45.2 [:memory:]
#> # Ordered by: dates, uid
#> uid clnt_id dates diagx diagx_1 diagx_2 flag_restrict_n flag_restrict_date
#> <int> <int> <dbl> <chr> <chr> <chr> <int> <int>
#> 1 21 3 17345 2917 2916 999 1 0
#> 2 28 3 18167 111 3035 <NA> 1 0
#> 3 35 4 16955 3038 3033 999 1 0
#> 4 11 4 18473 3040 2920 999 1 0
#> 5 36 11 16752 3047 3047 999 1 0
#> 6 12 11 17752 3046 3057 999 1 1Repeat these steps for hospitalization and row bind the results.
The output of these functions, including identify_row(), exclude(), restrict_n(), restrict_date() and more, can be piped into ‘dplyr’ functions for further manipulations. Therefore, wrangling with them along with ‘dplyr’ provide the maximum flexibility for implementing complex algorithms. However, your code could look repetitive if multiple data sources were involved. See the introduction vignette (vignette("healthdb")) for a much more concise way to work with multiple sources and definitions (the ‘Call-building functions’ section).