proc sql join one to last of group

[foxrt]

Hi!
I have problem doing the join between two tables in EG or sql code. First data set has observations with unique id and second data set has n obs with that id and also a var (for example date) and I need to join obs from first data set to the obs from second data set with the latest date. Also many other vars in both data sets. Data step merge is easy but is it possible to do it in one sql join (preferrably using EG query builder)? See data step solution below.

data a;
input id num1;
datalines;
1 4
2 5
;
data b;
input id num2 name $;
datalines;
1 2 Br
1 3 Qc
1 7 Fe
2 2 Dr
2 4 Mi
;

proc sort data=a;
by id;
run;

proc sort data=b;
by id num2;
run;

data c;
merge a b;
by id;
if last.id;
run;
proc print;run;

[Florent]

Hi,

Could you please try to adapt the following code to your needs and let me know if this is what you were expecting as SQL query ?

Regards,
Florent


proc sql;
create table d as
select ds1.ID, num1, num2, name
from a ds1,
b ds2
where ds1.ID = ds2.ID
and ds2.num2 = (select max(num2)
from b
where ds1.ID = ID);
quit;

[foxrt]

Thanks for you quick reply! It does exactly what I need. It's easy when you know it...