BookmarkSubscribeRSS Feed
foxrt
SAS Employee
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;
2 REPLIES 2
Florent
Quartz | Level 8
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
SAS Employee
Thanks for you quick reply! It does exactly what I need. It's easy when you know it...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 617 views
  • 0 likes
  • 2 in conversation