topic Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL. in SAS Programming

Sort Execution Failure when Writing to and from Oracle with Proc SQL.

DavidPhillips2 — Fri, 15 Apr 2022 15:50:26 GMT

I ran into a sort of execution failure when working with a medium dataset of 16 million rows. When joining with a small table, time_lookup to obtain one field. I assumed this was due to the temp library size limitations so I changed the libname to write to Oracle and I wrote the base of the table to Oracle first so everything is in Oracle but the sort execution failure remains.

proc sql;

create table wip.Persistence_merged2 as
select pm.*, tl.ACADEMIC_PERIOD as TO_ACADEMIC_PERIOD
from wip.Persistence_merged1 pm
left join odsprod.time_lookup tl on pm.TO_ACADEMIC_PERIOD_DESC = tl.ACADEMIC_PERIOD_DESC;

quit;

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

Kurt_Bremser — Fri, 15 Apr 2022 16:25:42 GMT

Try a hash:

data wip.Persistence_merged2;
set wip.Persistence_merged1;
if _n = 1
then do;
  length to_academic_period ...; /* define here */
  declare hash t1 (dataset:"odsprod.time_lookup (rename=(academic_period_desc=to_academic_period_desc academic_period=to_academic_period))");
  t1.definekey("to_academic_period_desc");
  t1.definedata("to_academic_period");
  t1.definedone();
end;
_n_ = t1.find();
run;

As long as you don't push the whole operation to the DB by using explicit pass-through, SQL will create an enormous utility file in WORK (or UTILLOC, if that is defined).

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

Sajid01 — Fri, 15 Apr 2022 16:26:23 GMT

If it is a temp library size issue, there will be a message in the log.

With the information you have shared it is difficult to give a proper response.

What does the log say?

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

SASKiwi — Sat, 16 Apr 2022 23:32:07 GMT

Please post your SAS log, including your WIP and ODSPROD LIBNAME statements so we can see where processing is happening.

If all of your processing is happening in Oracle then you won't be getting a sort execution failure in SAS.

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

DavidPhillips2 — Mon, 18 Apr 2022 12:51:04 GMT

Knowing it was not running the process on Oracle allowed me to make progress. If you have two Oracle libnames to two different schemas they must both use the same user name in the user name/password section of the libname otherwise the processing is done by SAS. I copied one of my tables over to the other schema so the script can stays in Oracle. I'm debugging more before posting more about the sorting error.

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

Tom — Mon, 18 Apr 2022 13:27:21 GMT

There should be a way to use ONE libref to point to database. You can use a database option to change the schema used by implicit passthru using the SCHEMA= dataset option.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n1h1m1ethqxwryn1wu68m46hdp1u.htm

libname myora oracle ... schema=X .... ;
data want;
 merge myora.table1 myora.table2 (schema=Y);
  by id;
run;

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

DavidPhillips2 — Tue, 19 Apr 2022 12:57:10 GMT

SAS Tech Support said to run the lines from this post before the code that errors.

https://support.sas.com/kb/39/705.html

proc options group=memory; run;
proc options option=(work utilloc) value; run;
proc options option=threads; run;

options fullstimer msglevel=i ;

Then determined that the sort errors are likely due to the work library size for my user settings. I'll ask our SAS admin to change my work library settings. Writing intermediary output to the same Oracle schema and then running the next step worked but it takes a long time to write extra steps involving large tables from SAS to Oracle.

Re: Sort Execution Failure when Writing to and from Oracle with Proc SQL.

mkeintz — Tue, 19 Apr 2022 14:49:05 GMT

Thanks @DavidPhillips2, for reporting the response you got from SAS.