topic Select Distinct in SAS Programming

Select Distinct

brophymj — Tue, 28 Feb 2017 17:33:46 GMT

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;

Re: Select Distinct

nehalsanghvi — Tue, 28 Feb 2017 17:42:25 GMT

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.

Re: Select Distinct

Reeza — Tue, 28 Feb 2017 20:38:51 GMT

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.