topic Re: Datepart taking longer time in SAS Programming

Datepart taking longer time

yashpande — Thu, 06 Jun 2019 18:43:20 GMT

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.

Re: Datepart taking longer time

Reeza — Thu, 06 Jun 2019 19:24:00 GMT

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.

Re: Datepart taking longer time

yashpande — Fri, 07 Jun 2019 03:04:53 GMT

Have table is sas table.

Re: Datepart taking longer time

Reeza — Fri, 07 Jun 2019 15:03:42 GMT

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.