DATA Step, Macro, Functions and more

Select Distinct

Reply
Super Contributor
Posts: 259

Select Distinct

 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;

Frequent Contributor
Posts: 75

Re: Select Distinct

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.

Super User
Posts: 19,822

Re: Select Distinct

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. 

Ask a Question
Discussion stats
  • 2 replies
  • 173 views
  • 1 like
  • 3 in conversation