HCA TEST

dm log 'clear';
data work.visit;
    length
        visit_key        $ 9
        pat_key          $ 8
        dept_key         $ 5
        contact_dt_key     8
        hsp_acct_key     $ 2
        dict_enc_type_key $ 6
        age                8
        appt_dt            8
        appt_checkin_dt    8
        appt_checkout_dt   8
        hosp_admit_dt      8
        hosp_dischrg_dt    8
        appt_made_dt       8
        appt_cancel_dt     8
        create_dt          8
        create_by        $ 7
        upd_dt             8
        upd_by           $ 7
        dob                8 ;
    format
        visit_key        $char9.
        pat_key          $char8.
        dept_key         $char5.
        contact_dt_key   yymmdd10.
        hsp_acct_key     $char2.
        dict_enc_type_key $char6.
        age              best7.
        appt_dt          datetime21.2
        appt_checkin_dt  datetime21.2
        appt_checkout_dt datetime21.2
        hosp_admit_dt    datetime21.2
        hosp_dischrg_dt  datetime21.2
        appt_made_dt     datetime21.2
        appt_cancel_dt   datetime21.2
        create_dt        datetime18.
        create_by        $char7.
        upd_dt           datetime18.
        upd_by           $char7.
        dob              datetime18. ;
    informat
        visit_key        $char9.
        pat_key          $char8.
        dept_key         $char5.
        contact_dt_key   yymmdd10.
        hsp_acct_key     $char2.
        dict_enc_type_key $char6.
        age              best7.
        appt_dt          datetime21.
        appt_checkin_dt  datetime21.
        appt_checkout_dt datetime21.
        hosp_admit_dt    datetime21.
        hosp_dischrg_dt  datetime21.
        appt_made_dt     datetime21.
        appt_cancel_dt   datetime21.
        create_dt        datetime18.
        create_by        $char7.
        upd_dt           datetime18.
        upd_by           $char7.
        dob              datetime18. ;
    infile 'c:\users\dogoee\downloads\new folder\hca\visit.csv'
        lrecl=32767
        firstobs=2
        encoding="wlatin1"
        dlm='2c'x
        missover
        truncover
        dsd ;
    input
        visit_key        : $char9.
        pat_key          : $char8.
        dept_key         : $char5.
        contact_dt_key   : ?? yymmdd8.
        hsp_acct_key     : $char2.
        dict_enc_type_key : $char6.
        age              : ?? comma7.
        appt_dt          : ?? anydtdtm16.
        appt_checkin_dt  : ?? anydtdtm16.
        appt_checkout_dt : ?? anydtdtm16.
        hosp_admit_dt    : ?? anydtdtm16.
        hosp_dischrg_dt  : ?? anydtdtm16.
        appt_made_dt     : ?? anydtdtm15.
        appt_cancel_dt   : ?? anydtdtm15.
        create_dt        : ?? anydtdtm16.
        create_by        : $char7.
        upd_dt           : ?? anydtdtm16.
        upd_by           : $char7.
        dob              : ?? anydtdtm15. ;
    array summa[*] _character_;
      do i = 1 to dim(summa);
      if summa[i] = 'na' then summa[i] =. ;
   end;
   drop i;
run;
data work.visit_diagnosis;
    length
        visit_key        $ 8
        dx_key           $ 7
        dict_dx_sts_key  $ 3 ;
    keep
        visit_key
        dx_key
        dict_dx_sts_key ;
    format
        visit_key        $char8.
        dx_key           $char7.
        dict_dx_sts_key  $char3. ;
    informat
        visit_key        $char8.
        dx_key           $char7.
        dict_dx_sts_key  $char3. ;
    infile 'c:\users\dogoee\downloads\new folder\hca\visit_diagnosis.csv'
        lrecl=32767
        firstobs=2
        encoding="wlatin1"
        dlm='2c'x
        missover
        dsd ;
    input
        visit_key        : $char8.
        pat_key          : $1.
        hsp_acct_key     : $1.
        dx_key           : $char7.
        dict_dx_type_key : $1.
        dict_dx_sts_key  : $char3.
        dict_dx_poa_key  : $1.
        seq_num          : $1.
        dx_cmt           : $1.
        create_dt        : $1.
        create_by        : $1.
        upd_dt           : $1.
        upd_by           : $1. ;

run;
data work.diagnosis;
    length
        dx_nm            $ 160
        dx_key           $ 7 ;
    keep
        dx_nm
        dx_key ;
    format
        dx_nm            $char160.
        dx_key           $char7. ;
    informat
        dx_nm            $char160.
        dx_key           $char7. ;
    infile 'c:\users\dogoee\downloads\new folder\hca\diagnosis.csv'
        lrecl=32767
        firstobs=2
        encoding="wlatin1"
        dlm='2c'x
        missover
        dsd ;
    input
        icd9_cd          : $1.
        dx_nm            : $char160.
        dx_key           : $char7.
        dx_stat          : $1.
        dx_id            : $1. ;
run;
data work.medication_order;
    length
        pat_key          $ 8
        visit_key        $ 8
        med_ord_nm       $ 163 ;
    keep
        pat_key
        visit_key
        med_ord_nm ;
    format
        pat_key          $char8.
        visit_key        $char8.
        med_ord_nm       $char163. ;
    informat
        pat_key          $char8.
        visit_key        $char8.
        med_ord_nm       $char163. ;
    infile 'c:\users\dogoee\downloads\new folder\hca\medication_order.csv'
        lrecl=32767
        firstobs=2
        encoding="wlatin1"
        dlm='2c'x
        missover
        dsd ;
    input
        pat_key          : $char8.
        med_key          : $1.
        med_ord_key      : $1.
        dict_ord_stat_key : $1.
        med_ord_create_dt : $1.
        med_ord_desc     : $1.
        visit_key        : $char8.
        pat_loc_dept_key : $1.
        discont_emp_key  : $1.
        med_ord_id       : $1.
        med_ord_nm       : $char163. ;
run;
data work.department;
    length
        dept_key         $ 5
        dept_nm          $ 39
        specialty        $ 34
        dept_abbr        $ 15
        dept_grp         $ 2
        dept_cntr        $ 46
        rev_loc_key      $ 4 ;
    format
        dept_key         $char5.
        dept_nm          $char39.
        specialty        $char34.
        dept_abbr        $char15.
        dept_grp         $char2.
        dept_cntr        $char46.
        rev_loc_key      $char4. ;
    informat
        dept_key         $char5.
        dept_nm          $char39.
        specialty        $char34.
        dept_abbr        $char15.
        dept_grp         $char2.
        dept_cntr        $char46.
        rev_loc_key      $char4. ;
    infile 'c:\users\dogoee\downloads\new folder\hca\department.csv'
        lrecl=32767
        firstobs=2
        encoding="wlatin1"
        dlm='2c'x
        missover
        dsd ;
    input
        dept_key         : $char5.
        dept_nm          : $char39.
        specialty        : $char34.
        dept_abbr        : $char15.
        dept_grp         : $char2.
        dept_cntr        : $char46.
        rev_loc_key      : $char4. ;
run;
proc sql;
    create table sumv as
    select distinct a1.pat_key,a1.visit_key,a1.hosp_admit_dt,a1.age,a1.hosp_dischrg_dt,a2.appt_dt,
        case when upcase(d.dx_nm) contains 'anaphylaxis'
                then '1'
                else '0'
            end
        as anaph_dx_ind,
        case when upcase(c.med_ord_nm) contains 'epinephrine'
                then '1'
                else '0'
            end
        as epi_order_ind,
        case when 0 lt intck('hour', (a1.hosp_dischrg_dt), (a2.appt_dt)) lt 192
                then '1'
                else '0'
            end
        as follow_up_ind ,
        case when 0 lt intck('hour', (a1.hosp_dischrg_dt), (a2.appt_dt)) lt 192
                then a2.appt_dt
                else .
            end
        as follow_up_date format=datetime21.2,
        case when 0 lt intck('hour', (a1.hosp_dischrg_dt), (a2.appt_dt)) lt 192
                then ((intck('hour', (a1.hosp_dischrg_dt), (a2.appt_dt)))/24)
                else .
            end
        as days_to_follow_up format=best1.0
    from work.visit as a1 left join work.visit as a2
            on a1.pat_key=a2.pat_key
        left join work.visit_diagnosis as b
            on a1.visit_key=b.visit_key
        left join work.medication_order as c
            on b.visit_key=c.visit_key
        left join work.diagnosis as d
            on b.dx_key=d.dx_key
        left join work.department as e
            on a1.dept_key=e.dept_key
    where datepart(a1.hosp_admit_dt) ge "01aug2014"d
        and 1 le a1.age le 18
        and b.dict_dx_sts_key in ('313' '314')
        and a1.dict_enc_type_key='83'
        and a2.dict_enc_type_key='108'
    order by a1.pat_key;
quit;
proc sort data=sumv nodupkey out=porj;
    by visit_key;
run;
proc export data=porj
    outfile="h:\hom1.xlsx"
    dbms=excel replace;
run;