BookmarkSubscribeRSS Feed
arnaudd
Calcite | Level 5

Hi everyone,

I have a problem with data sets merging and date. I'd like to merge two dataset by date, merging with the closest date if needed. Here is an example:

Dataset 1:

Date             Y1

12/03/2003     1

15/03/2003     2

20/03/2003     3

Dataset 2:

Date             Y2

12/03/2003     10

16/03/2003     20

20/03/2003     30

I would like to get this dataset, by merging Dataset 1 and Dataset 2 (critical point is on the 15/03/2003 date value):

Date              Y1             Y2

12/03/2003      1               10

15/03/2003      2                20

20/03/2003      3                30

Is there a way to do it automatically via SAS enterprise guide (via the join table button in query builder box) or do I need to write my own script for that?

Can you help me about the script too?

Thanks everyone for your help!

Arnaud

1 REPLY 1
Reeza
Super User

You can use EG point and click for this, the trick is know what query you need.

So perhaps first sketch the SQL query and then build it after?

It will go something like the following (untested)

proc sql;

create table want as

select a.date, a.y1, b.y2

from table1 as a

join table2 as b

on b.date>a.date

group by a.date

having b.date=min(b.date);

quit;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1 reply
  • 2195 views
  • 1 like
  • 2 in conversation