Lab 3 - Key

Load packages and connect to the De-ID CDW

rm(list=ls()) # clean environment

# load necessary packages
require("odbc")

## Loading required package: odbc

## Warning: package 'odbc' was built under R version 4.0.5

## Warning: replacing previous import 'lifecycle::last_warnings' by
## 'rlang::last_warnings' when loading 'hms'

require("DBI")

## Loading required package: DBI

## Warning: package 'DBI' was built under R version 4.0.5

require("dbplyr")

## Loading required package: dbplyr

## Warning: package 'dbplyr' was built under R version 4.0.5

## Warning: replacing previous import 'lifecycle::last_warnings' by
## 'rlang::last_warnings' when loading 'tibble'

## Warning: replacing previous import 'lifecycle::last_warnings' by
## 'rlang::last_warnings' when loading 'pillar'

require("tidyverse")

## Loading required package: tidyverse

## Warning: package 'tidyverse' was built under R version 4.0.5

## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --

## v ggplot2 3.3.5     v purrr   0.3.4
## v tibble  3.1.0     v dplyr   1.0.5
## v tidyr   1.1.3     v stringr 1.4.0
## v readr   1.4.0     v forcats 0.5.1

## Warning: package 'ggplot2' was built under R version 4.0.5

## Warning: package 'tidyr' was built under R version 4.0.5

## Warning: package 'stringr' was built under R version 4.0.5

## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::ident()  masks dbplyr::ident()
## x dplyr::lag()    masks stats::lag()
## x dplyr::sql()    masks dbplyr::sql()

require("knitr")

## Loading required package: knitr

## Warning: package 'knitr' was built under R version 4.0.5

require("DT")

## Loading required package: DT

## Warning: package 'DT' was built under R version 4.0.5

require("ggplot2")

# Create database connection
con <- DBI::dbConnect(odbc::odbc(), "DEID")

Exercise 1:2020 Admissions

dbGetQuery(con, "
  SELECT count(*)
  FROM deid_uf.EncounterFact
  WHERE YEAR(DateKeyValue) = 2020
  AND
    IsHospitalAdmission = 1
")

##        
## 1 80351

copd <- dbGetQuery(con, "
  SELECT count(*) AS Count
  FROM deid_uf.EncounterFact
  WHERE YEAR(DateKeyValue) = 2020
  AND
    IsHospitalAdmission = 1
  GROUP BY MONTH(DateKeyValue)
")

datatable(data.frame(Month = c("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"), Count = copd$Count), rownames = F, colnames = c("Month", "Count"), options = list(pageLength = 12, autoWidth = F), caption = 'Exercise 1: Admissions by Month with Primary Diagnosis of COPD Exacerbation (Year: 2020)')

Exercise 2: COPD Exacerbations

dbGetQuery(con, "
  SELECT count(*)
  FROM deid_uf.EncounterFact
  LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
    DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
  WHERE YEAR(EncounterFact.DateKeyValue) = 2020
  AND
    EncounterFact.IsHospitalAdmission = 1
  AND
    DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND
    DiagnosisTerminologyDim.VALUE LIKE '%J44.1%'
")

##     
## 1 86

Exercise 3: Discharge Disposition

dispo <- dbGetQuery(con, "
  SELECT DischargeDisposition, count(*) AS Count
  FROM deid_uf.EncounterFact
  LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
    DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
  WHERE YEAR(EncounterFact.DateKeyValue) = 2020
  AND
    EncounterFact.IsHospitalAdmission = 1
  AND
    DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND
    DiagnosisTerminologyDim.VALUE LIKE '%J44.1%'
  GROUP BY EncounterFact.DischargeDisposition
  ORDER BY Count DESC
")

datatable(dispo, rownames = F, colnames = c("Month", "Count"), options = list(pageLength = 9, autoWidth = F), caption = 'Exercise 3: Discharge Disposition For Patients Admitted with Primary Diagnosis of COPD Exacerbation (Year: 2020)')

Exercise 4: Length of Stay

los = dbGetQuery(con, "
  SELECT AdmissionDateKeyValue,
    DischargeDateKeyValue
  FROM deid_uf.EncounterFact
  LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
    DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
  WHERE YEAR(EncounterFact.DateKeyValue) = 2020
  AND
    EncounterFact.IsHospitalAdmission = 1
  AND
    DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND
    DiagnosisTerminologyDim.VALUE LIKE '%J44.1%'
")

# Calculate length of stay by subtracting admission date from discharge date
los$AdmissionLength = los$DischargeDateKeyValue - los$AdmissionDateKeyValue

# mean
paste0("Mean = ",
round(as.numeric(mean(na.omit(los$AdmissionLength))), digits = 2))

## [1] "Mean = 4.07"

# median
paste0("Median = ",
round(as.numeric(median(na.omit(los$AdmissionLength))), digits = 2))

## [1] "Median = 3"

Exercise 5: Race/Ethnicity

raceth = dbGetQuery(con, "
  SELECT PatDurableDim.UCSFDerivedRaceEthnicity_X,
  COUNT(DISTINCT PatDurableDim.PatientDurableKey) AS 'Count'
  FROM deid_uf.PatDurableDim
  LEFT JOIN deid_uf.EncounterFact ON
    PatDurableDim.PatientDurableKey = EncounterFact.PatientDurableKey
  LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
    DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
  WHERE YEAR(EncounterFact.DateKeyValue) = 2020
  AND
    EncounterFact.IsHospitalAdmission = 1
  AND
    DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND
    DiagnosisTerminologyDim.VALUE LIKE '%J44.1%'
  GROUP BY PatDurableDim.UCSFDerivedRaceEthnicity_X
  ORDER BY Count DESC
")

datatable(raceth, rownames = F, colnames = c("Race/ethnicity", "Patients"), options = list(pageLength = 5, autoWidth = F), caption = 'Exercise 5: Race/ethnicity For Patients Admitted with Primary Diagnosis of COPD Exacerbation (Year: 2020)')

Exercise 6: Diagnosis Groupers

grpr <-  dbGetQuery(con, "
  SELECT ValueSetEpicId, count(*) AS Count
  FROM deid_uf.DiagnosisSetDim
  WHERE Name LIKE '%COPD%'
  GROUP BY ValueSetEpicId
  ORDER BY Count DESC
")

datatable(grpr, rownames = F, colnames = c("Epic ID", "Count"), options = list(pageLength = 11, autoWidth = F), caption = 'Exercise 6: Count of Possible Groupers for COPD by Epic ID')