This lab will focus on reporting on medications and medication orders. We will use admissions for COPD exacerbations as a sample case, building on code from prior labs.
How many inpatient encounters are there for patients admitted with a diagnosis of COPD from 2016 to now (this is building on a prior lab). The ICD-10 Code is ‘J44.1’; remember to set IsHospitalAdmission = 1. In working through this, there are some patients with mutliple hospital admission encounters on the same day, and we’ll need to figure out why.
First, take a look at hospital admissions in the EncounterFact table for a specific patient (PatientDurableKey = D0065E0FDF1D40).
SELECT *
FROM deid_uf.EncounterFact
WHERE PatientDurableKey = 'D0065E0FDF1D40'
AND IsHospitalAdmission = 1
On the exact same day, this patient had two separate encounters that are classified as hospital admissions, one of them in which it looks like was only created for administration of the flu vaccine, whereas the other was the real admission. We can pick the encounter that is most likely the real admission encounter by looking at the admission source and discharge disposition. The flu vaccine encounter had no admission source or discharge disposition, while the real admission has an admission type of “Emergency” and discharge disposition of “Discharged to Home or Self Care (Routine Discharge)”. To identify the appropriate encounters, limit your query to patients that did not have a discharge disposition of "*Unspecified", like this:
SELECT *
FROM deid_uf.EncounterFact
WHERE PatientDurableKey = 'D0065E0FDF1D40'
AND IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'With that out of the way, back to the exercise: How many inpatient encounters are there for patients admitted with a diagnosis of COPD from 2016-present?
SELECT count(*) as N
FROM deid_uf.EncounterFact
INNER 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'How many unique medications (MedicationGenericName) were ordered for these patients?
SELECT count(DISTINCT MedicationGenericName)
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
Tally how often these medications (MedicationGenericName) were ordered and list them in descending order of frequency.
SELECT MedicationGenericName,
count(*) AS N
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
GROUP BY MedicationGenericName
ORDER BY N DESC
In the prior exercise, a lot of the same core medications show up with different dosages. Maybe you aren’t so interested right now in the exact dosages. Join the MedicationDim table so that you can try the same queries with the SimpleGenericName. In two separate queries, count the total number of distinct SimpleGenericName medications, and then count how often each was ordered.
SELECT count(DISTINCT SimpleGenericName) AS N
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
LEFT JOIN deid_uf.MedicationDim ON MedicationDim.MedicationKey = MedicationOrderFact.MedicationKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
SELECT SimpleGenericName,
count(*) AS N
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
LEFT JOIN deid_uf.MedicationDim ON MedicationDim.MedicationKey = MedicationOrderFact.MedicationKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified'
GROUP BY SimpleGenericName
ORDER BY N DESC
What happens if the same medication is ordered/re-ordered multiple times for a patient during their encounter? Unless you take this into account, it will show up multiple times in your result. If you haven’t already, modify your code to count each medication only once per encounter. You can do this type of grouping/summarization in many different ways. You can accomplish it in SQL, R, or Stata. If you’d like to attempt it in SQL, you can use what is called a Subselect query. Basically, you can SELECT something FROM (the results of another SELECT query).
Look at this example to get you started.
SELECT EncounterKey
FROM (SELECT EncounterKey, MedicationKey
FROM deid_uf.MedicationOrderFact
WHERE MedicationGenericName LIKE '%leuprolide%') AS nameofsomealiasThe results represent the number of encounters in which each medication was ordered.
SELECT simplegenericname,
count(SimpleGenericName) AS N
FROM (SELECT DISTINCT EncounterFact.EncounterKey, SimpleGenericName
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
LEFT JOIN deid_uf.MedicationDim ON MedicationDim.MedicationKey = MedicationOrderFact.MedicationKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified') AS test
GROUP BY SimpleGenericName
ORDER BY N DESC
What are the most common Therapeutic Classes, Pharmacy Classes, and Pharmacy Subclasses ordered for these patients? Write these as 3 separate queries, counting the number of encounters in which each was ordered.
SELECT DISTINCT TherapeuticClass,
count(*) AS N
FROM (SELECT DISTINCT EncounterFact.EncounterKey, TherapeuticClass
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
LEFT JOIN deid_uf.MedicationDim ON MedicationDim.MedicationKey = MedicationOrderFact.MedicationKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND DischargeDisposition <> '*Unspecified') AS test
GROUP BY TherapeuticClass
ORDER BY N DESC
SELECT DISTINCT PharmaceuticalClass,
count(*) AS N
FROM (SELECT DISTINCT EncounterFact.EncounterKey, PharmaceuticalClass
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
LEFT JOIN deid_uf.MedicationDim ON MedicationDim.MedicationKey = MedicationOrderFact.MedicationKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1) AS test
GROUP BY PharmaceuticalClass
ORDER BY N DESC
SELECT DISTINCT PharmaceuticalSubClass,
count(*) AS N
FROM (SELECT DISTINCT EncounterFact.EncounterKey, PharmaceuticalSubclass
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
LEFT JOIN deid_uf.MedicationDim ON MedicationDim.MedicationKey = MedicationOrderFact.MedicationKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1) AS test
GROUP BY PharmaceuticalSubclass
ORDER BY N DESC
In what percent of all COPD exacerbation admissions were antibiotics (only oral or intravenous) also ordered? First, find the total number of COPD Admissions, which you have already done above, and then find the number of admissions that had an antibiotic ordered. There are many ways to do this. You could write two separate SQL queries and just divide the results in R or STATA to get the percent. You can also do it all in SQL with a single query. There is no “right” way, just whichever way that gets you the answer quickest with whatever you tools with which you are most comfortable.
SELECT count(DISTINCT EncounterKey) as AllPatients
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'
SELECT count(DISTINCT MedicationOrderFact.EncounterKey) as Antibiotics
FROM deid_uf.EncounterFact
LEFT JOIN deid_uf.DiagnosisTerminologyDim
ON DiagnosisTerminologyDim.DiagnosisKey = EncounterFact.PrimaryDiagnosisKey
LEFT JOIN deid_uf.MedicationOrderFact ON MedicationOrderFact.EncounterKey = EncounterFact.EncounterKey
WHERE year(EncounterFact.DateKeyValue) >= 2016
AND DiagnosisTerminologyDim.Type = 'ICD-10-CM'
AND DiagnosisTerminologyDim.Value = 'J44.1'
AND EncounterFact.IsHospitalAdmission = 1
AND MedicationTherapeuticClass = 'ANTIBIOTICS'
AND (MedicationRoute = 'Oral' OR MedicationRoute = 'Intravenous')
## [1] "95.21%"Modify the Measurements section to include at least two different medication-related measurements. Decide whether it will make most sense to specify a therapeutic class of some sort, a grouper, or a medication name. Also consider if you’d like to specify whether the medication should be linked to a specific encounter or date range or some other factor, depending on what makes sense for your project.
Before you start coding, modify your Table 1 design to include rows for the two or more medication-related measurements. Decide whether it makes most sense to have “ever”, “number of orders”, “number of administrations”, or some other way to describe those measurements in your study sample.
Refine your Table 1 SQL/R/Stata code to obtain the results needed to fill in the new medication-related rows in your Table 1.