Date:         Tue, 29 Oct 2002 19:15:07 -0600
Reply-To:     Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kevin Myers <KevinMyers@AUSTIN.RR.COM>
Subject:      Re: Duplicates in both tables allowed in MERGE?
Content-Type: text/plain; charset="iso-8859-1"

Other folks have already discussed the fact that merging with repeats of BY
variables in more than one input data set has always been allowed, but that
the behavior in that scenario isn't what most people really want.  A data
step can be written that works around this problem using loops, observation
pointers, and FIRST./LAST..  However, for the typical solution that most
people want, using SQL is generally much easier:

proc sql;
   create table new as
      select old1.*, old2.* from old1, old2
      where old1.byvar1=old2.byvar2 and
            old1.byvar2=old2.byvar2 and
            ...
            old1.byvarN=old2.byvarN;
   quit;

If you use the above code fairly literally, you will get a warning about the
by variables being in both source tables.  You can either list out the
variables separately in the select clause, or use different variable names
in the two source tables to eliminate that warning.

PROC SQL produces a so-called Cartesian product of the matching rows from
the two tables that meet the where clause criteria, and in most cases that
is what folks really want when they are trying to merge two tables with
repeats of BY variables.  On the other hand, the existence of that situation
can often point out flaws in the user's understanding of their data
structures which need to addressed before proceeding ahead blindly.

s/KAM


----- Original Message -----
From: "Will Dwinnell" <predictr@BELLATLANTIC.NET>
Newsgroups: bit.listserv.sas-l
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Tuesday, October 29, 2002 12:35 PM
Subject: Duplicates in both tables allowed in MERGE?


> One of my older SAS books indicates that the BY column in a MERGE may
> have duplicates in either table being merged, but not both (I'll
> assume we're merging just two data sets on a single column for now).
> Another SAS book doesn't make any mention of this limitation.  Is this
> still an issue?  Was it ever an issue?
>
> Many thanks...