--- title: "Epi 231: 3" author: "Anobel Odisho, Jean Digitale, Mark Pletcher" subtitle: Part 3 - Encounters and Diagnoses output: html_document: df_print: paged number_sections: yes toc: yes toc_depth: '3' toc_float: yes code_folding: show theme: cosmo word_document: toc: yes toc_depth: '3' editor_options: chunk_output_type: console --- ```{r, echo = F} knitr::opts_chunk$set(warning = F, cache = F) ``` # Set Up ## Stata Start your Stata .do file. ```{r eval = F, echo = T} clear all cd "INSERT YOUR WORKING DIRECTORY" capture log close log using "Lab_3.log", replace ``` ## R This code gets you connected to the De-ID CDW and loads necessary packages for data import, cleaning, and display. ```{r setup, message=F} # Load necessary Pacakges library(odbc) # Loads the ODBC drivers library(DBI) # Allows you to pass SQL to database library(dbplyr) # use dplyr style commands to extract data from SQL databases library(tidyverse) # data cleaning library(lubridate) # work with dates more efficiently library(knitr) # Rmarkdown and table display library(DT) # Interactive tables in markdown library(ggplot2) # making charts # Create database connection con <- DBI::dbConnect(odbc::odbc(), "DEID") ``` # Intro In this lab we will be exploring basic encounter and diagnosis data. This is a rich source of structured data because it is often critical for billing purposes. # Key Tables - EncounterFact - The encounter fact table contains information about encounters documented in an EMR and encounters derived from billing data. Each row represents an encounter. Encounters from an EMR are flagged with IsDerivedFromBilling = 0 and encounters derived from billing data are flagged with IsDerivedFromBilling = 1. For encounters derived from billing data, Epic has released definitions that combine multiple outgoing and paid claims into one encounter based on comparing attributes like patient, provider, and date. - DiagnosisTerminologyDim - The diagnosis terminology dimension table contains information about terms and codes for diagnoses. Each row represents a term or code for a diagnosis. # Encounters All encounters are listed in the `EncounterFact` table. This has one row for each encounter. ## Encounter Provider What are all the possible sources of admission in this table, and how many of each kind are there? Where each patient was admitted from is stored in the `ProviderPrimarySpecialty` column. We will group by the `ProviderPrimarySpecialty` column and `COUNT` the number of times each encounter type is listed. We call that summary column `Count`, and arrange it in descending order of frequency. ### Stata ```{stata, eval = F, echo = T} * Define a local that contains the SQL query #delimit ; local sql SELECT ProviderPrimarySpecialty, count(*) AS Count FROM deid_uf.EncounterFact GROUP BY ProviderPrimarySpecialty ORDER BY Count DESC; #delimit cr * To execute a SQL statement and load results into a Stata dataset odbc load, exec("`sql'") dsn("DEID") clear ``` ### R-SQL ```{r, eval = F, echo = T} dbGetQuery(con, " SELECT ProviderPrimarySpecialty, count(*) AS Count FROM deid_uf.EncounterFact GROUP BY ProviderPrimarySpecialty ORDER BY Count DESC ") ``` ### R Dplyr ```{r, eval = T, echo = T} EncounterFact <- tbl(con, in_schema("deid_uf", "EncounterFact")) EncounterFact %>% count(ProviderPrimarySpecialty) %>% arrange(desc(n)) %>% collect() %>% datatable() ``` ## Encounters in 2020 How many encounters of each type were there in 2020? We'll also need to join the `EncounterFact` table, which has one row for every encounter. We're interested in the `Type` column. There are two ways to use dates in the DeID CDW. The most simple (and common) approach is to use the `DateKeyValue` column, which returns the date in YYYY-MM-DD format. However, you can also use the `DateKey` column, which allows you to link to the `DateDim` table. In the `DateDim` table, each row represents a date and there are additional columns to describe that day, such as the day of the week (Monday, Tuesday, etc), the month or year portion of the date, if the day is a holiday or the day after a holiday, or if it is is a weekend. For the purposes of this exercise, we will use the `DataKeyValue` field, and extract the year by using the SQL function `YEAR()` or the corresponding `R` function. One limitation of de-identified data is that all dates have been shifted. You can read more about UCSF's methodology to do so [here](https://wiki.library.ucsf.edu/pages/viewpage.action?spaceKey=DCDKB&title=Date-shifting+in+DeID+CDW). *Note:* This may take a few minutes to execute. ### Stata ```{stata, eval = F, echo = T} #delimit ; local sql SELECT EncounterFact.Type, count(*) AS Count FROM deid_uf.EncounterFact WHERE YEAR(DateKeyValue) = 2020 GROUP BY EncounterFact.Type ORDER BY Count DESC; #delimit cr odbc load, exec("`sql'") dsn("DEID") clear ``` ### R-SQL ```{r eval = F, echo = T} # load results into dataset enc_n for later use enc_n <- dbGetQuery(con, " SELECT EncounterFact.Type, count(*) AS Count FROM deid_uf.EncounterFact WHERE YEAR(DateKeyValue) = 2020 GROUP BY EncounterFact.Type ORDER BY Count DESC ") ``` ### R-Dbplyr ```{r eval=T, echo = T} EncounterFact <- tbl(con, in_schema("deid_uf", "EncounterFact")) EncounterFact %>% filter(year(DateKeyValue) == 2020) %>% count(Type) %>% collect() %>% datatable() ``` ## Most Common Encounters List the 20 most common encounter types, in descending order ### Stata ```{stata, eval = F, echo = T} #delimit ; local sql SELECT TOP 20 EncounterFact.Type, count(*) AS Count FROM deid_uf.EncounterFact WHERE YEAR(DateKeyValue) = 2020 GROUP BY EncounterFact.Type ORDER BY Count DESC; #delimit cr odbc load, exec("`sql'") dsn("DEID") clear ``` ### R-SQL ```{r eval = F, echo = T} dbGetQuery(con, " SELECT TOP 20 EncounterFact.Type, count(*) AS Count FROM deid_uf.EncounterFact WHERE YEAR(DateKeyValue) = 2020 GROUP BY EncounterFact.Type ORDER BY Count DESC ") ``` ### R-Dbplyr ```{r, eval = T, echo = T} EncounterFact <- tbl(con, in_schema("deid_uf", "EncounterFact")) EncounterFact %>% filter(year(DateKeyValue) == 2020) %>% count(Type) %>% arrange(desc(n)) %>% collect() %>% head(20) ``` # Diagnoses ## Diagnosis Codes All of the possible diagnosis types are stored in the `DiagnosisTerminologyDim` table. The `Type` column represents the type of diagnosis code, and the `Value` represents the actual diagnosis codes. Let's take a look. First, let's see how many different types of diagnosis codes we have, and how many rows for each kind. ### Stata ```{stata, eval = F, echo = T} #delimit ; local sql SELECT Type, count(*) FROM deid_uf.DiagnosisTerminologyDim GROUP BY Type; #delimit cr * To execute a SQL command and print results odbc exec("`sql'"), dsn("DEID") ``` ### R-SQL ```{r eval = F, echo = T} dbGetQuery(con, " SELECT Type, count(*) FROM deid_uf.DiagnosisTerminologyDim GROUP BY Type ") ``` ### R-Dbplyr ```{r eval = T, echo = T} DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim")) DiagnosisTerminologyDim %>% count(Type) %>% collect() %>% datatable() ``` ICD-10 has approximately 70,000 codes. Given this, why are there over 1.4 million rows in this table? Let's pick a single code (C61, prostate cancer) to find out. ### Stata ```{stata, eval = F, echo = T} #delimit ; local sql SELECT DiagnosisName, DiagnosisEpicId, Type, Value, DisplayString FROM deid_uf.diagnosisterminologydim WHERE Type = 'ICD-10-CM' AND Value = 'C61'; #delimit cr odbc load, exec("`sql'") dsn("DEID") clear ``` ### R-SQL ```{r eval = F, echo = T} dbGetQuery(con, " SELECT DiagnosisName, DiagnosisEpicId, Type, Value, DisplayString FROM deid_uf.diagnosisterminologydim WHERE Type = 'ICD-10-CM' AND Value = 'C61' ") ``` ### R-Dbplyr ```{r eval = T, echo = T} DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim")) DiagnosisTerminologyDim %>% filter(Type == "ICD-10-CM") %>% filter(Value == "C61")%>% select(DiagnosisName, DiagnosisEpicId, Type, Value, DisplayString) %>% collect() %>% datatable() ``` # Exercises ## 2020 Admissions Using the `EncounterFact` table, determine how many inpatient hospital encounters were there in all of 2020 and then by month in 2020. Hint: the column `IsHospitalAdmission` is equal to 1 for hospital admission encounters, and you can use the `MONTH()` function in SQL or `month()` in R (from the lubridate package). Arrange the results in ascending order of month. ## COPD Exacerbations How many admissions were there in 2020 where COPD exacerbation (ICD-10 J44.1) was the primary diagnosis? ## Discharge Disposition For patients admitted in 2020 where the primary encounter diagnosis was J44.1, what was the discharge disposition (the place they are expected to be discharged to, e.g. home, nursing home, etc)? ## Length of Stay For patients admitted in 2020 where the primary encounter diagnosis was J44.1, what was the mean and median length of stay? Hint: you can extract the dates in SQL, and perform your calculation in R/STATA. ## Race/Ethnicity What is distribution of patient race/ethnicity for these admissions? *Note*: use `PatDurableDim.UCSFDerivedRaceEthnicity_X` ## Diagnosis Groupers Find the best grouper to use when trying to broadly identify all patients with COPD. # Your Own Project ## Writing 1. Modify written inclusion/exclusion criteria to include: - a specific type or types of encounters (inpatient, outpatient, etc) - date ranges for those encounters - refine your inclusion/exclusion criteria using one or more diagnosis groupers 2, Describe one or more comorbidities, defined using diagnosis codes, that will be measurements you’ll use in your analyses. Consider whether these should be defined conditional on a date range. ## Planning/Presenting Results Before you start coding, modify your Table 1 design to include: - Rows for the comorbid conditions (e.g., Diabetes, n (%)) that you added to your protocol (as above) - A “Year” variable (year of last inpatient visit? Last office visit?) relevant to the encounters you are including or the inclusion/exclusion criteria you’ve defined; or some other relevant variable from the EncounterFact table. ## Coding Refine your Table 1 SQL code (started in Lab 1/2) to: - Implement your new inclusion/exclusion criteria so your code extracts the list of patients/encounters that are included in your study sample - Extract encounter and diagnosis information about needed for the new rows you have planned in Table 1 Write either SQL or Stata/R code to produce the data needed for your table, and fill in the table. Remember to update the numbers for race/ethnicity since your inclusion/exclusion criteria likely changed.