topic Re: proc sql join one to last of group in SAS Procedures

proc sql join one to last of group

foxrt — Fri, 22 May 2009 06:39:34 GMT

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;

Re: proc sql join one to last of group

Florent — Fri, 22 May 2009 09:23:22 GMT

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;

Re: proc sql join one to last of group

foxrt — Fri, 22 May 2009 09:42:52 GMT

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