I have two tables (a and b) where both tables have column "time" and the tables has different number of rows (b has more rows than a)
However, a.time and b.time are not the same and I hope to merge the two tables to a, using time as constraint.
I want data from b only be merge to a, where b.time is the closest time to a.time, but less than a.time.
Thinking how to use proc sql for the data.... my previous attempts reported an error for insufficient memory... Many Thanks
An approach with SQL could be to join tables by a.time >= b.time (or maybe by a.time between b.time and b.time - 1 week for example to limit the results) that would produce a 1:n join then you could group by a.time and select the rows HAVING b.time = max(b.time)
I got the following error....
ERROR: Sort execution failure.
proc sql; create table a_vs_b
as select a.S1, b.S2
from tablea as a left join tables as b
on a.time >= b.time
group by a.time
having b.time=max(b.time);
quit;
Please post data in usable form and the expected result.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.