09-24-2012 10:25 AM
I want to create a data set with a cartesian join,but i want to do with out doing cartesian join
input person $;
input test $;
I want to create a data set called Person_tests which should indicate all the three tests for each person
i.e like this
Can you please let me know how to achieve this with out using cartesian join
PROC SQL NOPRINT;
CREATE TABLE PERSON_TEST AS
FROM Names AS table1,TESTS As table2 ;
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.
09-24-2012 10:33 AM
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.
09-24-2012 10:59 AM
hi ... maybe a data step (does the same thing SQL does, just without the note in the LOG) ...
do _n_=1 to lastrec;
set tests nobs=lastrec point=_n_;
or you could put TESTS into memory (array or hash table) rather than rereading the data set for each observation in NAMES ...
array t(3) $2 _temporary_;
do _n_=1 to 3;
t(_n_) = test;
set names end=done;
do _n_=1 to 3;
test = t(_n_);
09-24-2012 11:58 AM
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.