join 2 tables on different date formats using proc sql

Question

i am trying to join two tables in sas using proc sql. however the dates are different (table 1 has Datetime as format and table 2 has date9). I wanted to join the tables with id (common in both tables) and date. DATEPART did not seem to work. Any ideas? This is the code that I am trying to run but does not work:

proc sql;
create table p.data1 as
select 
    a.*,
    b.var1 as var1_alt,
    Datepart(b.MonthEndDate) format date9. as EOMDate

from 
    p.base_1 a

    left join q.a_GLV b
    on a.ID = b.ID
    and a.MonthEndDate = b.MonthEndDate
order by
    a.ID,
    a.MonthEndDate
;
quit;

Answer 1

You need to use datepart in the join, since datetime and date are different numbers in SAS (# of seconds vs # of days).

proc sql;
create table p.data1 as
select 
    a.*,
    b.var1 as var1_alt,
    Datepart(b.MonthEndDate) format date9. as EOMDate

from 
    p.base_1 a

    left join q.a_GLV b
    on a.ID = b.ID
    and a.MonthEndDate = datepart(b.MonthEndDate)
order by
    a.ID,
    a.MonthEndDate
;
quit;