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, Oct 17 '14 at 14:51
@Compass Sorry for first typo. Yes both tables i.e. Profile and CHARAC have "ID" common. — LonelySoul, Oct 17 '14 at 14:55

score 1 · Answer 1 · answered Oct 17 '14 at 15:10

1

You can't use EDW like that. You need to join

on fff.ID=T1.ID

As far as data types, that probably is because EDW.ID is undefined and thus numeric by default.

answered Oct 17 '14 at 15:10

Joe

62,789
6
49
67

score 1 · Accepted Answer · answered Oct 17 '14 at 15:47

1

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?

answered Oct 17 '14 at 15:47

vasja

4,732
13
15

Selecting Max Value from Left Join

2 Answers2