Selecting Max Value from Left Join

Question

I have two tables like below.

Profile : ID
Charac : ID, NAME, DATE

With the above tables, I am trying to get NAME from Charac where we have max date.

I am trying to do a join with proc sql by replicating the answer for mysql like below

proc sql;
create table ggg as
select profile.ID ,T2.NAME
from Profile
left join 
(   select ID,max(DATE) as max_DATE
    from EDW.CHARAC
    group by ID
) as T1
on fff.ID = EDW.ID
left join EDW.CHARAC as T2
on T2.ID = T1.max_DATE
order by profile.ID DESC;
quit;

Error

ERROR: Unresolved reference to table/correlation name EDW.

ERROR: Expression using equals (=) has components that are of different data types.

EDW.CHARAC is this supposed to be EDW.CHARA? Also, is EDW.ID supposed to reference Chara's id? — Compass
@Compass Sorry for first typo. Yes both tables i.e. Profile and CHARAC have "ID" common. — LonelySoul

vasja vasja · Accepted Answer · 2014-10-17T15:47:15

Could it be you intended

on T2.ID = T1.max_DATE

which is probably source of "components that are of different data types" error

to be:

on T2.ID = T1.ID and T2.DATE = T1.max_DATE

that, is - joining on IDs at maximum DATE?

Selecting Max Value from Left Join

2 Answers