Write and run SAS programs in your web browser

ERROR: Insufficient space in file WORK

Reply
Contributor
Posts: 31

ERROR: Insufficient space in file WORK

proc sql;
creat table q6 as
select a.PRODUCT_ID,a.churn_month,b.*
from d.p3 as a left join lab as b
on a.PRODUCT_ID ne b.PRODUCT_ID;

QUIT;

 

 

lab table has 100000 records

 

log

 

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55        
56         proc sql;
57         creat table q6 as
            _____
            1
WARNING 1-322: Assuming the symbol CREATE was misspelled as creat.
 
58         select a.PRODUCT_ID,a.churn_month,b.*
59         from d.p3 as a left join lab as b
60         on a.PRODUCT_ID ne b.PRODUCT_ID;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
WARNING: Variable PRODUCT_ID already exists on file WORK.Q6.
ERROR: Insufficient space in file WORK.Q6.DATA.
ERROR: File WORK.Q6.DATA is damaged. I/O processing did not complete.
 
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
61        
62         QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
       real time           1:01.75
       cpu time            21.80 seconds

 

 

New Contributor
Posts: 4

Re: ERROR: Insufficient space in file WORK

Hi,
What are you trying to achieve ?
Why do you use table A left join table B on a.PRODUCT_ID ne b.PRODUCT_ID ?

If you try to extract all those records that in table A and not in table B, then you can use this method:

from d.p3 as a left join lab as b
on a.PRODUCT_ID = b.PRODUCT_ID
where b.PRODUCT_ID is null
Super User
Posts: 19,038

Re: ERROR: Insufficient space in file WORK

 

How big are your tables? A Cartesian join will be big...

How big is your workspace, if you know?

 

Also, fix the first error (create misspelled) and repost you code/log. 

 

Super User
Posts: 19,038

Re: ERROR: Insufficient space in file WORK

 

How big are your tables? A Cartesian join will be big...

How big is your workspace, if you know?

 

Also, fix the first error (create misspelled) and repost you code/log. 

 

Super User
Posts: 7,393

Re: ERROR: Insufficient space in file WORK

If your intent is to find all records in table a that do not have an entry in b, then you wrote your query wrong.

As it is, it will output every combination of tables a and b where the product ID's dont match.

Think of 100 IDs in b and 10000 records in a. For every record in a, there will be 99 records in b that fulfill your condition, therefore you get 990000 records in the output. This is what happens with you and explodes your WORK.

 

I'd rather do this to find records in a that don't have a match in b:

proc sort data=d.p3;
by product_id;
run;

proc sort data=lab;
by product_id;
run;

data q6;
merge
  d.p3 (in=a)
  lab (in=b keep=product_id)
;
by product_id;
if a and not b;
run;

 Unless you have a completely different intention, of course.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 569 views
  • 0 likes
  • 4 in conversation