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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.