Data Manipulation Coding Project 1 (solution) - Data Reconciliation

[Note: you are recommended to copy and paste the code below onto your SAS Studio for better viewing]

Model Answer

** Formats **;
proc format;
    value lev 0 = "Level 0 – opening inspection"
               1 = "Level I – call back, regular practice review, acquisition or relocation"
              2 = "Level II – re-inspection ordered by inspector"
              3 = "Level III (and above) – inspections ordered by the Accreditation Committee";

    value con    0 = "NA"
                1 = "Satisfactory - Inspection Concluded"
                2 = "Issues Identified"
                3 = "Re-inspection ordered"
                4 = "Referral to Accreditation Committee"
                5 = "Report to Accreditation Committee";
run;

** Standardize data set **;
** (1) North York **;
proc sort data=NY;
    by form compliance year pharno city;
run;

proc transpose data=NY out=t_NYDate;
    by form compliance year pharno city;
    var Date_Lev0-Date_Lev3;
run;

data t_NYDate2;
    set t_NYDate;
    format lev lev. visitdate mmddyy10.;
    lev = input(substr(_NAME_, 9, 1), best.);
    visitdate = col1;

    if col1^=.;

    drop _NAME_ col1;
run;

proc transpose data=NY out=t_NYCon;
    by form compliance year pharno city;
    var Con_Lev0-Con_Lev3;
run;

data t_NYCon2;
    set t_NYCon;
    format lev lev. con con.;
    lev = input(substr(_NAME_, 8, 1), best.);
    if col1 = "NA" then con = 0;
    else if col1 = "Satisfactory - Inspection Concluded" then con = 1;
    else if col1 = "Issues Identified" then con = 2;
    else if col1 = "Re-inspection ordered" then con = 3;
    else if col1 = "Referral to Accreditation Committee" then con = 4;
    else if col1 = "Report to Accreditation Committee" then con = 5;

    if col1^="";

    drop _NAME_ col1;
run;

Data NorthYork;
    merge t_NYDate2 (in=a) t_NYCon2 (in=b);
    by form compliance year pharno city lev;
run;

** (2) Toronto **;
data Toronto;
    set TO;
    format lev lev. con con.;
    if level = "Level 0 – opening inspection" then lev = 0;
    else if level = "Level I – call back, regular practice review, acquisition or relocation" then lev = 1;
    else if level = "Level II – re-inspection ordered by inspector" then lev = 2;
    else if level = "Level III (and above) – inspections ordered by the Accreditation Committee" then lev = 3;

    if concl = "NA" then con = 0;
    else if concl = "Satisfactory - Inspection Concluded" then con = 1;
    else if concl = "Issues Identified" then con = 2;
    else if concl = "Re-inspection ordered" then con = 3;
    else if concl = "Referral to Accreditation Committee" then con = 4;
    else if concl = "Report to Accreditation Committee" then con = 5;

    drop level concl;
run;

** (3) Richmond Hill **;
data RichmondHill;
    set RH;
    format lev lev. con con.;
    if level = "Level 0 – opening inspection" then lev = 0;
    else if level = "Level I – call back, regular practice review, acquisition or relocation" then lev = 1;
    else if level = "Level II – re-inspection ordered by inspector" then lev = 2;
    else if level = "Level III (and above) – inspections ordered by the Accreditation Committee" then lev = 3;

    if concl = "NA" then con = 0;
    else if concl = "Satisfactory - Inspection Concluded" then con = 1;
    else if concl = "Issues Identified" then con = 2;
    else if concl = "Re-inspection ordered" then con = 3;
    else if concl = "Referral to Accreditation Committee" then con = 4;
    else if concl = "Report to Accreditation Committee" then con = 5;

    drop level concl;
run;

** (4) Scarborough **;
proc sort data=SC;
    by form compliance year pharno city;
run;

proc transpose data=SC out=t_SCDate;
    by form compliance year pharno city;
    var Date_Lev0-Date_Lev3;
run;

data t_SCDate2;
    set t_SCDate;
    format lev lev. visitdate mmddyy10.;
    lev = input(substr(_NAME_, 9, 1), best.);
    visitdate = col1;

    if col1^=.;

    drop _NAME_ col1;
run;

proc transpose data=SC out=t_SCCon;
    by form compliance year pharno city;
    var Con_Lev0-Con_Lev3;
run;

data t_SCCon2;
    set t_SCCon;
    format lev lev. con con.;
    lev = input(substr(_NAME_, 8, 1), best.);
    if col1 = "NA" then con = 0;
    else if col1 = "Satisfactory - Inspection Concluded" then con = 1;
    else if col1 = "Issues Identified" then con = 2;
    else if col1 = "Re-inspection ordered" then con = 3;
    else if col1 = "Referral to Accreditation Committee" then con = 4;
    else if col1 = "Report to Accreditation Committee" then con = 5;

    if col1^="";

    drop _NAME_ col1;
run;

Data Scarborough;
    merge t_SCDate2 (in=a) t_SCCon2 (in=b);
    by form compliance year pharno city lev;
run;

data Final;
    set NorthYork Toronto RichmondHill Scarborough;
run;