CODEWITHSUNDEEP
sqljoinsas
user2641784
user2641784

Reputation: 387

join 2 tables on different date formats using proc sql

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;

Upvotes: 0

Views: 3247

Answers (1)

Joe
Joe

Reputation: 63424

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;

Upvotes: 1

Related Questions