merge two tables with closest value of b.time that is less than a.time

[theflunkee]

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

[EyalGonen]

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)

[theflunkee]

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;

[EyalGonen]

See https://communities.sas.com/t5/SAS-Procedures/ERROR-Sort-execution-failure-in-PROC-SQL/td-p/262581

[andreas_lds]

Please post data in usable form and the expected result.