Help using Base SAS procedures

WORK AROUND FOR CARTESIAN JOIN

Reply
Regular Contributor
Posts: 155

WORK AROUND FOR CARTESIAN JOIN

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;

TB

BL

CB

;

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.

Trusted Advisor
Posts: 2,113

Re: WORK AROUND FOR CARTESIAN JOIN

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.

Regular Contributor
Posts: 155

Re: WORK AROUND FOR CARTESIAN JOIN

Hi,

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

Respected Advisor
Posts: 3,124

Re: WORK AROUND FOR CARTESIAN JOIN

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

Haikuo

Valued Guide
Posts: 765

Re: WORK AROUND FOR CARTESIAN JOIN

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;

end;

stop;

run;

Regular Contributor
Posts: 155

Re: WORK AROUND FOR CARTESIAN JOIN

Hi,

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

PROC Star
Posts: 1,095

Re: WORK AROUND FOR CARTESIAN JOIN

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

Ask a Question
Discussion stats
  • 6 replies
  • 268 views
  • 0 likes
  • 5 in conversation