Datepart taking longer time

[yashpande]

Hi All,

I have oracle table which has date time variable and I need to create sas table with date9 format. Oracle table has more than 30million records. It takes lot of time to extract data due to conversion. Any help in optimization would be really great help
Below is the sample code


Proc sql;
Create table t1 as
Select a.* , b.reporting_date format=date9.
from have left join oracle_lib b
On a. Id=b.id where datepart(b.Reporting_date) =a. Eff_from_date;
Quit;

In another code reporting _date has value like 06Jun2019:00:00:00
Eff_from_date has values like 06-Jun-2019

Any help in reducing the time is of really good help.

[Reeza]

Where is the HAVE table? If it's not also on the oracle server it's taking a lot of time because linking data between the server and locally first means getting all the data in the same place. If you need to speed it up, I'd run the filter first based on the ID's to reduce the size and then do the date filter in a second step.

[yashpande]

Have table is sas table.

[Reeza · Accepted solution]

Then you need to redesign your process, did you try my suggested approach, where you first filter based on ID's from a macro variable instead and then filter on dates?

proc sql noprint;
select id into :id_list separated by " "
from have;
quit;

Proc sql;
Create table t1 as
Select id, reporting_date
from oracle_lib a
where a.id in (&id_list.);
Quit;


You also never have an aliased table named A in your code.