need help to modify query for better processing

Banu — Sat, 16 Apr 2022 11:08:54 GMT

Hi Team,

I have below query which takes 30minutes to complete.

Could you please suggest any bottleneck here.

proc sql;

create table X as ( select * from Y a inner join Z b on input(b.emp_id,11.) = a.emp_id

where a.sale_dt >= b.sale_dt and a.sale_dt < b.sale_last_dt );

quit;

Source Table A has: 3.6M data & B table has 3.3M data.

Output of this query gives 3.5M data and its taking 30 min to complete. Is there any way to reduce the process time.

Please suggest?

Re: need help to modify query for better processing

Ksharp — Sat, 16 Apr 2022 11:36:57 GMT

Here is :

proc sql;

     create table X as ( select * from Y a inner join Z b on input(b.emp_id,11.) = a.emp_id

     where a.sale_dt >= b.sale_dt and a.sale_dt < b.sale_last_dt );

quit;

Make a data step before SQL to convert this variable by INPUT().

data Y1;
 set Y;
new_emp_id=input(emp_id,11.);
run;

proc sql;
..................

topic need help to modify query for better processing in SAS Programming

need help to modify query for better processing

Re: need help to modify query for better processing