Date:         Fri, 20 Jun 2003 14:14:00 -0600
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: Merge with duplicate by values
Comments: To: harbourcharles@JOHNDEERE.COM
Content-Type: text/plain; charset=us-ascii

The data step merge has never created a cartesian join and has never
claimed to (at least not in the last 15 or 20 years).

As far as I know, the result of merging two data sets with multiple
observations of the same key has never been defined by SAS Institute.



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

>>> "Charles Harbour" <harbourcharles@JOHNDEERE.COM> 06/20/2003 12:56
PM >>>
Sanity check, please!

Many moons ago, when I was first learning sas, I remember learning that
if
you merged two datasets that both had multiple values of the by
variable
(s), you would get a cartesian join (complete cross product) in your
merged
dataset.  This is currently not the case (demonstrated below, example
adapted from the sas doc).  Sas doc shows a many to one merge and a one
to
many merge, but not a many to many.  In my case, I do want a cartesian
join, and have switched to use proc sql with a full join.

Do I have too many grey hairs, or did sas used to give a cartesian join
and
now does not?

Thanks,

Chip Harbour
Technical Consultant
Capacity Planning / Computer Performance - S390 Support Services
Deere & Co
Moline, Ill. 61265


Running sasv8 on the host:

DATA ONE;
  INPUT  ID $ FRUIT $;
  CARDS;
  A APPLE
  A APPLEMELON
  ;
RUN;

DATA TWO;
  INPUT ID $ ANIMAL $;
  CARDS;
  A ANT
  A AARDVARK
  A ANIMAL
  A ANTELOPE
  ;
RUN;

DATA MERGED;
  MERGE ONE TWO;
  BY ID;
RUN;
PROC PRINT DATA=MERGED; RUN;


OBS    ID    FRUIT       ANIMAL

 1     A     APPLE       ANT
 2     A     APPLEMEL    AARDVARK
 3     A     APPLEMEL    ANIMAL
 4     A     APPLEMEL    ANTELOPE