BookmarkSubscribeRSS Feed
stc200896
Calcite | Level 5

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.

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16

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
kiranv_
Rhodochrosite | Level 12

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;
stc200896
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1157 views
  • 2 likes
  • 3 in conversation