This lab will focus on tidying and reshaping data once you have extracted it from the DeID CDW. We will provide the initial SQL query to get extract the data of interest. You can them import into your tool of choice (R, STATA), and complete these exercises.
There are MANY different ways to accomplish these tasks, and while some may be “better” or more efficient than others, realistically it does not matter, and I do not want you to get hung up on optimizing your code or finding the one best way. Just try to accomplish the task in any way you can.
d <- dbGetQuery(con, "
SELECT DISTINCT e.PatientDurableKey,
e.EncounterKey,
e.AdmissionDateKeyValue,
e.DischargeDateKeyValue,
r.ResultDateKeyValue,
r.ResultTimeOfDayKeyValue,
r.ProcedureKey,
r.ProcedureName,
r.LabComponentKey,
t.LabTestKey,
r.ComponentName,
r.Value,
r.ReferenceValues
FROM deid_uf.EncounterFact e
LEFT JOIN deid_uf.DiagnosisTerminologyDim d ON d.DiagnosisKey = e.PrimaryDiagnosisKey
LEFT JOIN deid_uf.LabComponentResultFact r ON r.PatientDurableKey = e.PatientDurableKey
LEFT JOIN deid_uf.LabTestComponentResultMappingFact mf on r.LabComponentResultKey = mf.LabComponentResultKey
left JOIN deid_uf.LabTestFact t on mf.LabTestKey = t.LabTestKey
WHERE year(e.DateKeyValue) >= 2016
AND d.Type = 'ICD-10-CM'
AND d.Value = 'J44.1'
AND e.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
AND (r.CollectionDateKeyValue >= e.AdmissionDateKeyValue AND r.CollectionDateKeyValue <= e.DischargeDateKeyValue)
AND (r.ProcedureName = 'VENOUS BLOOD GAS W/LACTATE' OR r.ProcedureName = 'ARTERIAL BLOOD GAS W/LACTATE' OR
r.ProcedureName = 'COMPREHENSIVE METABOLIC PANEL (BMP, AST, ALT, T.BILI, ALKP, TP ALB)' OR
r.ProcedureName = 'BASIC METABOLIC PANEL (NA, K, CL, CO2, BUN, CR, GLU, CA)' OR
r.ProcedureName = 'COMPLETE BLOOD COUNT (INCLUDES PLATELET COUNT)' OR
r.ProcedureName = 'POCT GLUCOSE, FINGERSTICK'
)
")antibiotics <- dbGetQuery(con, "
SELECT DISTINCT e.PatientDurableKey, e.EncounterKey
FROM deid_uf.EncounterFact e
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = e.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact mof ON mof.EncounterKey = e.EncounterKey
WHERE year(e.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND e.IsHospitalAdmission = 1
AND MedicationTherapeuticClass = 'ANTIBIOTICS'
AND (MedicationRoute = 'Oral' OR MedicationRoute = 'Intravenous')
")For each unique patient and encounter combination, create a column for each type of lab (there are 6) that counts the number of that type of lab. For this exercise, use the ProcedureName, which looks at the overall panel, not the individual lab components (ComponentName). The column name should be the name of the lab, but you can abbreviate it (for example: ABG, VBG, CBC, GLUCOSE_POCT). Combine Comprehensive Metabolic Panel and Basic Metabolic Panel into one column. The actual column names you choose to use do not matter. If a patient did not have any of the specific test, fill that column with 0.
We would like to create two new binary/dummy variables. First, create a boolean variable (0 for false, 1 for true) indicating if a patient had a ABG performed any time during their admission, and another indicating if they had an ABG done on the first day of their admission. Your final result should have 334 rows (one for each patient), and 4 columns: PatientDurableKey, EncounterKey, abg_any, abg_firstday.
Your table should now have one row per patient per admission (encounter), with one column indicating if they had any ABG and one column indicating if they had an ABG on their first day. Add the following three columns to this table. Hint: You will need to work with dates and times here (and possibly combining them into a single datetime object. In R, one approach is to use the parse_date_time() function from the Lubridate package).
Going back to lab 4, let’s import the list of patients with a COPD admission who received antibiotics during their admission.
SELECT DISTINCT(PatientDurableKey)
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'Merge this list with your ABG results dataset, and create an indicator variable, marking 1 if the patient received antibiotics and 0 if they did not. Create a 2x2 table showing whether patients had an ABG on the first day by whether patients received antibiotics.
For your own project, you should generate results from at least two separate queries (such as medications, labs, procedures, or two of the same data source), and merge them into a single object, at the patient level. Your data should have one row per patient/encounter (or whatever combination is relevant for your project), and the values of at least 2 derived or calculated values.
Create a summary, boolean, or calculated value in one column that is in some way be related to a date, relative to that patient’s clinical experience. This can either be a specific date (date of admission), or a time period (number of events in the 30 days after discharge). Feel free to interpret this broadly, as it should be related to your own project.