BookmarkSubscribeRSS Feed
rishabhmehra13
Calcite | Level 5

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

 

 

4 REPLIES 4
alberts
Fluorite | Level 6
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
Super User

 

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
Super User

 

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1713 views
  • 0 likes
  • 4 in conversation