topic Re: ERROR: Insufficient space in file WORK in SAS Studio

ERROR: Insufficient space in file WORK

rishabhmehra13 — Wed, 14 Sep 2016 22:26:23 GMT

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;

56 proc sql;

57 creat table q6 as

_____

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.

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

Re: ERROR: Insufficient space in file WORK

alberts — Wed, 14 Sep 2016 23:37:52 GMT

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

Re: ERROR: Insufficient space in file WORK

Reeza — Thu, 15 Sep 2016 01:37:56 GMT

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.

Re: ERROR: Insufficient space in file WORK

Reeza — Thu, 15 Sep 2016 01:37:57 GMT

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.

Re: ERROR: Insufficient space in file WORK

Kurt_Bremser — Thu, 15 Sep 2016 06:47:33 GMT

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.