topic Re: Urgent help needed ERROR: Insufficient space in file WORK.'SASTMP-000000010'.UTILITY in SAS Viya

Urgent help needed ERROR: Insufficient space in file WORK.'SASTMP-000000010'.UTILITY

bhaskarkothavt — Thu, 09 May 2024 14:31:05 GMT

Hello,

I am getting below error message when i am trying to connect Oracle database from SAS Studio using Viya4 (the code ran fine yesterday without any errors though)

Creating an aggregated table which will return 700 -8000 rows and SQL code is as below

Could some please help me how to overcome this "Insufficient space".

Best Regards,

Bhaskar

Re: Urgent help needed ERROR: Insufficient space in file WORK.'SASTMP-000000010'.UTILITY

ballardw — Thu, 09 May 2024 14:46:27 GMT

Something in the data may be using many more records than you expect in the final result.

When sorting in general SAS requires about 3 times as much space as the data would occupy as a data set because of use of temporary storage and manipulation.

Approaches that may work:

1) Pass-through SQL if all the source tables are in the Oracle database

2) Do the join into a separate table then request the distinct count from that resulting table.

3) reduce the size of alias table A by applying the Where clause in the From instead of after the joins occur (I'm not going to retype everything from a picture, post code as text if you want clearer examples of what the code might look like)

Re: Urgent help needed ERROR: Insufficient space in file WORK.'SASTMP-000000010'.UTILITY

bhaskarkothavt — Thu, 09 May 2024 14:49:38 GMT

My apologies for not pasting the sql code in the original request. Here is the code and could you please elaborate what you meant by 1 approach.

Code:
proc sql;
create table audit_agg as
select c.course,
count(distinct a.stuno),
b.gpaname,
c.yt
from uachieve.job_queue_run a
inner join uachieve.job_queue_subreq b
on b.jobq_seq_no = a.int_seq_no
left outer join uachieve.job_queue_course c
on c.jobq_seq_no = a.int_seq_no
and c.rtabx = b.rtabx
and c.stabx = b.stabx
where a.userid like '%INMAJRGPA%'
and substr(b.gpaname, 1, 1) = 'P' /*Pathways courses*/
and c.yt = "20&yr.&tm."
group by c.course, b.gpaname, yt;
quit;

Re: Urgent help needed ERROR: Insufficient space in file WORK.'SASTMP-000000010'.UTILITY

ballardw — Thu, 09 May 2024 15:43:17 GMT

You don't show how you connect to Oracle.

Basically it pass-through means writing code that the external data base recognizes and execute it there.

An example from the online help

proc sql;
connect to oracle as mycon (user=myusr1
   password=mypwd1 path='mysrv1');

select *
   from connection to mycon
   /* everything in the next set of () are executed in the Oracle data base*/
    (select empid, lastname, firstname,
            hiredate, salary
     from employees where 
          hiredate>='31-DEC-88');

disconnect from mycon;
quit;

IF all of the sets in your query are in the data base then you would use the schema table names as Oracle has them. So all the joining and selection would happen in the Oracle database instead of in the SAS work space.

Re: Urgent help needed ERROR: Insufficient space in file WORK.'SASTMP-000000010'.UTILITY

bhaskarkothavt — Thu, 09 May 2024 15:58:49 GMT

I followed the approach 2 and it worked without any issues thanks a lot.