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.
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.
Hi,
My intention was to avoid messages in the log.I did it using Arrays.
It seems to me that what you want IS Cartesian Product.
Haikuo
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;
Hi,
Yup i did it in the same way.I did it after i posted the question.But any ways thx for your help.
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.