Lab 2 - 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: Cohort Size

dbGetQuery(con, "
SELECT COUNT(DISTINCT DiagnosisEventFact.PatientDurableKey) AS 'n'
FROM deid_uf.DiagnosisEventFact

LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
        DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey

WHERE DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND (DiagnosisTerminologyDim.VALUE = 'C61' OR DiagnosisTerminologyDim.VALUE = 'C67.0')
  AND DiagnosisEventFact.PatientDurableKey != '-1'
")

##       n
## 1 40487

Exercise 2: Diagnoses Over Time

enc <- dbGetQuery(con, "
  SELECT YEAR(StartDateKeyValue) as yyyy, count(*) AS encounter
  FROM deid_uf.DiagnosisEventFact
  LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
    DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
  WHERE DiagnosisTerminologyDim.VALUE = 'C61'
  AND
    DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND
    DiagnosisEventFact.PatientDurableKey != '-1'
  AND
    DiagnosisEventFact.StartDateKeyValue < '20200101'
  GROUP BY YEAR(StartDateKeyValue)
")
                          
datatable(enc, rownames = F, colnames = c("Year", "Encounters"), options = list(pageLength = 44, autoWidth = F), caption = 'Table 1: Annual Encounters for Prostate Cancer (1956 to 2019)')

ggplot(data = enc, aes(x=yyyy, y=encounter)) + geom_bar(stat="identity") + labs(title = "Figure 1: Annual Encounters for Prostate Cancer (1956 to 2019)", x = "Year", y = "Encounters") + theme_minimal()

Exercise 3: Race/Ethnicity

pca <- dbGetQuery(con, "
  SELECT PatDurableDim.UCSFDerivedRaceEthnicity_X,
  COUNT(DISTINCT PatDurableDim.PatientDurableKey) AS 'n'
  FROM deid_uf.PatDurableDim
  LEFT JOIN deid_uf.DiagnosisEventFact ON
    PatDurableDim.PatientDurableKey = DiagnosisEventFact.PatientDurableKey
  LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
  DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
  WHERE DiagnosisTerminologyDim.VALUE = 'C61'
  AND
    DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
  AND
    DiagnosisEventFact.PatientDurableKey != '-1'
  GROUP BY PatDurableDim.UCSFDerivedRaceEthnicity_X
")

datatable(pca, rownames = F, colnames = c("Race/ethnicity", "Patients"), options = list(pageLength = 10, autoWidth = F), caption = 'Table 2: Race/ethnicity of Patients with Prostate Cancer Diagnosis')

ggplot(data = pca, aes(x=UCSFDerivedRaceEthnicity_X, y=n)) + geom_bar(stat="identity") + labs(title = "Figure 2: Race/ethnicity of Patients with Prostate Cancer Diagnosis", x = "Race/ethnicity", y = "Patients") + theme_minimal() + theme(axis.text.x = element_text(angle = 90))