*******************************************************************
****************                                   ****************
****************  Part I: Measured EHR continuity  ****************
****************                                   ****************
*******************************************************************;


*******************************************************************
*****  Step 0: Pull out all Encounter claims in CMS and RPDR  *****
*******************************************************************;
data CMSEncounter;
    set C_CDM.Encounter:;
run;

proc SQL;
    CREATE TABLE CMS_Encounter_All AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date>=a.D_Index AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year1 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1 AND a.D_Index+365 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year2 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+366 AND a.D_Index+730 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year3 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+731 AND a.D_Index+1095 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year4 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year5 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year6 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year7 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year8 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year9 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE CMS_Encounter_Year10 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, CMSEncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;
quit;

data RPDREncounter;
    set R_CDM.Encounter_2007
        R_CDM.Encounter_2008
        R_CDM.Encounter_2009
        R_CDM.Encounter_2010
        R_CDM.Encounter_2011
        R_CDM.Encounter_2012
        R_CDM.Encounter_2013
        R_CDM.Encounter_2014
        R_CDM.Encounter_2015
        R_CDM.Encounter_2016
        R_CDM.Encounter_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE RPDR_Encounter_All AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date>=a.D_Index AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year1 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1 AND a.D_Index+365 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year2 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+366 AND a.D_Index+730 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year3 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+731 AND a.D_Index+1095 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year4 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year5 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year6 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year7 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year8 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year9 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;

    CREATE TABLE RPDR_Encounter_Year10 AS
    SELECT DISTINCT a.D_Index, a.D_Censor, b.*
    FROM EHR.Cohort AS a, RPDREncounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650 AND
          b.Admit_Date<=a.D_Censor
    ORDER BY b.PatID, a.D_Index, b.Admit_Date, b.Discharge_Date;
quit;

****************************************************************************************************************
*****  Step 1: In CMS - Delete Enc_Type="AV" if Admit_Date Between "IP" Admit_Date+1 AND Discharge_Date-1  *****
****************************************************************************************************************;
%macro InvalidAV(Year);
data CMS_Encounter_AV_&Year CMS_Encounter_IP_ED_&Year;
    set CMS_Encounter_&Year;

    if Enc_Type="AV" then output CMS_Encounter_AV_&Year;
    if Enc_Type in ("IP", "ED") then output CMS_Encounter_IP_ED_&Year;
run;

proc SQL;
    CREATE TABLE AV AS
    SELECT DISTINCT PatID, Admit_Date
    FROM CMS_Encounter_AV_&Year
    WHERE Enc_Type="AV";

    CREATE TABLE IP AS
    SELECT DISTINCT PatID, Admit_Date AS D_IP_Admit, Discharge_Date AS D_IP_Disch, Enc_Type AS Enc_Type_IP
    FROM CMS_Encounter_IP_ED_&Year
    WHERE Enc_Type="IP";

    CREATE TABLE Invalid_AV AS
    SELECT DISTINCT a.PatID, a.Admit_Date
    FROM AV AS a, IP AS b
    WHERE a.PatID=b.PatID AND
          a.Admit_Date>=b.D_IP_Admit+1 AND
          a.Admit_Date<=b.D_IP_Disch-1
    ORDER BY a.PatID, a.Admit_Date;
quit;

proc sort data=CMS_Encounter_AV_&Year;
    by PatID Admit_Date Discharge_Date;
run;

data CMS_Encounter_AV_&Year;
    merge CMS_Encounter_AV_&Year(in=a)
          Invalid_AV(in=b);
    by PatID Admit_Date;

    if a and b then delete;
run;

proc datasets;
    delete AV IP Invalid_AV;
quit;
%mend;
%InvalidAV(All) ;
%InvalidAV(Year1)
%InvalidAV(Year2)
%InvalidAV(Year3)
%InvalidAV(Year4)
%InvalidAV(Year5)
%InvalidAV(Year6)
%InvalidAV(Year7)
%InvalidAV(Year8);
%InvalidAV(Year9);
%InvalidAV(Year10);

**************************************************************************************************
*****  Step 2: In both CMS and RPDR - New var "New_Enc_Type" - AV + ED = Outpat, IP = InPat  *****
**************************************************************************************************;
%macro NewType(Year);
data CMS_Encounter_NewType_&Year;
    set CMS_Encounter_AV_&Year
        CMS_Encounter_IP_ED_&Year;

    if Enc_Type in ("AV", "ED") then New_Enc_Type="OutPat";
    else if Enc_Type="IP" then New_Enc_Type="InPat";
run;

data RPDR_Encounter_NewType_&Year;
    set RPDR_Encounter_&Year;

    if Enc_Type in ("AV", "ED") then New_Enc_Type="OutPat";
    else if Enc_Type="IP" then New_Enc_Type="InPat";

    if New_Enc_Type^='';
run;
%mend;
%NewType(All);
%NewType(Year1)
%NewType(Year2)
%NewType(Year3)
%NewType(Year4)
%NewType(Year5)
%NewType(Year6)
%NewType(Year7)
%NewType(Year8);
%NewType(Year9);
%NewType(Year10);

******************************************************************************************************
*****  Step 3: Merge each RPDR Encounter with CMS based on D_Admit and D_Disch and New_Enc_Type  *****
******************************************************************************************************;
%macro Match(Year);
proc SQL;
    CREATE TABLE RPDR_OutPat_&Year AS
    SELECT DISTINCT a.*
    FROM RPDR_Encounter_NewType_&Year AS a, CMS_Encounter_NewType_&Year AS b
    WHERE a.PatID=b.PatID AND
          a.New_Enc_Type="OutPat" AND
          b.New_Enc_Type="OutPat" AND
          a.Admit_Date=b.Admit_Date
    ORDER BY a.PatID, a.Encounter_Num, a.Admit_Date, a.Discharge_Date;

    CREATE TABLE RPDR_InPat_&Year AS
    SELECT DISTINCT a.*
    FROM RPDR_Encounter_NewType_&Year AS a, CMS_Encounter_NewType_&Year AS b
    WHERE a.PatID=b.PatID AND
          a.New_Enc_Type="InPat" AND
          b.New_Enc_Type="InPat" AND
          a.Admit_Date=b.Admit_Date AND
          a.Discharge_Date=b.Discharge_Date
    ORDER BY a.PatID, a.Encounter_Num, a.Admit_Date, a.Discharge_Date;
quit;

proc freq data=RPDR_Encounter_NewType_&Year;
    table New_Enc_Type;
run;
%mend;
%Match(All);
%Match(Year1);
%Match(Year2);
%Match(Year3);
%Match(Year4);
%Match(Year5);
%Match(Year6);
%Match(Year7);
%Match(Year8);
%Match(Year9);
%Match(Year10);

********************************************
*****  Step 4: Calculation - New vars  *****
********************************************;
%macro EncCap(Year, Flag, titlename);
proc SQL;
    CREATE TABLE Count_Out_RPDR_&Year AS
    SELECT DISTINCT PatID, COUNT(*) AS Enc_Out_RPDR&Flag
    FROM RPDR_OutPat_&Year
    GROUP BY PatID
    ORDER BY PatID;

    CREATE TABLE Count_In_RPDR_&Year AS
    SELECT DISTINCT PatID, COUNT(*) AS Enc_In_RPDR&Flag
    FROM RPDR_InPat_&Year
    GROUP BY PatID
    ORDER BY PatID;

    CREATE TABLE Count_Out_CMS_&Year AS
    SELECT DISTINCT PatID, COUNT(*) AS Enc_Out_CMS&Flag
    FROM CMS_Encounter_NewType_&Year
    WHERE New_Enc_Type="OutPat"
    GROUP BY PatID
    ORDER BY PatID;

    CREATE TABLE Count_In_CMS_&Year AS
    SELECT DISTINCT PatID, COUNT(*) AS Enc_In_CMS&Flag
    FROM CMS_Encounter_NewType_&Year
    WHERE New_Enc_Type="InPat"
    GROUP BY PatID
    ORDER BY PatID;
quit;

data Enc_Cap_&Year;
    merge EHR.Cohort(in=a keep=PatID)
          Count_Out_RPDR_&Year
          Count_In_RPDR_&Year
          Count_Out_CMS_&Year
          Count_In_CMS_&Year;
    by PatID;

    Enc_Cap&Flag=(Enc_Out_RPDR&Flag/Enc_Out_CMS&Flag + Enc_In_RPDR&Flag/Enc_In_CMS&Flag)/2;

    if Enc_Out_RPDR&Flag^=. and Enc_Out_CMS&Flag^=. and Enc_In_RPDR&Flag=. and Enc_In_CMS&Flag=. then do;
        Enc_Cap&Flag=Enc_Out_RPDR&Flag/Enc_Out_CMS&Flag;
    end;

    else if Enc_Out_RPDR&Flag^=. and Enc_Out_CMS&Flag^=. and Enc_In_RPDR&Flag=. and Enc_In_CMS&Flag^=. then do;
        Enc_Cap&Flag=(Enc_Out_RPDR&Flag/Enc_Out_CMS&Flag)/2;
    end;

    else if Enc_Out_RPDR&Flag=. and Enc_Out_CMS&Flag^=. and Enc_In_RPDR&Flag^=. and Enc_In_CMS&Flag^=. then do;
        Enc_Cap&Flag=(Enc_In_RPDR&Flag/Enc_In_CMS&Flag)/2;
    end;

    else if Enc_Out_CMS&Flag=. and Enc_In_CMS&Flag^=. then do;
        Enc_Cap&Flag=Enc_In_RPDR&Flag/Enc_In_CMS&Flag;
    end;

    if Enc_Cap&Flag>1 then Enc_Cap&Flag=1;

    if a;

    format Enc_Cap&Flag 8.2;
run;
%mend;
%EncCap(All, _All, "All");
%EncCap(Year1, 1, "Year1");
%EncCap(Year2, 2, "Year2");
%EncCap(Year3, 3, "Year3");
%EncCap(Year4, 4, "Year4");
%EncCap(Year5, 5, "Year5");
%EncCap(Year6, 6, "Year6");
%EncCap(Year7, 7, "Year7");
%EncCap(Year8, 8, "Year8");
%EncCap(Year9, 9, "Year9");
%EncCap(Year10, 10, "Year10");

data EHR.Cohort;
    merge EHR.Cohort(in=a)
          Enc_Cap_All;
    by PatID;

    array Missing[*] Enc_Out_RPDR_All Enc_In_RPDR_All Enc_Out_CMS_All Enc_In_CMS_All Enc_Cap_All;

    do i=1 to dim(Missing);
         if Missing[i]=. then Missing[i]=0;
    end;

    drop i;

    if a;
run;

*******************************************************************
***********                                             ***********
***********  Part II: Create EHR continuity predictors  ***********
***********                                             ***********
*******************************************************************;

*******************************************************************************
*****  Step 1: Pull all claims for Covars - Time Since D_Index            *****
*******************************************************************************;
data Diagnosis;
    set R_CDM.Diagnosis_2006
        R_CDM.Diagnosis_2007
        R_CDM.Diagnosis_2008
        R_CDM.Diagnosis_2009
        R_CDM.Diagnosis_2010
        R_CDM.Diagnosis_2011
        R_CDM.Diagnosis_2012
        R_CDM.Diagnosis_2013
        R_CDM.Diagnosis_2014
        R_CDM.Diagnosis_2015
        R_CDM.Diagnosis_2016
        R_CDM.Diagnosis_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE DX_Year0 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index-365 AND a.D_Index
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year1 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+1 AND a.D_Index+365
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year2 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+366 AND a.D_Index+730
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year3 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+731 AND a.D_Index+1095
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year4 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year5 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year6 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year7 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year8 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year9 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE DX_Year10 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_DX, b.Enc_Type, b.DX, b.DX_Type, b.PDX
    FROM EHR.Cohort AS a, Diagnosis AS b
    WHERE a.PatID=b.PatID AND
          b.DX_Type in ("09", "10") AND
          b.Admit_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;
quit;

data Procedures;
    set R_CDM.Procedures_2006
        R_CDM.Procedures_2007
        R_CDM.Procedures_2008
        R_CDM.Procedures_2009
        R_CDM.Procedures_2010
        R_CDM.Procedures_2011
        R_CDM.Procedures_2012
        R_CDM.Procedures_2013
        R_CDM.Procedures_2014
        R_CDM.Procedures_2015
        R_CDM.Procedures_2016
        R_CDM.Procedures_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE PX_Year0 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index-365 AND a.D_Index
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year1 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+1 AND a.D_Index+365
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year2 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+366 AND a.D_Index+730
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year3 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+731 AND a.D_Index+1095
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year4 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year5 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year6 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year7 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year8 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year9 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285
    ORDER BY a.PatID, a.D_Index, b.PX_Date;

    CREATE TABLE PX_Year10 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.PX_Date AS D_PX, b.Enc_Type, b.PX, b.PX_Type, b.Provider_ID
    FROM EHR.Cohort AS a, Procedures AS b
    WHERE a.PatID=b.PatID AND
          b.PX_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650
    ORDER BY a.PatID, a.D_Index, b.PX_Date;
quit;

data Dispensing;
    set R_CDM.Medication_2006
        R_CDM.Medication_2007
        R_CDM.Medication_2008
        R_CDM.Medication_2009
        R_CDM.Medication_2010
        R_CDM.Medication_2011
        R_CDM.Medication_2012
        R_CDM.Medication_2013
        R_CDM.Medication_2014
        R_CDM.Medication_2015
        R_CDM.Medication_2016
        R_CDM.Medication_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE RX_Year0 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index-365 AND a.D_Index
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year1 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+1 AND a.D_Index+365
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year2 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+366 AND a.D_Index+730
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year3 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+731 AND a.D_Index+1095
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year4 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year5 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year6 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year7 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year8 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year9 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;

    CREATE TABLE RX_Year10 AS
    SELECT DISTINCT a.PatID, a.D_Index, a.D_Censor, b.Dispense_Date AS D_RX format mmddyy10., b.Medication AS Generic
    FROM EHR.Cohort AS a, Dispensing AS b
    WHERE a.PatID=b.PatID AND
          b.Dispense_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650
    ORDER BY a.PatID, a.D_Index, b.Dispense_Date;
quit;

data Encounter;
    set R_CDM.Encounter_2006
        R_CDM.Encounter_2007
        R_CDM.Encounter_2008
        R_CDM.Encounter_2009
        R_CDM.Encounter_2010
        R_CDM.Encounter_2011
        R_CDM.Encounter_2012
        R_CDM.Encounter_2013
        R_CDM.Encounter_2014
        R_CDM.Encounter_2015
        R_CDM.Encounter_2016
        R_CDM.Encounter_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE Encounter_Year0 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index-365 AND a.D_Index
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year1 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1 AND a.D_Index+365
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year2 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+366 AND a.D_Index+730
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year3 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+731 AND a.D_Index+1095
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year4 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year5 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year6 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year7 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year8 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year9 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;

    CREATE TABLE Encounter_Year10 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Admit_Date AS D_Admit, b.Discharge_Date AS D_Disch, b.Enc_Type
    FROM EHR.Cohort AS a, Encounter AS b
    WHERE a.PatID=b.PatID AND
          b.Admit_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650
    ORDER BY a.PatID, a.D_Index, b.Admit_Date;
quit;

data Labs;
    set R_CDM.Labs_2006
        R_CDM.Labs_2007
        R_CDM.Labs_2008
        R_CDM.Labs_2009
        R_CDM.Labs_2010
        R_CDM.Labs_2011
        R_CDM.Labs_2012
        R_CDM.Labs_2013
        R_CDM.Labs_2014
        R_CDM.Labs_2015
        R_CDM.Labs_2016
        R_CDM.Labs_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE Lab_Year0 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index-365 AND a.D_Index
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year1 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+1 AND a.D_Index+365
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year2 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+366 AND a.D_Index+730
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year3 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+731 AND a.D_Index+1095
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year4 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year5 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year6 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year7 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year8 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year9 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285
    ORDER BY a.PatID, a.D_Index, b.Result_Date;

    CREATE TABLE Lab_Year10 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Lab_Name, b.Result_Date AS D_Lab
    FROM EHR.Cohort AS a, Labs AS b
    WHERE a.PatID=b.PatID AND
          b.Result_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650
    ORDER BY a.PatID, a.D_Index, b.Result_Date;
quit;

data Vital; /* Jun 02252021 - Smoking is no longer available */
    set R_CDM.Vital_2006
        R_CDM.Vital_2007
        R_CDM.Vital_2008
        R_CDM.Vital_2009
        R_CDM.Vital_2010
        R_CDM.Vital_2011
        R_CDM.Vital_2012
        R_CDM.Vital_2013
        R_CDM.Vital_2014
        R_CDM.Vital_2015
        R_CDM.Vital_2016
        R_CDM.Vital_2017;

    rename Medicare_PatID=PatID;
run;

proc SQL;
    CREATE TABLE Vital_Year0 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index-365 AND a.D_Index
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year1 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+1 AND a.D_Index+365
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year2 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+366 AND a.D_Index+730
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year3 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+731 AND a.D_Index+1095
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year4 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+1096 AND a.D_Index+1460
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year5 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+1461 AND a.D_Index+1825
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year6 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+1826 AND a.D_Index+2190
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year7 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+2191 AND a.D_Index+2555
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year8 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+2556 AND a.D_Index+2920
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year9 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+2921 AND a.D_Index+3285
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;

    CREATE TABLE Vital_Year10 AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Measure_Date AS D_Vital, b.HT, b.WT, b.Diastolic, b.Systolic, b.BMI
    FROM EHR.Cohort AS a, Vital AS b
    WHERE a.PatID=b.PatID AND
          b.Measure_Date BETWEEN a.D_Index+3286 AND a.D_Index+3650
    ORDER BY a.PatID, a.D_Index, b.Measure_Date;
quit;

**************************************
*****  Step 2: Define Vars - DX  *****
**************************************;
%macro Part1(Time);
data Predictor_DX_&Time;
    length Disease $80.;
    set DX_&Time;
    by PatID D_Index D_DX;

    if DX_Type="09" and DX in ("V700", "V7231") then do;
        Disease="Routine Care";
        output;
    end;

    if DX_Type="10" and DX in ("Z0000", "Z0001", "Z01411", "Z01419") then do;
        Disease="Routine Care";
        output;
    end;

    if DX_Type="09" and DX in ("V723", "V700") then do;
        Disease="Medical Exam";
        output;
    end;

    if DX_Type="10" and DX in ("Z0000", "Z0001", "Z01411", "Z01419") then do;
        Disease="Medical Exam";
        output;
    end;

    if DX_Type="09" and DX="V7612" then do; /* DX + PX */
        Disease="Mammography";
        output;
    end;

    if DX_Type="10" and DX="Z1231" then do; /* DX + PX */
        Disease="Mammography";
        output;
    end;

    if DX_Type="09" and DX in ("V048", "V0481", "V066") then do; /* DX + PX */
        Disease="Flu shot";
        output;
    end;

    if DX_Type="10" and DX="3E01340" then do; /* DX + PX */
        Disease="Flu shot";
        output;
    end;

    if DX_Type="09" and DX="V0382" then do; /* DX + PX */
        Disease="Pneumococcal vaccine";
        output;
    end;

    if DX_Type="10" and DX="Z23" then do; /* DX + PX */
        Disease="Pneumococcal vaccine";
        output;
    end;
run;

******************************
*****  Define Vars - PX  *****
******************************;
data Predictor_PX_&Time;
    length Disease $80.;
    set PX_&Time;
    by PatID D_Index D_PX;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do; /* Yinzhu 07022021 - New Definition */
        if PX in ("76083", "76092", "77052", "77057", "77063", "77067", "G0202") then do;
            Disease="Mammography";
            output;
        end;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do;
        if PX in ("Q0091", "P3000", "P3001", "G0123", "G0124", "G0141", "G0143", "G0145", "G0147", "G0148") or
                  "88141"<=PX<="88158" or "88164"<=PX<="88167" or "88174"<=PX<="88175" then do;
            Disease="Pap test";
            output;
        end;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do;
        if PX in ("G0103", "84153") or "84152"<=PX<="84154" then do;
            Disease="PSA test";
            output;
        end;
    end;

    if PX_Type="09" and length(PX)=4 and PX="4523" then do;
        Disease="Colonoscopy";
        output;
    end;

    if PX_Type="10" and length(PX)=7 and PX="0DJD8ZZ" then do;
        Disease="Colonoscopy";
        output;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do;
        if PX in ("G0105", "G0121") or "45378"<=PX<="45392" then do;
            Disease="Colonoscopy";
            output;
        end;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do;
        if PX in ("G0107", "G0328", "82270", "82274") then do;
            Disease="Fecal occult blood test";
            output;
        end;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do; /* DX + PX */
        if "90655"<=PX<="90660" or PX in ("90724", "G0008") then do;
            Disease="Flu shot";
            output;
        end;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do; /* DX + PX */
        if PX in ("90669", "90670", "90732") then do;
            Disease="Pneumococcal vaccine";
            output;
        end;
    end;

    if PX_Type ^in ("09", "10") and length(PX)=5 then do;
        if PX in ("76070", "76075", "76076", "76977") then do;
            Disease="BMD test";
            output;
        end;
    end;
run;
%mend Part1;

**********************************
*****  Flags for Predictors  *****
**********************************;
%macro Part2(Time, Flag);
proc SQL;
    CREATE TABLE &Time._RoutineCare AS
    SELECT DISTINCT PatID, D_Index, 1 AS Routine_Care_Code&Flag
    FROM Predictor_DX_&Time
    WHERE Disease="Routine Care"
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._MedicalExam AS
    SELECT DISTINCT PatID, D_Index, 1 AS Medical_Exam&Flag
    FROM Predictor_DX_&Time
    WHERE Disease="Medical Exam"
    ORDER BY PatID, D_Index;

    CREATE TABLE Mammography_DX AS
    SELECT DISTINCT PatID, D_Index, 1 AS Mammography&Flag
    FROM Predictor_DX_&Time
    WHERE Disease="Mammography"
    ORDER BY PatID, D_Index;

    CREATE TABLE Mammography_PX AS
    SELECT DISTINCT PatID, D_Index, 1 AS Mammography&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="Mammography"
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._PapTest AS
    SELECT DISTINCT PatID, D_Index, 1 AS PapTest&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="Pap test"
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._PSATest AS
    SELECT DISTINCT PatID, D_Index, 1 AS PSATest&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="PSA test"
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Colonoscopy AS
    SELECT DISTINCT PatID, D_Index, 1 AS Colonoscopy&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="Colonoscopy"
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._FecalOccultTest AS
    SELECT DISTINCT PatID, D_Index, 1 AS FecalOccultTest&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="Fecal occult blood test"
    ORDER BY PatID, D_Index;

    CREATE TABLE FluShot_DX AS
    SELECT DISTINCT PatID, D_Index, 1 AS FluShot&Flag
    FROM Predictor_DX_&Time
    WHERE Disease="Flu shot"
    ORDER BY PatID, D_Index;

    CREATE TABLE FluShot_PX AS
    SELECT DISTINCT PatID, D_Index, 1 AS FluShot&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="Flu shot"
    ORDER BY PatID, D_Index;

    CREATE TABLE PneumococcalVaccine_DX AS
    SELECT DISTINCT PatID, D_Index, 1 AS PneumococcalVaccine&Flag
    FROM Predictor_DX_&Time
    WHERE Disease="Pneumococcal vaccine"
    ORDER BY PatID, D_Index;

    CREATE TABLE PneumococcalVaccine_PX AS
    SELECT DISTINCT PatID, D_Index, 1 AS PneumococcalVaccine&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="Pneumococcal vaccine"
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._BMDTest AS
    SELECT DISTINCT PatID, D_Index, 1 AS BMDTest&Flag
    FROM Predictor_PX_&Time
    WHERE Disease="BMD test"
    ORDER BY PatID, D_Index;
quit;

data Mammography_DXPX;
    set Mammography_DX
        Mammography_PX;
    by PatID D_Index;
run;

data FluShot_DXPX;
    set FluShot_DX
        FluShot_PX;
    by PatID D_Index;
run;

data PneumococcalVaccine_DXPX;
    set PneumococcalVaccine_DX
        PneumococcalVaccine_PX;
    by PatID D_Index;
run;

proc SQL;
    CREATE TABLE &Time._Mammography AS
    SELECT DISTINCT *
    FROM Mammography_DXPX
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._FluShot AS
    SELECT DISTINCT *
    FROM FluShot_DXPX
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._PneumococcalVaccine AS
    SELECT DISTINCT *
    FROM PneumococcalVaccine_DXPX
    ORDER BY PatID, D_Index;
quit;
%mend Part2;

***************************************************************************
*****  Step 3: Vars - Repeat_Two_MD, Repeat_Three_MD - Office visits  *****
***************************************************************************;
%macro Part3(Time, Flag);
/* Helen code */
data MDVisit_&Time;
    set PX_&Time;
    by PatID D_Index D_PX;

    if PX_Type ^in ("09", "10") then do;
        if "99201"<=PX<="99205" or
           "99211"<=PX<="99215" or
           "99241"<=PX<="99245" or
           "99271"<=PX<="99275" or
           "99354"<=PX<="99357" or
           "99381"<=PX<="99387" or
           "99391"<=PX<="99397" or
           "99401"<=PX<="99404" or
                    PX ="99420" or
                    PX ="99429" or
                    PX ="99450" or
                    PX ="99455" or
                    PX ="99456" or
                    PX ="99499" then do;
            output;
        end;
    end;

    keep PatID D_Index D_PX Provider_ID;
run;

proc SQL;
    CREATE TABLE Count_Num_MDVisit AS
    SELECT DISTINCT PatID, D_Index, Provider_ID, COUNT(UNIQUE D_PX) AS N_MDVisit
    FROM MDVisit_&Time
    GROUP BY PatID, D_Index, Provider_ID
    ORDER BY PatID, D_Index, Provider_ID;

    CREATE TABLE &Time._Num_MDVisit2 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Repeat_Two_MD&Flag
    FROM Count_Num_MDVisit
    WHERE N_MDVisit>=2
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_MDVisit3 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Repeat_Three_MD&Flag
    FROM Count_Num_MDVisit
    WHERE N_MDVisit>=3
    ORDER BY PatID, D_Index;
quit;
%mend Part3;

********************************
*****  Step 4: Other vars  *****
********************************;
%macro Part4(Time, Flag);
proc SQL;
    CREATE TABLE &Time._A1C AS
    SELECT DISTINCT PatID, D_Index, 1 AS A1C&Flag
    FROM Lab_&Time
    WHERE index(Lab_Name, "A1C")>0 or
          index(Lab_Name, "A1c")>0
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._BMI AS
    SELECT DISTINCT PatID, D_Index, 1 AS BMI&Flag
    FROM Vital_&Time
    WHERE BMI^=.
    ORDER BY PatID, D_Index;

    CREATE TABLE Count_Num_DX AS
    SELECT DISTINCT PatID, D_Index, COUNT(UNIQUE D_DX) AS N_DX
    FROM DX_&Time
    GROUP BY PatID, D_Index
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_DX1 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Reg1_DX&Flag
    FROM Count_Num_DX
    WHERE N_DX>=1
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_DX2 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Reg2_DX&Flag
    FROM Count_Num_DX
    WHERE N_DX>=2
    ORDER BY PatID, D_Index;

    CREATE TABLE Count_Num_OutPat AS
    SELECT DISTINCT PatID, D_Index, COUNT(UNIQUE D_Admit) AS N_Encounter
    FROM Encounter_&Time
    WHERE Enc_Type="AV"
    GROUP BY PatID, D_Index
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_OPT1 AS
    SELECT DISTINCT PatID, D_Index, 1 AS OPT1_Visit&Flag
    FROM Count_Num_OutPat
    WHERE N_Encounter>=1
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_OPT2 AS
    SELECT DISTINCT PatID, D_Index, 1 AS OPT2_Visit&Flag
    FROM Count_Num_OutPat
    WHERE N_Encounter>=2
    ORDER BY PatID, D_Index;

    CREATE TABLE Count_Num_InPat AS
    SELECT DISTINCT PatID, D_Index, COUNT(UNIQUE D_Admit) AS N_Encounter
    FROM Encounter_&Time
    WHERE Enc_Type="IP"
    GROUP BY PatID, D_Index
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_INP1 AS
    SELECT DISTINCT PatID, D_Index, 1 AS INP1_Visit&Flag
    FROM Count_Num_InPat
    WHERE N_Encounter>=1
    ORDER BY PatID, D_Index;


    CREATE TABLE Count_Num_Med AS
    SELECT DISTINCT PatID, D_Index, COUNT(*) AS N_Med
    FROM RX_&Time._OutPat
    GROUP BY PatID, D_Index
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_Med1 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Med1_Visit&Flag
    FROM Count_Num_Med
    WHERE N_Med>=1
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_Med2 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Med2_Visit&Flag
    FROM Count_Num_Med
    WHERE N_Med>=2
    ORDER BY PatID, D_Index;


    CREATE TABLE Count_Num_RegMed AS
    SELECT DISTINCT PatID, D_Index, COUNT(UNIQUE D_RX) AS N_Med
    FROM RX_&Time
    GROUP BY PatID, D_Index
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_RegMed1 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Reg1_Med&Flag
    FROM Count_Num_RegMed
    WHERE N_Med>=1
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_RegMed2 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Reg2_Med&Flag
    FROM Count_Num_RegMed
    WHERE N_Med>=2
    ORDER BY PatID, D_Index;

    CREATE TABLE Count_Num_Basic AS
    SELECT DISTINCT PatID, D_Index, COUNT(UNIQUE D_Vital) AS N_Vital  /* Take out smoking */
    FROM Vital_&Time
    WHERE HT^=. OR
          WT^=. OR
          Diastolic^=. OR
          Systolic^=. OR
          BMI^=.
    GROUP BY PatID, D_Index
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_Basic1 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Reg1_Basic&Flag
    FROM Count_Num_Basic
    WHERE N_Vital>=1
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Num_Basic2 AS
    SELECT DISTINCT PatID, D_Index, 1 AS Reg2_Basic&Flag
    FROM Count_Num_Basic
    WHERE N_Vital>=2
    ORDER BY PatID, D_Index;
quit;
%mend Part4;

**************************************************
*****  Step 5: Josh 08192016 - Two new vars  *****
**************************************************;
%macro Part5(Time, Flag);
proc SQL;
    CREATE TABLE Count_Num_Encounter_&Flag AS
    SELECT DISTINCT PatID, D_Index, Enc_Type, COUNT(UNIQUE D_Admit) AS N_Encounter
    FROM Encounter_&Time
    GROUP BY PatID, D_Index, Enc_Type
    ORDER BY PatID, D_Index, Enc_Type;

    CREATE TABLE &Time._EDVisit AS
    SELECT DISTINCT PatID, D_Index, 1 AS ED1_Visit&Flag
    FROM Encounter_&Time
    WHERE Enc_Type="ED"
    ORDER BY PatID, D_Index;
quit;

data OneEncType&Flag;
    set Count_Num_Encounter_&Flag;
    by PatID D_Index;

    if first.PatID and last.PatID;
run;

proc SQL;
    CREATE TABLE &Time._Only_1ED AS
    SELECT DISTINCT PatID, D_Index, 1 AS Only_1ED_Visit&Flag
    FROM OneEncType&Flag
    WHERE Enc_Type="ED" AND
          N_Encounter=1
    ORDER BY PatID, D_Index;

    CREATE TABLE &Time._Only_ED AS
    SELECT DISTINCT PatID, D_Index, 1 AS Only_ED_Visit&Flag
    FROM OneEncType&Flag
    WHERE Enc_Type="ED"
    ORDER BY PatID, D_Index;
quit;
%mend Part5;

%Part1(Year0);
%Part2(Year0, R0);
%Part3(Year0, R0);
%Part4(Year0, R0);
%Part5(Year0, R0);

%Part1(Year1);
%Part2(Year1, R1);
%Part3(Year1, R1);
%Part4(Year1, R1);
%Part5(Year1, R1);

%Part1(Year2);
%Part2(Year2, R2);
%Part3(Year2, R2);
%Part4(Year2, R2);
%Part5(Year2, R2);

%Part1(Year3);
%Part2(Year3, R3);
%Part3(Year3, R3);
%Part4(Year3, R3);
%Part5(Year3, R3);

%Part1(Year4);
%Part2(Year4, R4);
%Part3(Year4, R4);
%Part4(Year4, R4);
%Part5(Year4, R4);

%Part1(Year5);
%Part2(Year5, R5);
%Part3(Year5, R5);
%Part4(Year5, R5);
%Part5(Year5, R5);

%Part1(Year6);
%Part2(Year6, R6);
%Part3(Year6, R6);
%Part4(Year6, R6);
%Part5(Year6, R6);

%Part1(Year7);
%Part2(Year7, R7);
%Part3(Year7, R7);
%Part4(Year7, R7);
%Part5(Year7, R7);

%Part1(Year8);
%Part2(Year8, R8);
%Part3(Year8, R8);
%Part4(Year8, R8);
%Part5(Year8, R8);

%Part1(Year9);
%Part2(Year9, R9);
%Part3(Year9, R9);
%Part4(Year9, R9);
%Part5(Year9, R9);

%Part1(Year10);
%Part2(Year10, R10);
%Part3(Year10, R10);
%Part4(Year10, R10);
%Part5(Year10, R10);


**************************************
*****  Step 6: Demographic vars  *****
**************************************;
proc SQL;
    CREATE TABLE Demographic_Sex_CDM AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Sex AS Sex_RPDR
    FROM EHR.Cohort AS a, R_CDM.Demographic AS b
    WHERE a.PatID=b.Medicare_PatID
    ORDER BY a.PatID, a.D_Index;

    CREATE TABLE Demographic_Race_CDM AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Race AS Race_RPDR
    FROM EHR.Cohort AS a, R_CDM.Demographic AS b
    WHERE a.PatID=b.Medicare_PatID
    ORDER BY a.PatID, a.D_Index;

    CREATE TABLE Demographic_Hispanic_CDM AS
    SELECT DISTINCT a.PatID, a.D_Index, b.Hispanic AS Hispanic_RPDR
    FROM EHR.Cohort AS a, R_CDM.Demographic AS b
    WHERE a.PatID=b.Medicare_PatID
    ORDER BY a.PatID, a.D_Index;


%macro Dup(In);
data Clean_&In Dup_&In;
    set Demographic_&In;
    by PatID D_Index;

    if first.PatID and last.PatID then output Clean_&In;
    else output Dup_&In;
run;
%mend;
%Dup(Sex_CDM);
%Dup(Race_CDM);
%Dup(Hispanic_CDM);


**************************************************
*****  Step 7: Merge all covars with cohort  *****
**************************************************;
%macro Final(Time, Flag);
data EHR.Cohort;
    merge EHR.Cohort(in=a)
          &Time._Num_MDVisit2
          &Time._Num_MDVisit3
          &Time._RoutineCare
          &Time._MedicalExam
          &Time._Mammography
          &Time._PapTest
          &Time._PSATest
          &Time._Colonoscopy
          &Time._FecalOccultTest
          &Time._FluShot
          &Time._PneumococcalVaccine
          &Time._BMDTest
          &Time._A1C
          &Time._BMI
          &Time._Num_DX1
          &Time._Num_DX2
          &Time._Num_OPT1
          &Time._Num_OPT2
          &Time._Num_INP1
          &Time._Num_RegMed1
          &Time._Num_RegMed2
          &Time._Num_Basic1
          &Time._Num_Basic2
          &Time._EDVisit
          &Time._Only_1ED
          &Time._Only_ED;
    by PatID D_Index;

    array Missing[*] Repeat_Two_MD&Flag Repeat_Three_MD&Flag Routine_Care_Code&Flag Medical_Exam&Flag Mammography&Flag PapTest&Flag
                     PSATest&Flag Colonoscopy&Flag FecalOccultTest&Flag FluShot&Flag PneumococcalVaccine&Flag BMDTest&Flag
                     A1C&Flag BMI&Flag Reg1_DX&Flag Reg2_DX&Flag OPT1_Visit&Flag OPT2_Visit&Flag INP1_Visit&Flag
                     Reg1_Med&Flag Reg2_Med&Flag Reg1_Basic&Flag Reg2_Basic&Flag
                     Only_1ED_Visit&Flag Only_ED_Visit&Flag ED1_Visit&Flag;

    do i=1 to dim(Missing);
        if Missing[i]=. then Missing[i]=0;
    end;


    if Reg1_MedR&Flag=1 or Med1_VisitR&Flag=1 then drug1_R&Flag=1;
    else drug1_R&Flag=0;

    if Reg2_MedR&Flag=1 or Med2_VisitR&Flag=1 then drug2_R&Flag=1;
    else drug2_R&Flag=0;

    if OPT1_VisitR&Flag=1 or INP1_VisitR&Flag=1 then In_or_out_R&Flag=1;
    else In_or_out_R&Flag=0;

    N_Routine_Care&Flag=sum(of Medical_Exam&Flag Mammography&Flag PapTest&Flag PSATest&Flag Colonoscopy&Flag FecalOccultTest&Flag
                          FluShot&Flag PneumococcalVaccine&Flag BMDTest&Flag A1C&Flag BMI&Flag);

    if N_Routine_Care&Flag>=1 then Routine_Care_1Fact&Flag=1;
    else Routine_Care_1Fact&Flag=0;

    if N_Routine_Care&Flag>=2 then Routine_Care_2Fact&Flag=1;
    else Routine_Care_2Fact&Flag=0;

    if OPT1_Visit&Flag=1 and INP1_Visit&Flag=1 then INP1_OPT1_Visit&Flag=1;
    else INP1_OPT1_Visit&Flag=0;

    if OPT2_Visit&Flag=1 and INP1_Visit&Flag=1 then INP1_OPT2_Visit&Flag=1;
    else INP1_OPT2_Visit&Flag=0;


    drop i;

    if a;
run;
%mend Final;
%Final(Year0, R0);
%Final(Year1, R1);
%Final(Year2, R2);
%Final(Year3, R3);
%Final(Year4, R4);
%Final(Year5, R5);
%Final(Year6, R6);
%Final(Year7, R7);
%Final(Year8, R8);
%Final(Year9, R9);
%Final(Year10, R10);

data EHR.Cohort;
    merge EHR.Cohort(in=a)
          Clean_Sex_CDM
          Clean_Race_CDM
          Clean_Hispanic_CDM
    by PatID D_Index;

    if a;
run;


*******************************************************************
*********                                                **********
*********  Part III: Calculate Predicted EHR continuity  **********
*********                                                **********
*******************************************************************;


%macro datastep;
Data EHR.Cohort; set EHR.Cohort;

if age_cms>=72 then age_di=1; else age_di=0;

if sex_rpdr="M" then sex_rpdr_M=1; else sex_rpdr_M=0;

if race_rpdr="05" then race_rpdr_White=1; else race_rpdr_White=0
if race_rpdr="03" then race_rpdr_Black=1; else race_rpdr_Black=0;
if race_rpdr_White+race_rpdr_Black=0 then race_rpdr_Others=1; else race_rpdr_Others=0;

%do n = 1 %to 7;

if MammographyR&n.=1 or PapTestR&n.=1 then fem_screenR&n.=1; else fem_screenR&n.=0;

if ColonoscopyR&n.=1 or FecalOccultTestR&n.=1 then colon_ca_screenR&n.=1; else colon_ca_screenR&n.=0;

if FluShotR&n.=1 or PneumococcalVaccineR&n.=1 then vaccR&n.=1; else vaccR&n.=0;

	/*	Co_N_Hosp_R1: 0, 1, >=2*/
if Co_N_Hosp_R&n. >1 then Co_N_Hosp_group_R&n. = ">1";
	else if Co_N_Hosp_R&n. = 1  then Co_N_Hosp_group_R&n.= "1";
	else if Co_N_Hosp_R&n. = 0 then Co_N_Hosp_group_R&n.= "0";

	/*	Co_Total_HospLos_R&n.: 0, 1-3, >3*/
if Co_Total_HospLOS_R&n. >0 and Co_Total_HospLOS_R&n. <=3 then Co_Total_HospLOS_group_R&n. = "1-3";
	else if Co_Total_HospLOS_R&n. >3 then Co_Total_HospLOS_group_R&n. = ">3";
	else if Co_Total_HospLOS_R&n. = 0 then Co_Total_HospLOS_group_R&n. = "0";

	/*  Co_N_MDVisit_R&n.: 0, 1-2, 3-4, >=5 */
if Co_N_MDVisit_R&n. >=1 and Co_N_MDVisit_R&n. <3 then Co_N_MDVisit_group_R&n. = "1-2";
	else if Co_N_MDVisit_R&n. = 0 then Co_N_MDVisit_group_R&n. = "0";
	else if Co_N_MDVisit_R&n. >=3 and Co_N_MDVisit_R&n. <5 then Co_N_MDVisit_group_R&n. = "3-4";
	else if Co_N_MDVisit_R&n. >=5 then Co_N_MDVisit_group_R&n. = "5+";

	/* Co_N_Drugs_R&n.: 0, 1-2, 3-4, 5-9, >=10*/
if Co_N_Drugs_R&n. >= 10 then Co_N_Drugs_group_R&n. = "10+";
	else if Co_N_Drugs_R&n. >=1 and Co_N_Drugs_R&n.<=2 then Co_N_Drugs_group_R&n. = "1-2";
	else if Co_N_Drugs_R&n. >=3 and Co_N_Drugs_R&n.<=4 then Co_N_drugs_group_R&n. = "3-4";
	else if Co_N_Drugs_R&n. >=5 and Co_N_Drugs_R&n.<=9 then Co_N_drugs_group_R&n. = "5-9";
	else if Co_N_Drugs_R&n. =0 then Co_N_Drugs_group_R&n. = "0";
%end;
run;
%mend;
%datastep;


/*Predicted EHR continuity*/

%macro calculated_MPEC;

Data EHR.Cohort;
Set EHRCONT;
	%do n = 1 %to 7;
Cal_MPEC_R&n. = 0.01
		+0.03*Repeat_Two_MDR&n.
		+0.10*Medical_ExamR&n.
		+0.26*BMIR&n.
		+0.01*Reg1_DXR&n.
		-0.02*ED1_VisitR&n.
		+0.12*Co_N_Hosp_group2_R&n.
		+0.12*Co_N_Hosp_group1_R&n.
		+0.06*Co_N_MDVisit_group1_R&n.
		+0.13*Co_N_MDVisit_group2_R&n.
		+0.20*Co_N_MDVisit_group3_R&n.
		+0.06*Co_N_Drugs_group1_R&n.
		+0.22*Co_N_Drugs_group4_R&n.
		+0.17*Co_N_Drugs_group2_R&n.
		+0.22*Co_N_Drugs_group3_R&n.
		-0.02*age_di
		+0.02*Sex_RPDR_M
		+0.04*race_rpdr_Black
		+0.02*race_rpdr_Others
		+0.13*fem_screenR1
		+0.05*colon_ca_screenR1
		+0.07*vaccR1;

	%end;
run;

%mend;
%calculated_MPEC;