clear all

* Set working directory
cd "M:\Epi231\Stata Code\"

capture log close
log using "Lab 6.log", replace

********************************************************************************

* 2 Import Labs

#delimit ;
local sql
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'
          );
#delimit cr

odbc load, exec("`sql'") dsn("DEID") clear

save labs_raw, replace

* 2.2 Import Antibiotics
#delimit ;
local sql
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');
#delimit cr

odbc load, exec("`sql'") dsn("DEID") clear

save abx_raw, replace

********************************************************************************

/* Note: There are many ways to do this - here is one. Egen is your friend when 
collapsing long form data into one row per person. If you don't understand
why certain steps are taken, browse the data to see what each line of code is
doing and it will become clear. */

* 3 Number of Labs by Type

use labs_raw, clear

distinct PatientDurableKey EncounterKey

keep PatientDurableKey EncounterKey LabTestKey ProcedureName
duplicates drop

distinct PatientDurableKey EncounterKey LabTestKey ProcedureName
/* You can see here we don't have 4122 unique LabTestKeys as we would expect.
In a real study, you would further investigate these duplicates! */

/* The total function sums the number of times the expression inside the 
parentheses is true. The by (or bysort) ensures this sum is per unique 
patient/encounter combination. */

bysort PatientDurableKey EncounterKey: egen cbc = total(ProcedureName=="COMPLETE BLOOD COUNT (INCLUDES PLATELET COUNT)")
bysort PatientDurableKey EncounterKey: egen chem = total(strmatch(ProcedureName, "*METABOLIC PANEL*"))
	/* Captures Comprehensive Metabolic Panel and Basic Metabolic Panel */
bysort PatientDurableKey EncounterKey: egen vbg = total(ProcedureName=="VENOUS BLOOD GAS W/LACTATE")
bysort PatientDurableKey EncounterKey: egen abg = total(ProcedureName=="ARTERIAL BLOOD GAS W/LACTATE")
bysort PatientDurableKey EncounterKey: egen glucose_poct = total(ProcedureName=="POCT GLUCOSE, FINGERSTICK")

/* The variables created by the total function in egen are constant for each 
PatientDurableKey and EncounterKey. We need to drop the variables that are not 
constant within PatientDurableKey and EncounterKey to be able to collapse
the dataset. Then we can drop the duplicates and are left with one row 
per patient/encounter combination. */

drop ProcedureName LabTestKey
duplicates drop

distinct PatientDurableKey EncounterKey

/* You could save this dataset for later use. We aren't using these variables later 
in this lab, so I do not do so here. If this was for my own project, I would not yet
drop the extraneous variables/duplicates. I would create all the variables I needed
from the lab data (continued below) before collapsing to one row per patient/encounter. */

********************************************************************************

* 4 ABGs

use labs_raw, clear

* Create a dummy variable marking all ABGs
gen abg = ProcedureName=="ARTERIAL BLOOD GAS W/LACTATE"

/* Use the max function in egen to turn this into a 1/0 that is constant by 
patient/encounter (if a patient/encounter ever had an ABG, it will become 1
because the maximum value for that patient/encounter is 1.) */
bysort PatientDurableKey EncounterKey: egen abg_any = max(abg)

* Stata did us the favor of recognizing the date variables as dates, so we do not need to do anything to transform them

* Create a dummy variable marking ABGs completed on admission date
gen abg_firstday_raw = (ProcedureName=="ARTERIAL BLOOD GAS W/LACTATE" & AdmissionDateKeyValue == ResultDateKeyValue)
/* I often label variables that I need for egen functions, but will drop later "_raw"
to keep them straight from the final variables I need to keep. */

* Use the max function to create a constant within patient/encounter
bysort PatientDurableKey EncounterKey: egen abg_firstday = max(abg_firstday_raw)

********************************************************************************

* 5 ABG results and WBC Counts
/* We are not yet dropping the duplicates to match the R key because we need to add
more variables to complete our dataset. R allows you to have multiple datasets 
loaded at once, but in Stata we can only have one at a time. */

/* Note: You can do this without creating the date/time variable, but we do so 
here to demonstrate the code. */

* Create date/time variable 
gen double labtime=clock(ResultTimeOfDayKeyValue, "hm")
format labtime %tcHH:MM

gen double labdatetime=ResultDateKeyValue*24*60*60*1000+labtime
format labdatetime %tc

* Sort by date/time within Patient and Encounter
sort PatientDurableKey EncounterKey labdatetime

* _n labels each WBC with a row number within that patient and encounter
by PatientDurableKey EncounterKey: gen wbc_n = _n if ComponentName=="WBC Count"
/* If you browse, you'll see the row numbers are only filled in for rows that contain 
a WBC Count, but the row numbers are assigned based on the rows for all labs within an 
encounter. For example, the row number for the first WBC for the first encounter is 
35 (not 1) because that is row on which the first WBC is found. */

/* Create a variable containing the minimum of the row number for an encounter.
This notes the row for the first WBC for that encounter. The maximum notes the 
row for the last WBC for that encounter. */ 
by PatientDurableKey EncounterKey: egen wbc_first_n = min(wbc_n) if ComponentName=="WBC Count"
by PatientDurableKey EncounterKey: egen wbc_last_n = max(wbc_n) if ComponentName=="WBC Count"

/* Creating a numeric variable with values of WBC to facilitate use of egen max 
function below. */ 
gen wbc = Value if ComponentName=="WBC Count"
destring wbc, replace 

* Create variables containing the first and last values of WBC.
gen wbc_first_raw = wbc if wbc_first_n==wbc_n & wbc_first_n<.
gen wbc_last_raw = wbc if wbc_last_n==wbc_n & wbc_first_n<.

* Make them constant within encounter to facilitate de-duplicating later.
by PatientDurableKey EncounterKey: egen wbc_first = max(wbc_first_raw)
by PatientDurableKey EncounterKey: egen wbc_last = max(wbc_last_raw)
/* Note: Could also use the mean, min, or mode function here. */

* Capture the highest value of WBC during admission
by PatientDurableKey EncounterKey: egen wbc_max = max(wbc) 

/* I tried this, but Stata oddly added on miliseconds and rendered this approach not workable:
* Create a variable containing the date/time of the first WBC completed
by PatientDurableKey EncounterKey: egen wbc_first_datetime = min(labdatetime) if ComponentName=="WBC Count"
format wbc_first_datetime %tc
* Capture the value of the first WBC
gen wbc_first_raw = wbc if wbc_first_datetime==labdatetime */

* Keep necessary variables
keep PatientDurableKey EncounterKey abg_any abg_firstday wbc_first wbc_last wbc_max

* Drop duplicates to keep one row per patient/encounter
duplicates drop

distinct PatientDurableKey EncounterKey

********************************************************************************

* 6 Antibiotics

* Merge antibiotics data to lab data
merge 1:1 PatientDurableKey EncounterKey using abx_raw

distinct PatientDurableKey EncounterKey

/* Note that everyone in the antibiotics dataset received antibiotics 
(see the WHERE clause). Thus, if they were "not matched" and from the "using" dataset (2),
or if they "matched" during the merge (3), we can code them got_abx = 1. */
gen got_abx = _m != 1

* Tab ABG on first day and antibiotics
tab abg_firstday got_abx, row

* Save final dataset for later analysis
save labs_abx_clean, replace

log close