Several research articles have found lab values to be important predictors for clinical outcomes such as readmission (Donze et al., 2013). Lab results are problematic, however, because they are missing in most patients. No lab result type will be present for every patient; for example, in our example, not every patient got blood drawn for lab tests during their visit. Indeed, of the three different types of lab tests present in our data, the most common test was the BNP, drawn in four out of six patients. What do we do with the other two patients?

One way around this is to set up a "flag" for the presence of an abnormal result. This is accomplished in Query Set #6 for the glucose lab test. After the first query adds the Abnml_glucose column with an ALTER TABLE statement, the next query sets the result equal to the number of times that specific lab test exceeds a value of 200 for each patient visit. Notice the multiple AND clauses; they are necessary for selecting the right patient, date, and lab test of interest. So, only visits with an excessive result will have a value greater than zero for this feature. Notice we use the CAST() function to convert the values from TEXT to FLOAT before testing the value:

sqlite> ALTER TABLE MORT_FINAL_2 ADD COLUMN Abnml_glucose INTEGER;

sqlite> UPDATE MORT_FINAL_2 SET Abnml_glucose = 
(SELECT COUNT(*) FROM LABS AS L
WHERE MORT_FINAL_2.Pid = L.Pid
AND MORT_FINAL_2.Visit_date = L.Lab_date
AND L.Lab_name = 'Glucose'
AND CAST(L.Lab_value AS FLOAT) >= 200);

While this solves the missing lab data problem, a limitation of this method is that it treats missing results and normal results as being the same. In Query Set #7, we will study basic methods for filling in missing values.