topic Re: WORK AROUND FOR CARTESIAN JOIN in SAS Procedures

WORK AROUND FOR CARTESIAN JOIN

JasonNC — Mon, 24 Sep 2012 14:25:58 GMT

Hi ,

I want to create a data set with a cartesian join,but i want to do with out doing cartesian join

For ex;

data Names;

input person $;

datalines;

Jason

Jim

Richard

;

run;

Data Tests;

input test $;

datalines;

;

run;

I want to create a data set called Person_tests which should indicate all the three tests for each person

i.e like this

Jason TB

Jason BL

Jason CB

Jim TB

Jim BL

Jim CB

Can you please let me know how to achieve this with out using cartesian join

PROC SQL NOPRINT;

CREATE TABLE PERSON_TEST AS

SELECT table1.*,table2.test

FROM Names AS table1,TESTS As table2 ;

QUIT;

When i run this it will show

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

i want to achieve the results with out Cartesian join.

Re: WORK AROUND FOR CARTESIAN JOIN

Doc_Duke — Mon, 24 Sep 2012 14:33:27 GMT

To me, the question doesn't make much sense. You appear to want a Cartesian Join as the result. To do it in SQL, you've done it fine. You can simulate a Cartesian Join with a data step, but you are still doing a Cartesian join and it will still take multiple passes of the data.

Re: WORK AROUND FOR CARTESIAN JOIN

Haikuo — Mon, 24 Sep 2012 14:33:48 GMT

It seems to me that what you want IS Cartesian Product.

Haikuo

Re: WORK AROUND FOR CARTESIAN JOIN

MikeZdeb — Mon, 24 Sep 2012 14:59:18 GMT

hi ... maybe a data step (does the same thing SQL does, just without the note in the LOG) ...

data person_test;

set names;

do _n_=1 to lastrec;

set tests nobs=lastrec point=_n_;

output;

end;

run;

or you could put TESTS into memory (array or hash table) rather than rereading the data set for each observation in NAMES ...

data person_test;

array t(3) $2 _temporary_;

do _n_=1 to 3;

set tests;

t(_n_) = test;

end;

do until(done);

set names end=done;

do _n_=1 to 3;

test = t(_n_);

output;

end;

stop;

run;

Re: WORK AROUND FOR CARTESIAN JOIN

JasonNC — Mon, 24 Sep 2012 15:35:04 GMT

Hi,

My intention was to avoid messages in the log.I did it using Arrays.

Re: WORK AROUND FOR CARTESIAN JOIN

JasonNC — Mon, 24 Sep 2012 15:36:06 GMT

Hi,

Yup i did it in the same way.I did it after i posted the question.But any ways thx for your help.

Re: WORK AROUND FOR CARTESIAN JOIN

TomKari — Mon, 24 Sep 2012 15:58:11 GMT

If it's the NOTE that's bothering you, just ignore it. It is VERY rare to actually want a cartesian join, and I can assure you from years of experience that it's fairly common to code one by accident, so SAS just warns you, in case it's not what you want. The code works fine, and produces the result you want, and there's no better way to do it.

Tom