drop table ALINE_PAIRED_DATA_MAY15; create table ALINE_PAIRED_DATA_MAY15 as with treated as (select m.icustay_id as icustay_id_t , m.n1 ,ICU_LOS_DAY as ICU_LOS_DAY_t ,HOSPITAL_LOS_DAY as HOSPITAL_LOS_DAY_t ,AGE as AGE_t ,GENDER_NUM as GENDER_NUM_t ,GENDER as GENDER_t ,ETHNIC_GROUP as ETHNIC_GROUP_t ,WEIGHT_FIRST as WEIGHT_FIRST_t ,BMI as BMI_t ,SAPSI_FIRST as SAPSI_FIRST_t ,SOFA_FIRST as SOFA_FIRST_t ,SERVICE_UNIT as SERVICE_UNIT_t ,SERVICE_NUM as SERVICE_NUM_t ,DAY_ICU_INTIME as DAY_ICU_INTIME_t ,DAY_ICU_INTIME_NUM as DAY_ICU_INTIME_NUM_t ,HOUR_ICU_INTIME as HOUR_ICU_INTIME_t ,HOSP_EXP_FLG as HOSP_EXP_FLG_t ,ICU_EXP_FLG as ICU_EXP_FLG_t ,MORT_DAY as MORT_DAY_t ,ICU_HOUR_FLG as ICU_HOUR_FLG_t ,DAY_28_FLG as DAY_28_FLG_t ,MORT_DAY_CENSORED as MORT_DAY_CENSORED_t ,CENSOR_FLG as CENSOR_FLG_t ,VENT_FLG as VENT_FLG_t ,VENT_1ST_12HR as VENT_1ST_12HR_t ,VENT_1ST_24HR as VENT_1ST_24HR_t ,VENT_B4_ALINE as VENT_B4_ALINE_t ,VENT_DAY as VENT_DAY_t ,VENT_FREE_DAY as VENT_FREE_DAY_t ,VASO_FLG as VASO_FLG_t ,VASO_DAY as VASO_DAY_t ,VASO_FREE_DAY as VASO_FREE_DAY_t ,VASO_B4_ALINE as VASO_B4_ALINE_t ,ANES_FLG as ANES_FLG_t ,ANES_DAY as ANES_DAY_t ,ANES_FREE_DAY as ANES_FREE_DAY_t ,ANES_B4_ALINE as ANES_B4_ALINE_t ,FENTANYL_FLG as FENTANYL_FLG_t ,MIDAZOLAM_FLG as MIDAZOLAM_FLG_t ,PROPOFOL_FLG as PROPOFOL_FLG_t ,DILAUDID_FLG as DILAUDID_FLG_t ,SEPSIS_FLG as SEPSIS_FLG_t ,CHF_FLG as CHF_FLG_t ,AFIB_FLG as AFIB_FLG_t ,RENAL_FLG as RENAL_FLG_t ,LIVER_FLG as LIVER_FLG_t ,COPD_FLG as COPD_FLG_t ,CAD_FLG as CAD_FLG_t ,STROKE_FLG as STROKE_FLG_t ,MAL_FLG as MAL_FLG_t ,RESP_FLG as RESP_FLG_t ,ARDS_FLG as ARDS_FLG_t ,PNEUMONIA_FLG as PNEUMONIA_FLG_t ,MAP_1ST as MAP_1ST_t ,HR_1ST as HR_1ST_t ,TEMP_1ST as TEMP_1ST_t ,SPO2_1ST as SPO2_1ST_t ,CVP_1ST as CVP_1ST_t ,ABG_COUNT as ABG_COUNT_t ,VBG_COUNT as VBG_COUNT_t ,BG_COUNT as BG_COUNT_t ,HCT_MED as HCT_MED_t ,HCT_LOWEST as HCT_LOWEST_t ,HCT_HIGHEST as HCT_HIGHEST_t ,HCT_ABNORMAL_FLG as HCT_ABNORMAL_FLG_t ,WBC_FIRST as WBC_FIRST_t ,HGB_FIRST as HGB_FIRST_t ,PLATELET_FIRST as PLATELET_FIRST_t ,SODIUM_FIRST as SODIUM_FIRST_t ,POTASSIUM_FIRST as POTASSIUM_FIRST_t ,TCO2_FIRST as TCO2_FIRST_t ,CHLORIDE_FIRST as CHLORIDE_FIRST_t ,BUN_FIRST as BUN_FIRST_t ,CREATININE_FIRST as CREATININE_FIRST_t ,GLUCOSE_FIRST as GLUCOSE_FIRST_t ,CALCIUM_FIRST as CALCIUM_FIRST_t ,MAGNESIUM_FIRST as MAGNESIUM_FIRST_t ,PHOSPHATE_FIRST as PHOSPHATE_FIRST_t ,AST_FIRST as AST_FIRST_t ,ALT_FIRST as ALT_FIRST_t ,LDH_FIRST as LDH_FIRST_t ,BILIRUBIN_FIRST as BILIRUBIN_FIRST_t ,ALP_FIRST as ALP_FIRST_t ,ALBUMIN_FIRST as ALBUMIN_FIRST_t ,TROPONIN_T_FIRST as TROPONIN_T_FIRST_t ,CK_FIRST as CK_FIRST_t ,BNP_FIRST as BNP_FIRST_t ,LACTATE_FIRST as LACTATE_FIRST_t ,PH_FIRST as PH_FIRST_t ,SVO2_FIRST as SVO2_FIRST_t ,PO2_FIRST as PO2_FIRST_t ,PCO2_FIRST as PCO2_FIRST_t ,IV_DAY_1 as IV_DAY_1_t ,IV_DAY_2 as IV_DAY_2_t ,IV_DAY_3 as IV_DAY_3_t ,DNR_ADM_FLG as DNR_ADM_FLG_t ,DNR_SWITCH_FLG as DNR_SWITCH_FLG_t ,CMO_SWITCH_FLG as CMO_SWITCH_FLG_t ,DNR_CMO_SWITCH_FLG as DNR_CMO_SWITCH_FLG_t ,ALINE_DURATION as ALINE_DURATION_t from aline_match m join ALINE_MIMIC_DATA_MAY15 d on m.icustay_id=d.icustay_id and m.treated=1 ) --select * from treated; , untreated as (select m.icustay_id as icustay_id_u , m.id ,ICU_LOS_DAY as ICU_LOS_DAY_u ,HOSPITAL_LOS_DAY as HOSPITAL_LOS_DAY_u ,AGE as AGE_u ,GENDER_NUM as GENDER_NUM_u ,GENDER as GENDER_u ,ETHNIC_GROUP as ETHNIC_GROUP_u ,WEIGHT_FIRST as WEIGHT_FIRST_u ,BMI as BMI_u ,SAPSI_FIRST as SAPSI_FIRST_u ,SOFA_FIRST as SOFA_FIRST_u ,SERVICE_UNIT as SERVICE_UNIT_u ,SERVICE_NUM as SERVICE_NUM_u ,DAY_ICU_INTIME as DAY_ICU_INTIME_u ,DAY_ICU_INTIME_NUM as DAY_ICU_INTIME_NUM_u ,HOUR_ICU_INTIME as HOUR_ICU_INTIME_u ,HOSP_EXP_FLG as HOSP_EXP_FLG_u ,ICU_EXP_FLG as ICU_EXP_FLG_u ,MORT_DAY as MORT_DAY_u ,ICU_HOUR_FLG as ICU_HOUR_FLG_u ,DAY_28_FLG as DAY_28_FLG_u ,MORT_DAY_CENSORED as MORT_DAY_CENSORED_u ,CENSOR_FLG as CENSOR_FLG_u ,VENT_FLG as VENT_FLG_u ,VENT_1ST_12HR as VENT_1ST_12HR_u ,VENT_1ST_24HR as VENT_1ST_24HR_u ,VENT_B4_ALINE as VENT_B4_ALINE_u ,VENT_DAY as VENT_DAY_u ,VENT_FREE_DAY as VENT_FREE_DAY_u ,VASO_FLG as VASO_FLG_u ,VASO_DAY as VASO_DAY_u ,VASO_FREE_DAY as VASO_FREE_DAY_u ,VASO_B4_ALINE as VASO_B4_ALINE_u ,ANES_FLG as ANES_FLG_u ,ANES_DAY as ANES_DAY_u ,ANES_FREE_DAY as ANES_FREE_DAY_u ,ANES_B4_ALINE as ANES_B4_ALINE_u ,FENTANYL_FLG as FENTANYL_FLG_u ,MIDAZOLAM_FLG as MIDAZOLAM_FLG_u ,PROPOFOL_FLG as PROPOFOL_FLG_u ,DILAUDID_FLG as DILAUDID_FLG_u ,SEPSIS_FLG as SEPSIS_FLG_u ,CHF_FLG as CHF_FLG_u ,AFIB_FLG as AFIB_FLG_u ,RENAL_FLG as RENAL_FLG_u ,LIVER_FLG as LIVER_FLG_u ,COPD_FLG as COPD_FLG_u ,CAD_FLG as CAD_FLG_u ,STROKE_FLG as STROKE_FLG_u ,MAL_FLG as MAL_FLG_u ,RESP_FLG as RESP_FLG_u ,ARDS_FLG as ARDS_FLG_u ,PNEUMONIA_FLG as PNEUMONIA_FLG_u ,MAP_1ST as MAP_1ST_u ,HR_1ST as HR_1ST_u ,TEMP_1ST as TEMP_1ST_u ,SPO2_1ST as SPO2_1ST_u ,CVP_1ST as CVP_1ST_u ,ABG_COUNT as ABG_COUNT_u ,VBG_COUNT as VBG_COUNT_u ,BG_COUNT as BG_COUNT_u ,HCT_MED as HCT_MED_u ,HCT_LOWEST as HCT_LOWEST_u ,HCT_HIGHEST as HCT_HIGHEST_u ,HCT_ABNORMAL_FLG as HCT_ABNORMAL_FLG_u ,WBC_FIRST as WBC_FIRST_u ,HGB_FIRST as HGB_FIRST_u ,PLATELET_FIRST as PLATELET_FIRST_u ,SODIUM_FIRST as SODIUM_FIRST_u ,POTASSIUM_FIRST as POTASSIUM_FIRST_u ,TCO2_FIRST as TCO2_FIRST_u ,CHLORIDE_FIRST as CHLORIDE_FIRST_u ,BUN_FIRST as BUN_FIRST_u ,CREATININE_FIRST as CREATININE_FIRST_u ,GLUCOSE_FIRST as GLUCOSE_FIRST_u ,CALCIUM_FIRST as CALCIUM_FIRST_u ,MAGNESIUM_FIRST as MAGNESIUM_FIRST_u ,PHOSPHATE_FIRST as PHOSPHATE_FIRST_u ,AST_FIRST as AST_FIRST_u ,ALT_FIRST as ALT_FIRST_u ,LDH_FIRST as LDH_FIRST_u ,BILIRUBIN_FIRST as BILIRUBIN_FIRST_u ,ALP_FIRST as ALP_FIRST_u ,ALBUMIN_FIRST as ALBUMIN_FIRST_u ,TROPONIN_T_FIRST as TROPONIN_T_FIRST_u ,CK_FIRST as CK_FIRST_u ,BNP_FIRST as BNP_FIRST_u ,LACTATE_FIRST as LACTATE_FIRST_u ,PH_FIRST as PH_FIRST_u ,SVO2_FIRST as SVO2_FIRST_u ,PO2_FIRST as PO2_FIRST_u ,PCO2_FIRST as PCO2_FIRST_u ,IV_DAY_1 as IV_DAY_1_u ,IV_DAY_2 as IV_DAY_2_u ,IV_DAY_3 as IV_DAY_3_u ,DNR_ADM_FLG as DNR_ADM_FLG_u ,DNR_SWITCH_FLG as DNR_SWITCH_FLG_u ,CMO_SWITCH_FLG as CMO_SWITCH_FLG_u ,DNR_CMO_SWITCH_FLG as DNR_CMO_SWITCH_FLG_u ,ALINE_DURATION as ALINE_DURATION_u from aline_match m join ALINE_MIMIC_DATA_MAY15 d on m.icustay_id=d.icustay_id and m.treated=0 ) --select * from untreated; , final_table as (select t.* , u.* from treated t join untreated u on t.n1=u.id ) select * from final_table;