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

[bhaskarkothavt]

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

[ballardw · Accepted solution]

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)

[bhaskarkothavt]

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;

[ballardw]

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.

[bhaskarkothavt]

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