BookmarkSubscribeRSS Feed
JasonNC
Quartz | Level 8

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.

6 REPLIES 6
Doc_Duke
Rhodochrosite | Level 12

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.

JasonNC
Quartz | Level 8

Hi,

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

Haikuo
Onyx | Level 15

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

Haikuo

MikeZdeb
Rhodochrosite | Level 12

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;

JasonNC
Quartz | Level 8

Hi,

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

TomKari
Onyx | Level 15

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1197 views
  • 0 likes
  • 5 in conversation