ERROR: Insufficient space in file WORK

[rishabhmehra13]

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

[alberts]

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

[Reeza]

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. 

[Reeza]

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. 

[Kurt_Bremser]

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.