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;
SAS Tech Support said to run the lines from this post before the code that errors.
https://support.sas.com/kb/39/705.html
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).
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?
SAS Tech Support said to run the lines from this post before the code that errors.
https://support.sas.com/kb/39/705.html
Thanks @DavidPhillips2, for reporting the response you got from SAS.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: