Method to extract sequential events for a defined population in MS SQL via dbplyr / purrr / R

purrr_map_multiple_event_extract.R

# con is a connection object

#Package load
pacman::p_load(tidyverse, tidylog, janitor, pool,odbc, dbplyr)


dbConnect(
  odbc(),
  Driver = "SQL Server",
  Server = "server name",
  Database = "database name",
  Trusted_Connection = "True" # we have windows user leve authentication
)

with_no_lock <- function(tbl) {
  return(sql(paste('SELECT * FROM', tbl ,'WITH (NOLOCK)')))
}

sql_ce_table <- tbl(con, with_no_lock('name_of_clin_event_table'))

#I create a list of values that I wish to extract. 

clin_events = c(
  'Blood Glucose',
  'Blood Glucose, Capillary',
  'Blood Glucose - POC',
  'Pain Score Numerical',
  'Peripheral Pulse Rate',
  'Pulse Rate',
  'Apical Heart Rate',
  'Heart Rate Monitored',
  'Respiratory Rate',
  'Lying Systolic Blood Pressure',
  'Systolic Blood Pressure',
  'Systolic Blood Pressure Invasive',
  'Systolic Blood Pressure Sitting',
  'Systolic Blood Pressure Standing',
  'ED Temperature Axillary Calc',
  'ED Temperature Oral Calc',
  'ED Temperature Tympanic Calc',
  'Temperature Axillary',
  'Temperature Oral',
  'Temperature Skin',
  'Temperature Tympanic',
  'SaO2 In Oxygen',
  'Sp02',
  'Diastolic Blood Pressure',
  'Diastolic Blood Pressure Invasive',
  'Diastolic Blood Pressure Sitting',
  'Diastolic Blood Pressure Standing',
  'Lying Diastolic Blood Pressure'
)

#I use this list to create a list of event codes through a lookup in the event table -> event_codes$event_code

# map then takes event code list along with a a population list to 
# extract each event in turn for a population identified through another query

ce <- map(x = event_codes$clinical_event, function(x)
  sql_ce_table %>%
    filter(ENCNTR_ID %in% local(pt_pop$encounter_id) & EVENT_CD == x) %>%
    select(
      EVENT_ID,
      ENCNTR_ID,
      EVENT_CD,
      EVENT_RESULT_TXT
      # other vars that I need for my analysis
    ) %>%
    collect() %>%
    clean_names()
) %>%
  reduce(rbind)