Help using Base SAS procedures

ERROR: Insufficient space in file WORK

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

ERROR: Insufficient space in file WORK

I'm running a very simple inner join query in SAS , and it gives me the "insufficient space in work.SASTMP" error. My work directory is empty, and I'm not even using it. All my SAS data sets are in my C drive which has more than 200G  space.

Can anyone help me with that? Again my libraries are in my C drive.

Thanks


Accepted Solutions
Solution
‎01-24-2018 12:28 PM
Super User
Posts: 10,217

Re: ERROR: Insufficient space in file WORK

No miracle you run out of space:

proc sql outobs=10;
create table CPI.combined as
select distinct
 a.ACCTNUM,
a.CUST_ID,
b.SourceACCT,
b.CUST_ID
from CPI.CUST_profile_info a,
CPI.CUST_DEC_PRO b
where a.CUST_ID ^= b.CUST_ID;
quit;

This means that every observation in cust_profile is joined with ALL observations of cust_dec_pro that have a different cust_id.

If the datasets have distinct cust_id's, you end up with 900000 * 599999 observations (539,999,100,000)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,217

Re: ERROR: Insufficient space in file WORK

[ Edited ]

Even a slight mistake can cause SQL to run out of space, so we need to see the code. It's also important to know the data, so a quick overview (number of observations & variables, observation size) of the datasets will be helpful. That you do not use WORK does not mean much, as proc sql builds its utility file(s) there, and that seems to be the problem. Is WORK also located on the C: drive?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

Re: ERROR: Insufficient space in file WORK

[ Edited ]
Posted in reply to KurtBremser

I'm trying to submit the following query. I have also tried other queries and still get the same error. yes, WORK is located on my C: drive, but I have plenty of space there (more than 200 GB). one of my tables have 900000 observations and is 1.2 GB.The other one has 600000 observations and  412MG. the tables include personal information bout customers.

proc sql outobs=10;

create table CPI.combined as

select distinct

 a.ACCTNUM,

a.CUST_ID,

b.SourceACCT,

b.CUST_ID

from CPI.CUST_profile_info a,

CPI.CUST_DEC_PRO b

where a.CUST_ID ^= b.CUST_ID;

quit;

 

Solution
‎01-24-2018 12:28 PM
Super User
Posts: 10,217

Re: ERROR: Insufficient space in file WORK

No miracle you run out of space:

proc sql outobs=10;
create table CPI.combined as
select distinct
 a.ACCTNUM,
a.CUST_ID,
b.SourceACCT,
b.CUST_ID
from CPI.CUST_profile_info a,
CPI.CUST_DEC_PRO b
where a.CUST_ID ^= b.CUST_ID;
quit;

This means that every observation in cust_profile is joined with ALL observations of cust_dec_pro that have a different cust_id.

If the datasets have distinct cust_id's, you end up with 900000 * 599999 observations (539,999,100,000)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,217

Re: ERROR: Insufficient space in file WORK

PS do you want to find matches or non-matches on a join by cust_id?

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

Re: ERROR: Insufficient space in file WORK

Posted in reply to KurtBremser

Yes, this is exactly what I'm trying to do. I was using the inner join first then I changed it to where statement.

I have also tried to submit the following query just to see if it works, but I still get the same error message after an hour.

proc sql outobs=10;

create table CPI.test as

select distinct

a.CUST_ID,

a.ACCTNUM

from CPI.CUST_INFO;

quit;

 

Super User
Posts: 10,217

Re: ERROR: Insufficient space in file WORK

SQL is notoriously bad when it has to do sorting on big tables.

Use this instead:

proc sort
  data=cpi.cust_info (keep=cust_id acctnum)
  out=cpi.test
  nodupkey
;
by cust_id acctnum;
run;

If you want to discover cust_id's in one dataset that are not present in the other, use a data step merge.

If you need to find matching cust_id's with differences in other variables, it can also be achieved in a data step merge.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 23

Re: ERROR: Insufficient space in file WORK

Posted in reply to KurtBremser

Thank you so much, it works.

you're the best Smiley Happy

Super User
Posts: 23,700

Re: ERROR: Insufficient space in file WORK

Are you using SAS UE?

Contributor
Posts: 23

Re: ERROR: Insufficient space in file WORK

No, I'm using SAS PC

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 477 views
  • 0 likes
  • 3 in conversation