DATA Step, Macro, Functions and more

how do I merge 2 datasets on 2 different timestamps

Reply
New Contributor
Posts: 2

how do I merge 2 datasets on 2 different timestamps

[ Edited ]

I have two massive datasets that I need to merge.  All of the fields are the same with the exception of the time field.  I want to merge the two datasets where T1.time is within 30 minutes of T2.time.  I have tried everything I can think of but that's probably not saying too much ;-).  Here's a sample of data below...

 

DATA T1;
INPUT STORE KEY2 DATE TIME VAR3
DATALINES;
3 163003 03SEP2016 18:47 1;
RUN;

 

DATA T2;
INPUT STORE KEY2 DATE TIME VAR1 VAR2;
DATALINES;
3 163003 03SEP2016 18:33 1 1;
RUN;

 

DATA T3;

MERGE T2 (IN=A) T1;

BY STORE KEY2 DATE;

IF A;

IF.........;

RUN;

 

The final table should be a left join to T2 to pull in var3 from T1 if T1.time is within 30 minutes of T2.time. 

I sincererly appreciate any help anyone could provide. 

 

Thanks.

Trusted Advisor
Posts: 1,128

Re: how do I merge 2 datasets on 2 different timestamps

[ Edited ]

Please try proc sql

 

proc sql;
create table want as select a.*,(a.time-b.time)/60 as t,b.var1,b.var2 from t1 as a left join t2 as b on 
a.store=b.store and a.key2=b.key2 and a.date=b.date having t<30;
quit;
Thanks,
Jag
PROC Star
Posts: 252

Re: how do I merge 2 datasets on 2 different timestamps

[ Edited ]

something like this should work

 

proc sql;
select a.*, var2
from t1 a
left join
t2 b
on a.store =b.store
and a.key2 =b.key2
and b.time between  a.time - '00:30't and a.time;
quit;
New Contributor
Posts: 2

Re: how do I merge 2 datasets on 2 different timestamps

I tried both solutions and they both worked!  Thank you so much!

Ask a Question
Discussion stats
  • 3 replies
  • 99 views
  • 2 likes
  • 3 in conversation