SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Merging two tables by choosing the nearest dates

Reply
New Contributor
Posts: 3

Merging two tables by choosing the nearest dates

[ Edited ]

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.

Frequent Contributor
Posts: 93

Re: Merging two tables by choosing the nearest dates

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.

Super User
Posts: 5,429

Re: Merging two tables by choosing the nearest dates

Are you working in JMP?
Then you'll be better off posting your inquiry in a JMP forum.
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 129 views
  • 0 likes
  • 3 in conversation