Cartesian Product

Hi All,

i wanted to do a cartesian product
for ex:
data temp;
input a b;
cards;
1 2
3 4
5 6
;
run;
data temp1;
input c;
cards;
3
7
11
;
run;


proc sql;

create table temp3 as select a,b,c from temp,temp1;
quit;

Am getting the required output.But when i look into SAS log am getting a
Note The execution of this query involves performing one or more Cartesian product joins that can
not be optimized.
Even though it a Note i dont want the same to be present
is there any possible way to remove the note.
or is there any other way to do cartesian product ???

Thanks in Advance
Saranya.

On Aug 9, 12:21*am, Saranya Subramanian <saranya...@gmail.com> wrote:
> Hi All,
>
> i wanted to do a cartesian product
> for ex:
> data temp;
> input a b;
> cards;
> 1 2
> 3 4
> 5 6
> ;
> run;
> data temp1;
> input c;
> cards;
> 3
> 7
> 11
> ;
> run;
>
> proc sql;
>
> * * * * create table temp3 as select a,b,c from temp,temp1;
> quit;
>
> Am getting the required output.But when i look into SAS log am getting a
> Note *The execution of this query involves performing one or more Cartesian product joins that can
> * * * not be optimized.
> Even though it a Note i dont want the same to be present
> is there any possible way to remove the note.
> or is there any other way to do cartesian product ???
>
> Thanks in Advance
> Saranya.

data temp;
input a b;
cards;
1 2
3 4
5 6
;

data temp1;
input c;
cards;
3
7
11
;

data want;
set temp;
do i = 1 to totobs;
set temp1 point = i nobs = totobs; output;
end;
run;

proc print;
run;

Obs a b c

1 1 2 3
2 1 2 7
3 1 2 11
4 3 4 3
5 3 4 7
6 3 4 11
7 5 6 3
8 5 6 7
9 5 6 11

Try this:

data temp;
retain __Xtra 123 ;
input a b;
cards;
1 2
3 4
5 6
;
run;
data temp1;
retain __Xtra 123 ;;
input c;
cards;
3
7
11
;
run;

proc sql;
create table temp3 as
select a,b,c
from temp , temp1
where temp.__Xtra EQ temp1.__Xtra;
quit;

On Wednesday, August 8, 2012 12:21:08 PM UTC-4, Saranya Subramanian wrote:
> Hi All,
>
>
>
> i wanted to do a cartesian product
>
> for ex:
>
> data temp;
>
> input a b;
>
> cards;
>
> 1 2
>
> 3 4
>
> 5 6
>
> ;
>
> run;
>
> data temp1;
>
> input c;
>
> cards;
>
> 3
>
> 7
>
> 11
>
> ;
>
> run;
>
>
>
>
>
> proc sql;
>
>
>
> create table temp3 as select a,b,c from temp,temp1;
>
> quit;
>
>
>
> Am getting the required output.But when i look into SAS log am getting a
>
> Note The execution of this query involves performing one or more Cartesian product joins that can
>
> not be optimized.
>
> Even though it a Note i dont want the same to be present
>
> is there any possible way to remove the note.
>
> or is there any other way to do cartesian product ???
>
>
>
> Thanks in Advance
>
> Saranya.

On Friday, August 10, 2012 6:52:43 AM UTC+5:30, lchmdream wrote:
> On Aug 9, 12:21*am, Saranya Subramanian <saranya...@gmail.com> wrote:
>
> > Hi All,
>
> >
>
> > i wanted to do a cartesian product
>
> > for ex:
>
> > data temp;
>
> > input a b;
>
> > cards;
>
> > 1 2
>
> > 3 4
>
> > 5 6
>
> > ;
>
> > run;
>
> > data temp1;
>
> > input c;
>
> > cards;
>
> > 3
>
> > 7
>
> > 11
>
> > ;
>
> > run;
>
> >
>
> > proc sql;
>
> >
>
> > * * * * create table temp3 as select a,b,c from temp,temp1;
>
> > quit;
>
> >
>
> > Am getting the required output.But when i look into SAS log am getting a
>
> > Note *The execution of this query involves performing one or more Cartesian product joins that can
>
> > * * * not be optimized.
>
> > Even though it a Note i dont want the same to be present
>
> > is there any possible way to remove the note.
>
> > or is there any other way to do cartesian product ???
>
> >
>
> > Thanks in Advance
>
> > Saranya.
>
>
>
> data temp;
>
> input a b;
>
> cards;
>
> 1 2
>
> 3 4
>
> 5 6
>
> ;
>
>
>
> data temp1;
>
> input c;
>
> cards;
>
> 3
>
> 7
>
> 11
>
> ;
>
>
>
> data want;
>
> set temp;
>
> do i = 1 to totobs;
>
> set temp1 point = i nobs = totobs; output;
>
> end;
>
> run;
>
>
>
> proc print;
>
> run;
>
>
>
> Obs a b c
>
>
>
> 1 1 2 3
>
> 2 1 2 7
>
> 3 1 2 11
>
> 4 3 4 3
>
> 5 3 4 7
>
> 6 3 4 11
>
> 7 5 6 3
>
> 8 5 6 7
>
> 9 5 6 11

This works ..but the problem with point is i cant a where condition

This may be a solution to your where condition problem:

data a/view=a;
set temp1(where=(condition));
run;

data b/view=b;
set temp2(where=(condition));
run;

Then do the Cartesian product.