06-05-2017 11:18 AM - edited 06-05-2017 11:27 AM
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...
INPUT STORE KEY2 DATE TIME VAR3
3 163003 03SEP2016 18:47 1;
INPUT STORE KEY2 DATE TIME VAR1 VAR2;
3 163003 03SEP2016 18:33 1 1;
MERGE T2 (IN=A) T1;
BY STORE KEY2 DATE;
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.
06-05-2017 11:34 AM - edited 06-05-2017 11:36 AM
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;
06-05-2017 11:43 AM - edited 06-05-2017 11:53 AM
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;