Epi 231: Lab 1 & 2
Part 2 - Intro to the DeID CDW
Anobel Odisho, Jean Digitale, Mark Pletcher
1 Intro
Part 1 of the lab involves requesting accounts, completing necessary trainings, and verifying access to RAE, Git, and the DeID CDW. Those instructions can be found on the CLE. Once you have done that, Part 2 of the lab is your first foray into connecting and querying the De-Identified Clinical Data Warehouse. The intention is to guide you through the initial connection process, explain the environment set up, and introduce you to some basic queries. Don’t focus on any clinical specifics. You do not need to be familiar with the CDW tables and columns for the purposes of this lab. We will indicate which tables/columns you should be using.
You can copy and paste the code out of this HTML document into your environment, or you can open the original R Markdown (.Rmd) file in RAE and execute the code chunks directly.
2 Connect to Database
The DeID CDW is a SQL-based relational database that consists of 120 tables with over 3000 variables and millions of observations. You cannot load the whole database into your statistical analysis program; instead, you will need to connect to the database and use SQL to query the tables where they are, and then extract specifically the data you’ll need for statistical analyses. Our labs will walk you through these various steps.
2.1 SQL Server Management Studio
One direct way to connect to a SQL database is to use SQL Server Management Studio, which allows you to write SQL queries and then export the data for use in the statistical analysis of your choice. In this course, we know you will want to use statistical analysis software to analyze your data, so we will provide instructions on how to connect directly from your stats software using an ODBC (Open DataBase Connectivity) interface. If you’d rather use SQL Server Management Studio, follow these instructions.
2.2 Stata
ODBC (Open Database Connectivity) is a standard application programming interface (API) for accessing database management systems (DBMS). ODBC’s architecture consists of four components: the user interface (Stata commands), an ODBC driver manager, an ODBC driver, and an ODBC data source. In this course, we will learn to use Stata commands to query our data source, the De-Identified Clinical Data Warehouse. To connect to the De-Identified Data Warehouse from Stata in the RAE, we will use a Data Source Name (DSN). This DSN stores connection details like the database name, directory, driver, username, and password so that we don’t have to enter it every time to connect to the database. The DSN for the DeID CDW has already been created for everyone in RAE. It is called “DEID”.
Every time you execute a SQL query in Stata, you have tell Stata both the SQL code you want it to execute and the data source (DSN) you want it to use.
The following code proves you’re connected to the CDW. Note: This code is not required every time you want to connect. You do NOT need to execute this individual line of code at the top of every do-file in the future.
odbc query "DEID"Open Stata in RAE, and try running this command. If it runs without error, then you’ve succeeded in connecting.
2.3 R
In order to connect to an external data source from R, you need to follow three general steps:
- Enable R to access the ODBC drivers on your computer/environment. ODBC (Open Database Connectivity) is a standard application programming interface (API) for accessing database management systems (DBMS). The package that allows R to access the built in ODBC drivers is called odbc.
- Enable R to communicate with a database using the ODBC drivers. The DBI package allows you to use the ODBC drivers and connect to a database
- Establish the connection to the database. This is done using by calling the dbConnect function in the DBI package, using ODBC drivers, and calling a specific database either using a detailed connection string or with a built-in Data Source Name (
DSN). For your convenience, aDSNhas for the De-Identified Data Warehouse has been created for you. It is called, “DEID”. You will use this exact command every time you want to connect to the CDW, so keep it handy for re-use at the beginning of each of your scripts.
There are two ways to send SQL queries to the database. They produce equivalent results. We will show and explain both approaches. Select the approach that you are most comfortable with.
- The DBI package allows you to send SQL code from R directly to the database. For students comfortable with SQL, this allows you to write the SQL code directly and execute it.
- The dbplyr package allows you to write tidyverse friendly code using common functions like filter and select, and translates your tidyverse code to SQL and executes it.
library(odbc)
library(DBI)
library(dbplyr)
# Create database connection
# con <- DBI::dbConnect(odbc::odbc(), "DEID")
# For anobel only will remove from final lab
source("connect_deidcdw.R")2.3.1 Load Additional Packages
There are a series of packages we will use for data manipulation and cleaning and displaying output in tables and figures. You should load these packages for each lab.
- Tidyverse includes the following packages: dplyr, tidyr, ggplot2, stringr, readr, purr, tibble, forcats. We will primarily be using the first four.
- knitr allows the generation of reports (like this one) using Markdown and has many useful functions for quick, attractive statistical output.
- DT generates interactive tables from your results. While in these examples we will be using it with default settings to keep things simple, it is very customizable and powerful.
library(tidyverse)
library(knitr)
library(DT)3 Exploring Diagnosis Terminology
Diagnoses and relevant codes are often the first inclusion/exclusion criteria that investigators select when working with EHR data. Lets start with exploring the relevant tables.
3.1 Overview
The table DiagnosisTerminologyDim contains data about diagnostic codes themselves. List the first 20 rows of this table.
The first time you refer to a table in a query, it is best practice to tell SQL in what schema the table exists. For example, there are two versions of the De-Identified Data Warehouse: deid and deid_uf. Some tables are present in both schemas. We want to use the tables in the user-friendly schema, deid_uf. Thus, when we call a table the first time, we will put “deid_uf.” before the table name so that SQL looks in the right place!
The most basic SQL query needs two commands: SELECT and FROM.
Here, we’re asking SQL to SELECT the first 20 rows (TOP 20) of ALL columns (denoted by *) from the table DiagnosisTerminologyDim which is a member of the deid_uf schema.
As your queries get more complex, you can add a WHERE command (WHERE column_name = 'some value') to filter your results, add GROUP BY for grouping, and merge tables using various forms of JOIN.
3.1.1 STATA
Try putting this code into a Stata .do file:
clear all
cd "INSERT YOUR WORKING DIRECTORY"
capture log close
log using "Lab 1_2.log", replace
#delimit ;
local sql
SELECT TOP 20 *
FROM deid_uf.DiagnosisTerminologyDim;
#delimit cr
odbc load, exec("`sql'") dsn("DEID") clear
browse
save test_top20.dta, replace
log closeThe log commands will record the Stata output in a text file that you can access later. You will need to specify a valid pathname in your RAE folder in the cd (“change directory”) command .
Complex SQL queries include multiple statements that are easiest to write and organize with line breaks. However, Stata’s usual line break indicator (///, which enables a command to be split across multiple lines) is not compatible with SQL code. To allow us to build increasingly complex SQL queries in a readable way, we will reset the character that defines the end of a command to be a semi-colon (;) using the #delimit command. This enables us to create a local (block of text that can be called up later) that can then be submitted as executable code in an odbc command. We’ll name the local sql. End the SQL code with ; and then reset the delimiter to a carriage return with #delimit cr. Now we can call sql (the local we created) to execute the query.
To execute a SQL query and load the results in the results window, use the odbc load command. You will refer to the local with your SQL query inside the parentheses of the exec option where Stata looks for the SQL query syntax, and also include the DSN “DEID” option every time. The clear option clears out anything that was previously in memory so there is room to load the new dataset created from the SQL query. Be sure to run the code where you define your SQL local in the same run as the odbc command, since a local disappears quickly from Stata’s memory.
After loading the SQL results into Stata as a dataset, you can use all your usual Stata commands (e.g., tabulate), and you can save the data as a .dta file that can be called up quickly later with a use command in Stata (no need for more SQL).
Note – if you wanted to simply print out the data from the SQL command instead of loading it into memory, you could use this odbc command, which is sort of like a list command in Stata:
odbc exec("`sql'"), dsn("DEID")You now have a full method for querying a SQL database and extracting data, loading it into Stata, analyzing and saving the data with usual Stata commands, and logging everything. You can now swap in or add any other SQL commands or queries. For future labs, we’ll just provide SQL code blocks; you can create the cd and log commands, etc.
3.1.2 R-SQL
Use the function dbGetQuery to send the query to the database and fetch the results. The first argument for the function is the name of the connection that you will use (we created this as con). The second argument is the actual query you are sending. All arguments have to be separated by a comma and the SQL query is contained in double quotes (single will be reserved for use inside the SQL query you send, such as in later examples).
SELECT column_name_1, column_name_2
FROM schema_name.table_namedbGetQuery(con, "
SELECT TOP 20 *
FROM deid_uf.DiagnosisTerminologyDim")If you want to save your results as an object, use the assignment operator (<-) like you normally would in R.
3.1.3 R-dbplyr
With dbplyr, you first have to initialize each database table you plan to use with the tbl() function. This takes the name of the connection (con) as the first argument, and the database table name as the second argument. Each table only needs to be initialized once in a script. You can give the tables that you initialize any name you want (short names can make your faster to type and work with but harder to interpret later). For the purposes of these labs, we will always be using the full table name to minimize confusion.
dbplyr syntax is the reverse of SQL, as you identify the table first, and then use the pipe operator (%>%) to send this object into the next function. You start with the table of interest (DiagnosisTerminologyDim) and send it forward into the head() function, where you specify you want the first 20 rows.
If you want to save your results as an object, you have to use the collect() function. Otherwise you can display your results as a table or figure but it will not be saved as an object and you will need to re-query the database to display your results again. As your queries become more complex and require many table joins with larger data sets, they can take a while to run and it makes more sense to save the data locally using collect() and then manipulate/display it.
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisTerminologyDim %>%
head(20)3.2 Diagnosis Code Types
This table contains multiple different diagnosis code types in the same column, such as ICD-9 or ICD-10. These are stored in the TYPE column. Create a table that shows all possible values of this column and how many observations there for each.
The first command, SELECT, identifies which columns our query will display. Here we are selecting the TYPE column and how many times that TYPE comes up (COUNT(TYPE)) which will be called n in our final table. We are selecting from the DiagnosisTerminologyDim table, which is in the deid_uf schema. We want to summarize by the categories in TYPE, so we use GROUP BY TYPE. Any time you include a summary function, like COUNT in your select query, all of those variables must also be included in the GROUP BY portion of the query.
3.2.1 STATA
#delimit ;
local sql
SELECT
TYPE,
COUNT(TYPE) AS 'n'
FROM
deid_uf.DiagnosisTerminologyDim
GROUP BY TYPE;
#delimit cr
odbc exec("`sql'"), dsn("DEID")3.2.2 R-SQL
dbGetQuery(con, "
SELECT
TYPE,
COUNT(TYPE) AS 'n'
FROM
deid_uf.DiagnosisTerminologyDim
GROUP BY TYPE")3.2.3 R-dbplyr
The dbplyr version starts with identifying the table of interest (DiagnosisTerminologyDim), and passing that to the next function, count(). The count() function does the grouping, tallying, and creation of the summary column all together.
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisTerminologyDim %>%
count(Type)3.3 Prostate Cancer Codes
The ICD-10 Code for Prostate Cancer is C61. Each diagnosis code is associated with multiple DiagnosisKeys, which are internal representations for this code.
How many different Diagnosis Keys are associated with the ICD-10 code? Let’s keep looking into the DiagnosisTerminologyDim table, and identify observations where the TYPE column contains “ICD-10-CM” and the VALUE column is the code, “C61”.
3.3.1 STATA
#delimit ;
local sql
SELECT count(*)
FROM deid_uf.DiagnosisTerminologyDim
WHERE
TYPE = 'ICD-10-CM'
AND
VALUE = 'C61';
#delimit cr
odbc exec("`sql'"), dsn("DEID")
3.3.2 R-SQL
dbGetQuery(con, "
SELECT count(*)
FROM deid_uf.DiagnosisTerminologyDim
WHERE
TYPE = 'ICD-10-CM'
AND
VALUE = 'C61'")3.3.3 R-dbplyr
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisTerminologyDim %>%
filter(Type == "ICD-10-CM") %>%
filter(Value == "C61") %>%
tally()3.4 Diagnosis Names
Why are there so many internal codes for a single diagnosis? List all of the different ways this diagnosis can be described in the EHR using C61. Look at the column DiagnosisName.
3.4.1 STATA
#delimit ;
local sql
SELECT DiagnosisName
FROM deid_uf.DiagnosisTerminologyDim
WHERE
TYPE = 'ICD-10-CM'
AND
VALUE = 'C61';
#delimit cr
odbc exec("`sql'"), dsn("DEID")
Try this again using the odbc load command, followed by the browse command - the results may be more digestible to you!
3.4.2 R-SQL
dbGetQuery(con, "
SELECT DiagnosisName
FROM deid_uf.DiagnosisTerminologyDim
WHERE
TYPE = 'ICD-10-CM'
AND
VALUE = 'C61'") 3.4.3 R-dbplyr
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisTerminologyDim %>%
filter(Type == "ICD-10-CM") %>%
filter(Value == "C61") %>%
select(DiagnosisName)3.5 Internal IDs
Identify all of the different ways Prostate Cancer can be described in the EHR and the relevant internal IDs. These internal IDs (DiagnosisKey) are used to link to other internal tables. The ICD-10 code for prostate cancer is C61. Sort these in alphabetical order by DiagnosisName.
3.5.1 STATA
#delimit ;
local sql
SELECT DiagnosisKey,
DiagnosisName
FROM deid_uf.DiagnosisTerminologyDim
WHERE
TYPE = 'ICD-10-CM'
AND
VALUE = 'C61'
ORDER BY DiagnosisName;
#delimit cr
odbc load, exec("`sql'") dsn("DEID") clear
browse
3.5.2 R-SQL
dbGetQuery(con, "
SELECT DiagnosisKey,
DiagnosisName
FROM deid_uf.DiagnosisTerminologyDim
WHERE
TYPE = 'ICD-10-CM'
AND
VALUE = 'C61'
ORDER BY DiagnosisName") 3.5.3 R-dbplyr
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisTerminologyDim %>%
filter(Type == "ICD-10-CM") %>%
filter(Value == "C61") %>%
select(DiagnosisKey, DiagnosisName) %>%
arrange(DiagnosisName) %>%
collect() %>%
datatable()3.6 Joining Tables
There are multiple ways to join separate tables, such as LEFT JOIN, RIGHT JOIN, INNER JOIN, and FULL JOIN. (JOIN is SQL speak for merge in Stata.) For each join, you need to identify a KEY, which is a column that is present in BOTH tables. For example, a patient medical record number or a diagnosis code.
To conceptualize a LEFT JOIN, think about placing the first table you select on the LEFT side of the page. This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null or NA values.
A RIGHT JOIN is the opposite of the LEFT JOIN.
An INNER JOIN will only return the rows where the values have a match in BOTH tables.
An OUTER JOIN or FULL JOIN will return ALL rows from both tables, and will populate with NA values wherever there are no matches.
Please take a moment to review this walkthrough.
Use a JOIN to determine the number of patients with an ICD-10 diagnosis for prostate cancer.
For our query, let’s start with the FROM clause. This defines the “left” table in the LEFT JOIN statement. Here, we’re going to be primarily selecting from the DiagnosisEventFact table in the deid_uf schema. This table contains one row for every event attributing a diagnosis to a patient and information about that event (such as EncounterKey) and diagnosis (such as diagnosis TYPE). There can be multiple rows per patient encounter (as a patient can be assigned multiple diagnoses at a visit).
Now to the first line of the query. We want to SELECT one column, PatientDurableKey, which is unique to each patient. The DISTINCT function gives you a list of the unique patients (we don’t want to count a given person more than once if they have multiple diagnosis events). The COUNT() function counts how many rows that query returns. AS 'n' will rename that column name to n.
If we just ran the first two lines, we would get a count of unique patients that had an encounter in the database. That number, approximately 2.4 million, is large and impressive, but not very useful.
Let’s further narrow our query. We would like to filter on the prostate cancer ICD10 code, which is C61. That is stored in a separate table, DiagnosisTerminologyDim. We need to join those tables so that we can filter based on that result. We will perform a LEFT JOIN, which keeps the DiagnosisEventFact table on the left, and then adds on the DiagnosisTerminologyDim table using the shared column DiagnosisKey as the matching column.
Now you can filter to the C61 ICD-10 code. Add the WHERE clause at the end, only returning rows where the VALUE column in the DiagnosisTerminologyDim table is C61.
3.6.1 STATA
#delimit ;
local sql
SELECT COUNT(DISTINCT PatientDurableKey) AS 'n'
FROM deid_uf.DiagnosisEventFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
WHERE DiagnosisTerminologyDim.VALUE = 'C61';
#delimit cr
odbc exec("`sql'"), dsn("DEID")3.6.2 R-SQL
dbGetQuery(con, "
SELECT COUNT(DISTINCT PatientDurableKey) AS 'n'
FROM deid_uf.DiagnosisEventFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
WHERE DiagnosisTerminologyDim.VALUE = 'C61'
")3.6.3 R-dbplyr
We start by initializing the two tables we are going to use, DiagnosisTerminologyDim and DiagnosisEventFact. You only need to do it once per session, not before every query. We start our dbplyr chain by identifying the first table of interest, DiagnosisEventFact. From that, we only want to select two columns of interest, PatientDurableKey and DiagnosisKey. It’s generally good practice select as few columns as you can, so you have more efficient queries.
We then do a left_join to the DiagnosisTerminologyDim table, and identify DiagnosisKey as the common column in both tables. Next we filter down to patients that only have an ICD-10 code of “C61”, which is in the VALUE column. You dont need to identify a specific table here because you have already joined them. Then you perform a similar distinct function on the PatientDurableKey column, to get the distinct list of patients. Then you pass this result to the count() function for your final result.
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisEventFact <- tbl(con, in_schema("deid_uf", "DiagnosisEventFact"))
DiagnosisEventFact %>%
select(PatientDurableKey, DiagnosisKey) %>%
left_join(DiagnosisTerminologyDim, by = "DiagnosisKey") %>%
filter(VALUE == "C61") %>%
distinct(PatientDurableKey) %>%
count()4 Exercises
4.1 Cohort Size
How many patients in this database have ever had a diagnosis of prostate cancer or bladder cancer (use ICD-10 code C67.0 only)? In addition to identifying the two ICD codes, for accuracy, you should also specify that you are looking for ICD-10 codes. Make sure you are limiting your query to results where the TYPE column in the DiagnosisTerminologyDim table is equal to ICD-10-CM. You will need to identify all of your appropriate DiagnosisKey values and join with the DiagnosisEventFact table. The correct patient ID column is PatientDurableKey. Also, you will need to exclude all patients with a PatientDurableKey that is set to “-1”. There are some test/dummy patients in the database, and their PatientDurableKey was set to -1.
Note the following SQL operators:
- And:
AND
- Or:
OR
- Equal to:
=
- Not equal to:
!=or<>
4.1.1 STATA
#delimit ;
local sql
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';
#delimit cr
odbc exec("`sql'"), dsn("DEID")
4.1.2 R-SQL
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'
")4.1.3 R-dbplyr
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisEventFact <- tbl(con, in_schema("deid_uf", "DiagnosisEventFact"))
DiagnosisTerminologyDim %>%
filter(Type == "ICD-10-CM") %>%
filter(Value == "C61" | Value == "C67.0") %>%
select(DiagnosisKey) %>%
left_join(DiagnosisEventFact, by = "DiagnosisKey") %>%
select(PatientDurableKey) %>%
filter(PatientDurableKey != "-1") %>%
distinct() %>%
count()4.2 Diagnoses Over Time
You would like to understand how many encounters there are for prostate cancer every year using the DiagnosisEventFact table. Tally the number of encounters where prostate cancer was an encounter diagnosis in each year. Use the SQL YEAR() function around the date, “StartDateKeyValue”. Name the year variable you create “EncounterYear”. Exclude 2020, as the data for this year are incomplete. Present the data as a table and a bar chart (histogram), with each bar representing one year and the Y axis representing the number of encounters.
For example, if you wanted to extract the year from a date of birth, from the imaginary column date_of_birth in a PATIENT table, you would write the following SQL query:
SELECT YEAR(data_of_birth)
FROM PATIENTYou can also create an ALIAS, which is just another name for a column which may be easier to view. When you select a column, you use the command AS to create an alias. To rename the result of the prior query to "YEAR _OF_BIRTH":
SELECT YEAR(data_of_birth) as YEAR_OF_BIRTH
FROM PATIENT4.2.1 STATA
#delimit ;
local sql
SELECT YEAR(StartDateKeyValue) as EncounterYear
FROM deid_uf.DiagnosisEventFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
WHERE DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
AND DiagnosisTerminologyDim.VALUE = 'C61'
AND StartDateKeyValue < '2020-01-01';
#delimit cr
odbc load, exec("`sql'") dsn("DEID") clear
tab EncounterYear, missing
egen countyear = count(EncounterYear), by(EncounterYear)
duplicates drop
sort EncounterYear
twoway bar countyear EncounterYear
An alternative way of excluding encounters in 2020 is: AND YEAR(StartDateKeyValue) != '2020'.
4.2.2 R-SQL
enc <- dbGetQuery(con, "
SELECT YEAR(DiagnosisEventFact.StartDateKeyValue) AS EncounterYear
FROM deid_uf.DiagnosisEventFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
WHERE DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
AND (DiagnosisTerminologyDim.VALUE = 'C61')
AND PatientDurableKey != '-1'
AND StartDateKeyValue < '2020-01-01'")4.2.3 R-dbplyr
DiagnosisTerminologyDim <- tbl(con, in_schema("deid_uf", "DiagnosisTerminologyDim"))
DiagnosisEventFact <- tbl(con, in_schema("deid_uf", "DiagnosisEventFact"))
enc <- DiagnosisTerminologyDim %>%
filter(Type == "ICD-10-CM") %>%
filter(Value == "C61") %>%
select(DiagnosisKey) %>%
left_join(DiagnosisEventFact, by = "DiagnosisKey") %>%
filter(PatientDurableKey != "-1") %>%
filter(StartDateKeyValue < "2020-01-01") %>%
mutate(EncounterYear = year(StartDateKeyValue)) %>%
select(EncounterYear) %>%
collect()4.2.4 Results
enc %>%
count(EncounterYear) %>%
kable()| EncounterYear | n |
|---|---|
| 1956 | 1 |
| 1960 | 1 |
| 1963 | 1 |
| 1964 | 1 |
| 1975 | 1 |
| 1979 | 1 |
| 1980 | 1 |
| 1983 | 1 |
| 1984 | 1 |
| 1985 | 1 |
| 1986 | 1 |
| 1987 | 7 |
| 1988 | 13 |
| 1989 | 21 |
| 1990 | 46 |
| 1991 | 788 |
| 1992 | 1064 |
| 1993 | 829 |
| 1994 | 1035 |
| 1995 | 1526 |
| 1996 | 2518 |
| 1997 | 3619 |
| 1998 | 5356 |
| 1999 | 9706 |
| 2000 | 13917 |
| 2001 | 15939 |
| 2002 | 17527 |
| 2003 | 18464 |
| 2004 | 19126 |
| 2005 | 19011 |
| 2006 | 20210 |
| 2007 | 19458 |
| 2008 | 19314 |
| 2009 | 20755 |
| 2010 | 21335 |
| 2011 | 31932 |
| 2012 | 51940 |
| 2013 | 58777 |
| 2014 | 71788 |
| 2015 | 103694 |
| 2016 | 130714 |
| 2017 | 133246 |
| 2018 | 138895 |
| 2019 | 158629 |
ggplot(enc, aes(x = EncounterYear)) +
geom_bar(fill = "darkcyan") +
theme_minimal()
ggplot(enc, aes(x = EncounterYear)) +
geom_bar(fill = "darkcyan") +
theme_minimal()
4.3 Race/Ethnicity
Tabulate the Race/Ethnicity (name it RaceEth) for all patients that have ever had a diagnosis of prostate cancer. Patient Race/Ethnicity is stored as PatDurableDim.UCSFDerivedRaceEthnicity_X. If you request all of the results, it will return 1.1 million rows. You can pull everything and manipulate it in your statistical software (which will be slow), or you can filter it in SQL and only bring over the necessary rows (which will be faster). Both ways will work. Display your results as a table and as a bar chart (histogram).
4.3.1 STATA
#delimit ;
local sql
SELECT UCSFDerivedRaceEthnicity_X as RaceEth
FROM deid_uf.PatDurableDim
LEFT JOIN deid_uf.DiagnosisEventFact ON
DiagnosisEventFact.PatientDurableKey = PatDurableDim.PatientDurableKey
LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
WHERE DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
AND DiagnosisTerminologyDim.VALUE = 'C61'
AND PatDurableDim.PatientDurableKey != '-1';
#delimit cr
odbc load, exec("`sql'") dsn("DEID") clear
tab RaceEth, missing
gen n = _n
graph bar (count) n, over(RaceEth, label(angle(45)))
4.3.2 R-SQL
pca <- dbGetQuery(con, "
SELECT UCSFDerivedRaceEthnicity_X as RaceEth
FROM deid_uf.PatDurableDim
LEFT JOIN deid_uf.DiagnosisEventFact ON
DiagnosisEventFact.PatientDurableKey = PatDurableDim.PatientDurableKey
LEFT JOIN deid_uf.DiagnosisTerminologyDim ON
DiagnosisTerminologyDim.DiagnosisKey = DiagnosisEventFact.DiagnosisKey
WHERE DiagnosisTerminologyDim.TYPE = 'ICD-10-CM'
AND DiagnosisTerminologyDim.VALUE = 'C61'
AND PatDurableDim.PatientDurableKey != '-1'
")4.3.3 R-dbplyr
4.3.4 Results
| Var1 | Freq |
|---|---|
| *Unknown | 3 |
| American Indian or Alaska Native | 2766 |
| Asian | 100404 |
| Black or African American | 75617 |
| Latinx | 62763 |
| Multi-Race/Ethnicity | 13621 |
| Native Hawaiian or Other Pacific Islander | 11105 |
| Other | 36717 |
| Unknown/Declined | 40039 |
| White or Caucasian | 865389 |
5 Your Own Project
Each week we’ll take a step forward on your own project, including writing and planning, coding, and presenting results.
5.1 Writing
Write a first draft of the Protocol for your Final Project. Include all the required elements (see assignment), but the content within each element need not be finalized nor even highly polished. In the Study Subjects section, describe inclusion and/or exclusion criteria that use one or more diagnosis codes.
5.2 Coding
Write code in your language of choice that extracts study subjects based on your desired diagnosis codes, and extract their race/ethnicity and sex.
5.3 Presentation of Results
Design Table 1, including race/ethnicity and sex, and fill it in for participants meeting your first pass inclusion/exclusion criteria.