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.
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;
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;
I tried both solutions and they both worked! Thank you so much!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.