merging by closest date - join table

arnaudd — Tue, 22 Apr 2014 16:08:42 GMT

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

Re: merging by closest date - join table

Reeza — Tue, 22 Apr 2014 16:30:37 GMT

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;

topic merging by closest date - join table in SAS Enterprise Guide

merging by closest date - join table

Re: merging by closest date - join table