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;