topic Merging two tables by choosing the nearest dates in SAS Data Management

Merging two tables by choosing the nearest dates

ileshem — Tue, 25 Apr 2017 18:40:03 GMT

Hello,

I am a bit new to scripting and I am trying to marge table "b" to table "a" using the nearest dates.

Table a:

NAME	DATE	SUMMERY
TERRY	4/23/2017 13:40	1
JHON	4/23/2017 13:40	3
ADI	4/24/2017 0:21	6
MIKE	4/23/2017 13:40	0
ADI	4/18/2017 0:21	1
TERRY	4/18/2017 13:40	1

Table b:

DATE	NAME	Counter
4/18/2017 7:36	ADI	135
4/18/2017 10:23	JHON	94
4/25/2017 0:00	TERRY	287
4/18/2017 7:39	TERRY	137
4/20/2017 15:24	MIKE	112

This is as far as I went, as you can see it is only merging the rows with the exact same date:

Clear Globals();

dt1 = Open( "\c:\1\a.jmp");
dt2 = Open( "\c:\1\b.jmp");

(dt1 << Join(
With( dt2 ),
By Matching Columns( : DATE = : DATE, :NAME = :NAME ),
Name( "Include non-matches" )(1, 0),
Preserve main table order( 1 ),
output table("Joined Table")
)
);

Can someone please guide me?

Thanks.

Re: Merging two tables by choosing the nearest dates

thomp7050 — Tue, 25 Apr 2017 18:51:59 GMT

I recommend using the magical comma in proc sql.

PROC SQL;
create table differences as 
select mydata.id, mydata.date, mydata.id as id2, mydata2.date as date2, mydata.date - mydata.date2 as difference
from mydata, mydata as mydata2;

Then simply find the record with the minimum difference for each mydata.id.

Re: Merging two tables by choosing the nearest dates

LinusH — Wed, 26 Apr 2017 08:22:17 GMT

Are you working in JMP?
Then you'll be better off posting your inquiry in a JMP forum.