drop table ALINE_PAIRED_DATA_APR15; create table ALINE_PAIRED_DATA_APR15 as with treated as (select distinct m.icustay_id as icustay_id_t ,m.n1 ,ICU_LOS_DAY ICU_LOS_DAY_T ,HOSPITAL_LOS_DAY HOSPITAL_LOS_DAY_T ,HOSP_EXP_FLG HOSP_EXP_FLG_T ,ICU_EXP_FLG ICU_EXP_FLG_T ,AGE AGE_T ,GENDER_NUM GENDER_NUM_T ,GENDER GENDER_T ,ETHNIC_GROUP ETHNIC_GROUP_T ,WEIGHT_FIRST WEIGHT_FIRST_T ,BMI BMI_T ,SAPSI_FIRST SAPSI_FIRST_T ,SOFA_FIRST SOFA_FIRST_T ,SERVICE_UNIT SERVICE_UNIT_T ,SERVICE_NUM SERVICE_NUM_T ,DAY_ICU_INTIME DAY_ICU_INTIME_T ,DAY_ICU_INTIME_NUM DAY_ICU_INTIME_NUM_T ,HOUR_ICU_INTIME HOUR_ICU_INTIME_T ,MORT_DAY MORT_DAY_T ,DAY_28_FLG DAY_28_FLG_T ,MORT_DAY_CENSORED MORT_DAY_CENSORED_T ,CENSOR_FLG CENSOR_FLG_T ,VENT_DAY VENT_DAY_T ,VENT_FREE_DAY VENT_FREE_DAY_T ,ANES_FLG ANES_FLG_T ,ANES_DAY ANES_DAY_T ,ANES_FREE_DAY ANES_FREE_DAY_T ,FENTANYL_FLG FENTANYL_FLG_T ,MIDAZOLAM_FLG MIDAZOLAM_FLG_T ,PROPOFOL_FLG PROPOFOL_FLG_T ,DILAUDID_FLG DILAUDID_FLG_T ,SEPSIS_FLG SEPSIS_FLG_T ,CHF_FLG CHF_FLG_T ,AFIB_FLG AFIB_FLG_T ,RENAL_FLG RENAL_FLG_T ,LIVER_FLG LIVER_FLG_T ,COPD_FLG COPD_FLG_T ,CAD_FLG CAD_FLG_T ,STROKE_FLG STROKE_FLG_T ,MAL_FLG MAL_FLG_T ,RESP_FLG RESP_FLG_T ,ARDS_FLG ARDS_FLG_T ,PNEUMONIA_FLG PNEUMONIA_FLG_T ,MAP_1ST MAP_1ST_T ,HR_1ST HR_1ST_T ,TEMP_1ST TEMP_1ST_T ,SPO2_1ST SPO2_1ST_T ,CVP_1ST CVP_1ST_T ,ABG_COUNT ABG_COUNT_T ,WBC_FIRST WBC_FIRST_T ,HGB_FIRST HGB_FIRST_T ,PLATELET_FIRST PLATELET_FIRST_T ,SODIUM_FIRST SODIUM_FIRST_T ,POTASSIUM_FIRST POTASSIUM_FIRST_T ,TCO2_FIRST TCO2_FIRST_T ,CHLORIDE_FIRST CHLORIDE_FIRST_T ,BUN_FIRST BUN_FIRST_T ,CREATININE_FIRST CREATININE_FIRST_T ,GLUCOSE_FIRST GLUCOSE_FIRST_T ,CALCIUM_FIRST CALCIUM_FIRST_T ,MAGNESIUM_FIRST MAGNESIUM_FIRST_T ,PHOSPHATE_FIRST PHOSPHATE_FIRST_T ,AST_FIRST AST_FIRST_T ,ALT_FIRST ALT_FIRST_T ,LDH_FIRST LDH_FIRST_T ,BILIRUBIN_FIRST BILIRUBIN_FIRST_T ,ALP_FIRST ALP_FIRST_T ,ALBUMIN_FIRST ALBUMIN_FIRST_T ,TROPONIN_T_FIRST TROPONIN_T_FIRST_T ,CK_FIRST CK_FIRST_T ,BNP_FIRST BNP_FIRST_T ,LACTATE_FIRST LACTATE_FIRST_T ,PH_FIRST PH_FIRST_T ,SVO2_FIRST SVO2_FIRST_T ,PO2_FIRST PO2_FIRST_T ,PCO2_FIRST PCO2_FIRST_T ,IV_DAY_1 IV_DAY_1_T ,RBC_DAY_1 RBC_DAY_1_T ,DNR_ADM_FLG DNR_ADM_FLG_T ,DNR_SWITCH_FLG DNR_SWITCH_FLG_T ,CMO_SWITCH_FLG CMO_SWITCH_FLG_T ,DNR_CMO_SWITCH_FLG DNR_CMO_SWITCH_FLG_T ,ALINE_DURATION ALINE_DURATION_T from aline_match m join aline_mimic_data_april15 d on m.icustay_id=d.icustay_id and m.treated=1 ) --select count(*) from treated; , untreated as (select m.icustay_id as icustay_id_u ,m.id ,ICU_LOS_DAY ICU_LOS_DAY_U ,HOSPITAL_LOS_DAY HOSPITAL_LOS_DAY_U ,HOSP_EXP_FLG HOSP_EXP_FLG_U ,ICU_EXP_FLG ICU_EXP_FLG_U ,AGE AGE_U ,GENDER_NUM GENDER_NUM_U ,GENDER GENDER_U ,ETHNIC_GROUP ETHNIC_GROUP_U ,WEIGHT_FIRST WEIGHT_FIRST_U ,BMI BMI_U ,SAPSI_FIRST SAPSI_FIRST_U ,SOFA_FIRST SOFA_FIRST_U ,SERVICE_UNIT SERVICE_UNIT_U ,SERVICE_NUM SERVICE_NUM_U ,DAY_ICU_INTIME DAY_ICU_INTIME_U ,DAY_ICU_INTIME_NUM DAY_ICU_INTIME_NUM_U ,HOUR_ICU_INTIME HOUR_ICU_INTIME_U ,MORT_DAY MORT_DAY_U ,DAY_28_FLG DAY_28_FLG_U ,MORT_DAY_CENSORED MORT_DAY_CENSORED_U ,CENSOR_FLG CENSOR_FLG_U ,VENT_DAY VENT_DAY_U ,VENT_FREE_DAY VENT_FREE_DAY_U ,ANES_FLG ANES_FLG_U ,ANES_DAY ANES_DAY_U ,ANES_FREE_DAY ANES_FREE_DAY_U ,FENTANYL_FLG FENTANYL_FLG_U ,MIDAZOLAM_FLG MIDAZOLAM_FLG_U ,PROPOFOL_FLG PROPOFOL_FLG_U ,DILAUDID_FLG DILAUDID_FLG_U ,SEPSIS_FLG SEPSIS_FLG_U ,CHF_FLG CHF_FLG_U ,AFIB_FLG AFIB_FLG_U ,RENAL_FLG RENAL_FLG_U ,LIVER_FLG LIVER_FLG_U ,COPD_FLG COPD_FLG_U ,CAD_FLG CAD_FLG_U ,STROKE_FLG STROKE_FLG_U ,MAL_FLG MAL_FLG_U ,RESP_FLG RESP_FLG_U ,ARDS_FLG ARDS_FLG_U ,PNEUMONIA_FLG PNEUMONIA_FLG_U ,MAP_1ST MAP_1ST_U ,HR_1ST HR_1ST_U ,TEMP_1ST TEMP_1ST_U ,SPO2_1ST SPO2_1ST_U ,CVP_1ST CVP_1ST_U ,ABG_COUNT ABG_COUNT_U ,WBC_FIRST WBC_FIRST_U ,HGB_FIRST HGB_FIRST_U ,PLATELET_FIRST PLATELET_FIRST_U ,SODIUM_FIRST SODIUM_FIRST_U ,POTASSIUM_FIRST POTASSIUM_FIRST_U ,TCO2_FIRST TCO2_FIRST_U ,CHLORIDE_FIRST CHLORIDE_FIRST_U ,BUN_FIRST BUN_FIRST_U ,CREATININE_FIRST CREATININE_FIRST_U ,GLUCOSE_FIRST GLUCOSE_FIRST_U ,CALCIUM_FIRST CALCIUM_FIRST_U ,MAGNESIUM_FIRST MAGNESIUM_FIRST_U ,PHOSPHATE_FIRST PHOSPHATE_FIRST_U ,AST_FIRST AST_FIRST_U ,ALT_FIRST ALT_FIRST_U ,LDH_FIRST LDH_FIRST_U ,BILIRUBIN_FIRST BILIRUBIN_FIRST_U ,ALP_FIRST ALP_FIRST_U ,ALBUMIN_FIRST ALBUMIN_FIRST_U ,TROPONIN_T_FIRST TROPONIN_T_FIRST_U ,CK_FIRST CK_FIRST_U ,BNP_FIRST BNP_FIRST_U ,LACTATE_FIRST LACTATE_FIRST_U ,PH_FIRST PH_FIRST_U ,SVO2_FIRST SVO2_FIRST_U ,PO2_FIRST PO2_FIRST_U ,PCO2_FIRST PCO2_FIRST_U ,IV_DAY_1 IV_DAY_1_U ,RBC_DAY_1 RBC_DAY_1_U ,DNR_ADM_FLG DNR_ADM_FLG_U ,DNR_SWITCH_FLG DNR_SWITCH_FLG_U ,CMO_SWITCH_FLG CMO_SWITCH_FLG_U ,DNR_CMO_SWITCH_FLG DNR_CMO_SWITCH_FLG_U ,ALINE_DURATION ALINE_DURATION_U from aline_match m join aline_mimic_data_april15 d on m.icustay_id=d.icustay_id and m.treated=0 ) --select * from untreated; , matched as (select t.* , u.* from treated t join untreated u on t.n1=u.id ) select * from matched; ------- Aline duration analysis --------- create table aline_duration_data_apr15 as select m.icustay_id ,ALINE_FLG ,ALINE_DURATION ,DAY_28_FLG ,ICU_LOS_DAY ,HOSPITAL_LOS_DAY ,HOSP_EXP_FLG ,ICU_EXP_FLG from aline_match m join aline_mimic_data_april15 d on m.icustay_id=d.icustay_id;