topic Re: optimizing proc sql join in SAS Programming

optimizing proc sql join

Nasser_DRMCP — Wed, 13 Jan 2021 16:17:20 GMT

Hello,

I have a table t1 that holds 3 millions rows (with one datetime) and a table t2 that holds 5 millions rows (with 2 datetime)

In a proc sql, i have a join like this

select ...

FROM t1 AS a LEFT JOIN t2 AS b

ON a.IDT_RSS=b.IDT_RSS

AND a.TSP_CVL BETWEEN b.TSP_TEST_DEB AND b.TSP_TEST_FIN

;

cpu times takes 23 minutes. How coul I optimize ?

thanks a lot in advance

nasser

Kurt_Bremser — Wed, 13 Jan 2021 16:28:34 GMT

Tell us a little more about your data.

are the IDT_RSS unique in t1, or do you have multiple entries per IDT_RSS?
same for t2?
if IDT_RSS is non-unique in t2, do the time ranges overlap, or is each TSP_TEST_DEB larger than the preceding TSP_TEST_FIN (if the dataset is sorted by IDT_RSS and TSP_TEST_DEB)?

Kurt_Bremser — Wed, 13 Jan 2021 16:30:38 GMT

And some additional info: what variables are contained in the datasets, what are their types and defined lengths?

Nasser_DRMCP — Tue, 19 Jan 2021 09:59:26 GMT

Hello Kurt

thanks a lot for your respons and sorry for answering in late.

we succeeded to solve the problem by reducing the scope.

instead of collect and treat all rows each times, we treat only one month each month.

I mean we treat only missing data in target (versus source), instead of remove and replace.

Thanks a lot

regards

Nasser