BookmarkSubscribeRSS Feed
brophymj
Quartz | Level 8

 A couple of years ago, I got some advice on coding and the below code worked perfectly but now when I run it I get an error message that says:

 

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

 

I'm not sure why it's not working for me now. Before, I just copied it into SAS (not sure what version I was using at the time) but now it doesn't work. 

 

Any help would be much appreciated. 

Thansk

 

DATA CAT;

INPUT SUIT :$1. @@;

CARDS;

S C H D

;

DATA SEQ;

INPUT NUM :$2. @@;

CARDS;

14 13 12 11 10 09 08 07 06 05 04 03 02

;

PROC SQL NOPRINT;

SELECT DISTINCT QUOTE(CATS(SUIT,NUM)) INTO :HANDS SEPARATED BY ' ' FROM CAT, SEQ;

QUIT;

2 REPLIES 2
nehalsanghvi
Pyrite | Level 9

The code still works. It creates the hands macro variable with the value below that you can see in the log when you do %put &hands;

 

"C02" "C03" "C04" "C05" "C06" "C07" "C08" "C09" "C10" "C11" "C12" "C13" "C14" "D02" "D03"

"D04" "D05" "D06" "D07" "D08" "D09" "D10" "D11" "D12" "D13" "D14" "H02" "H03" "H04" "H05"

"H06" "H07" "H08" "H09" "H10" "H11" "H12" "H13" "H14" "S02" "S03" "S04" "S05" "S06" "S07"

"S08" "S09" "S10" "S11" "S12" "S13" "S14"

 

SAS gives you that message to alert you that you are performing the join without a join key. When done unintentionally and on very large datasets with millions of rows, this type of join can bog down sytems/computing resources and produce unexpected results.

Reeza
Super User

It's a warning, not an error.

 

833 ;
834 PROC SQL NOPRINT;
835 SELECT DISTINCT QUOTE(CATS(SUIT,NUM)) INTO :HANDS SEPARATED BY ' ' FROM CAT cross join SEQ;
NOTE: The execution of this query involves performing one or more Cartesian product joins that
can not be optimized.
836 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.15 seconds
cpu time 0.03 seconds

 

Your results are correct. The only way I know to suppress the error will suppress all errors. 

If you need a clean log, you'll have to find a different method. I hate that if you explicitly state that you're doing  a cross join it doesn't go away. 

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!

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.

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
  • 2 replies
  • 860 views
  • 1 like
  • 3 in conversation