Date:         Tue, 4 Dec 2001 13:57:12 -0700
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: Cartesian product
Comments: To: cafo@INT.TELE.DK
Content-Type: text/plain; charset=us-ascii

Does this do what you want?

=====
786  options compress=no;
787  data a;
788     length b $10;
789     a=1; b='BO'; output;
790     a=2; b='BO'; output;
791     a=3; b='IB'; output;
792     a=4; b='MY'; output;
793     a=5; b='MY'; output;
794  run;

NOTE: The data set WORK.A has 5 observations and 2 variables.
NOTE: DATA statement used:
      real time           0.03 seconds
      cpu time            0.02 seconds


795
796  proc sort data=a;
797     by b a;
798  run;

NOTE: SAS sort was used.
NOTE: There were 5 observations read from the data set WORK.A.
NOTE: The data set WORK.A has 5 observations and 2 variables.
NOTE: PROCEDURE SORT used:
      real time           0.02 seconds
      cpu time            0.02 seconds


799
800  data aa;
801     length b $10;
802     aa=1; b='BO'; output;
803     aa=2; b='BO'; output;
804     aa=3; b='IB'; output;
805     aa=51; b='MY'; output;
806     aa=52; b='MY'; output;
807     aa=53; b='MY'; output;
808     aa=54; b='MY'; output;
809  run;

NOTE: The data set WORK.AA has 7 observations and 2 variables.
NOTE: DATA statement used:
      real time           0.02 seconds
      cpu time            0.02 seconds


810
811  proc sort data=aa;
812     by b aa;
813  run;

NOTE: SAS sort was used.
NOTE: There were 7 observations read from the data set WORK.AA.
NOTE: The data set WORK.AA has 7 observations and 2 variables.
NOTE: PROCEDURE SORT used:
      real time           0.10 seconds
      cpu time            0.02 seconds


814
815  data combined (drop=aa_b  sortedby=b a aa);
816     set a;
817     do pointer = 1 to aa_nobs;
818        set aa (rename=(b=aa_b)) nobs=aa_nobs point=pointer;
819        if b = aa_b then
820           output;
821     end;
822  run;

NOTE: There were 5 observations read from the data set WORK.A.
NOTE: The data set WORK.COMBINED has 13 observations and 3 variables.
NOTE: DATA statement used:
      real time           0.04 seconds
      cpu time            0.03 seconds


823
824  /* Verify that data are correctly sorted.  */
825  data _null_;
826     set combined;
827        by b a aa;
828  run;

NOTE: There were 13 observations read from the data set WORK.COMBINED.
NOTE: DATA statement used:
      real time           0.01 seconds
      cpu time            0.01 seconds

=====


--
JackHamilton@FirstHealth.com
Development Manager, Technical Group
METRICS Department, First Health
West Sacramento, California USA


>>> CAFO <cafo@INT.TELE.DK> 12/04/2001 7:09 AM >>>
Hi ,

How do I create the Cartesian product that I get from the program
below
using datastep in stead of sql ?

data a;

length b $10;

a=1; b='BO'; output;

a=2; b='BO'; output;

a=3; b='IB'; output;

a=4; b='MY'; output;

a=5; b='MY'; output;

run;

data aa;

length b $10;

aa=1; b='BO'; output;

aa=2; b='BO'; output;

aa=3; b='IB'; output;

aa=51; b='MY'; output;

aa=52; b='MY'; output;

aa=53; b='MY'; output;

aa=54; b='MY'; output;

run;

proc sql;

create table kk as

select *

from a, aa

where a.b=aa.b;

quit;

proc sort;

by b a aa;

run;