Date:         Fri, 21 Feb 1997 23:32:01 GMT
Reply-To:     Allan Page <pageal@TOTAL.NET>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Allan Page <pageal@TOTAL.NET>
Organization: CIBC
Subject:      Re: Merging two tables with duplicates in each

> James Redman wrote:
> >
> > Hi all,
> >
> > I have two tables that I would like to merge together to get all
> > possible combinations. For example :-
> >
> > Table1                  Table2
> > X       Y               X       Z
> > ---------------------------------
> > a       1               a       3
> > a       2               a       4
> > b       5               b       7
> > c       6               c       8
> >
> > And would like to merge it into a table :-
> >
> > Merged
> > X       Y       Z
> > -----------------
> > a       1       3
> > a       1       4
> > a       2       3
> > a       2       4
> > b       5       7
> > c       6       8
> >
> > I am having great trouble getting the 4 obs where X='a'. All my
attempts
> > only come back with 2.
> >
 
 
> The SQL Procedure handles this quite nicely, since it returns the
Cartesian product of a join.  I tried the following code and got exactly
the results you were looking for as suggested in you note above.
 
> proc sql;
     select a.x, y, z
    from one a, two b
    where a.x = b.x;
quit;
>