Start your Stata .do file.
clear all
cd "INSERT YOUR WORKING DIRECTORY"
capture log close
log using "Lab_3.log", replaceThis code gets you connected to the De-ID CDW and loads necessary packages for data import, cleaning, and display.
# 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")
source("connect_deidcdw.R")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.
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.
All encounters are listed in the EncounterFact table. This has one row for each encounter.
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.
* 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") cleardbGetQuery(con, "
SELECT
ProviderPrimarySpecialty,
count(*) AS Count
FROM deid_uf.EncounterFact
GROUP BY ProviderPrimarySpecialty
ORDER BY Count DESC
")EncounterFact <- tbl(con, in_schema("deid_uf", "EncounterFact"))
EncounterFact %>%
count(ProviderPrimarySpecialty) %>%
arrange(desc(n)) %>%
collect() %>%
datatable()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.
Note: This may take a few minutes to execute.
#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# 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
")EncounterFact <- tbl(con, in_schema("deid_uf", "EncounterFact"))
EncounterFact %>%
filter(year(DateKeyValue) == 2020) %>%
count(Type) %>%
collect() %>%
datatable()List the 20 most common encounter types, in descending order
#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") cleardbGetQuery(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
")EncounterFact <- tbl(con, in_schema("deid_uf", "EncounterFact"))
EncounterFact %>%
filter(year(DateKeyValue) == 2020) %>%
count(Type) %>%
arrange(desc(n)) %>%
collect() %>%
head(20)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.
#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")dbGetQuery(con, "
SELECT Type,
count(*)
FROM deid_uf.DiagnosisTerminologyDim
GROUP BY Type
")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.
#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") cleardbGetQuery(con, "
SELECT DiagnosisName,
DiagnosisEpicId,
Type,
Value,
DisplayString
FROM deid_uf.diagnosisterminologydim
WHERE Type = 'ICD-10-CM'
AND Value = 'C61'
")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()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.
How many admissions were there in 2020 where COPD exacerbation (ICD-10 J44.1) was the primary diagnosis?
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)?
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.
What is distribution of patient race/ethnicity for these admissions? Note: use PatDurableDim.UCSFDerivedRaceEthnicity_X
Find the best grouper to use when trying to broadly identify all patients with COPD.
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.
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.
Refine your Table 1 SQL code (started in Lab 1/2) to: