SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
mhoward2
Obsidian | Level 7

I have a join on a couple tables that takes FOREVER to run. Way way longer than it should and one of the notes I haven't noticed before states: 

 

NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

I'm not sure what that means exactly but a quick google indicates that it probably isn't what I'm trying to accomplish.

 

PROC SQL;
CREATE TABLE WORK.TABLE3 AS
	SELECT t1.*,
		   t2.NAME,
		   t2.DATE
	FROM WORK.TABLE1 t1 INNER JOIN
		 WORK.TABLE2 t2 ON (CASE WHEN t1.CAT1 NOT IN ('CATA', 'CATB') THEN (t1.ID1 = t2.ID1 AND t1.ID2= t2.ID2 AND t2.ID3 = t1.ID3)
							  WHEN t1.CAT1 IN ('CATA', 'CATB') THEN (t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2)
						 END);
QUIT;

Is this an acceptable way to join? Is there a better way?

 

Thanks in advance.

1 REPLY 1
SASKiwi
PROC Star

CASE in a join is not a good idea. Just do a normal join:

PROC SQL;
CREATE TABLE WORK.TABLE3 AS
   SELECT t1.*,
		  t2.NAME,
		  t2.DATE
   FROM WORK.TABLE1 t1 
   INNER JOIN WORK.TABLE2 t2 
   ON (t1.CAT1 NOT IN ('CATA', 'CATB') and t1.ID1 = t2.ID1 AND t1.ID2= t2.ID2 AND t2.ID3 = t1.ID3)
   or (t1.CAT1 IN ('CATA', 'CATB') and t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2)
   ;
QUIT;

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 3243 views
  • 0 likes
  • 2 in conversation