topic Re: merge two tables with closest value of b.time that is less than a.time in SAS Programming

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

theflunkee — Wed, 08 Dec 2021 14:06:23 GMT

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

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

EyalGonen — Wed, 08 Dec 2021 14:24:41 GMT

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)

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

theflunkee — Wed, 08 Dec 2021 14:41:24 GMT

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;

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

EyalGonen — Wed, 08 Dec 2021 15:48:09 GMT

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

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

andreas_lds — Thu, 09 Dec 2021 07:47:30 GMT

Please post data in usable form and the expected result.