=========================================================================
Date:         Thu, 18 Jul 2002 10:50:44 -0400
Reply-To:     diskin.dennis@KENDLE.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         diskin.dennis@KENDLE.COM
Subject:      Re: Merge vs. SQL
Comments: To: abdu.elnagheeb@BANKOFAMERICA.COM
Content-type: text/plain; charset=us-ascii

Abdu,

I think if you look at your SAS log, you will see a warning for your
datastep merge that you have more than one dataset with duplicates of BY
values.
This is due to the fact that although SAS will proceed with the merge, it
is likely that you will not get what you wanted. Please see the merge
documentation for a detailed description.

FWIW,
Dennis Diskin




From:  Abdu Elnagheeb <abdu.elnagheeb@BANKOFAMERICA.COM>@LISTSERV.UGA.EDU>
       on 07/18/2002 10:17 AM

Please respond to abdu.elnagheeb@BANKOFAMERICA.COM

Sent by:    "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>


To:    SAS-L@LISTSERV.UGA.EDU
cc:

Subject:    Merge vs. SQL


Hello all.
I hope someone can help with this question. I have 3 data sets want to
merge
using SQL (left join -or your suggestion).  Below I gave a small sample and
the code to do so. Any explanantion why the results from SQL and the Data
Step (Merge) are not the same?
How can I make SQL come up with the same result as the Data Step? (I have
to
use SQL because I' m going against DB2 tables. This example just to show
results).
As you will see: The values of the variable Z were updated in the Data
Step,
while not in the SQL (in SQL values of Z = 31 and 32 were replaced with
missing).
PLEASE, email me as I have no access to the list now.

Thanks,
abdu

*********code************;
options nocenter pageno=1;
data d1 ;
     ID = 1 ; x = 5 ; output ;
     ID = 2 ; x = 7 ; output ;
     ID = 3 ; x = 9 ; output ;
run;
*proc print data = d1 ;title "***D1***";
run;
data c1 ;
     ID = 1 ; z = 11 ; y = 4 ;output ;
     ID = 1 ; z = 12 ; y = 6 ;output ;
     ID = 2 ; z = 21 ; y = 8 ;output ;
     ID = 2 ; z = 22 ; y = 9 ;output ;
run;
*proc print data = c1 ;title "***C1***";
run;
data c2 ;
     ID = 1 ; z = 11 ; y = 11;output ;
     ID = 1 ; z = 12 ; y = 13;output ;
     ID = 2 ; z = 21 ; y = 15;output ;
     ID = 2 ; z = 22 ; y = 17;output ;
     ID = 2 ; z = 23 ; y = 19;output ;
     ID = 3 ; z = 31 ; y = 21;output ;
     ID = 3 ; z = 32 ; y = 23;output ;
run;
*proc print data = c2 ;title "***C2***";
run;
proc sql ;
     create table all2 as
     select *
     from
     (select
     d1.ID, d1.x, c1.z, c1.y as y1, c2.z, c2.y as y2
     from
     d1 left join c1 on d1.ID = c1.ID
       left join c2 on d1.ID = c2.ID);
run ;
proc print data = all2 ; title "**D1 Left join C1 and C2**";
run;
/**results**/

**D1 Left join C1 and C2**

Obs    ID    x     z    y1    y2

  1     1    5    11     4    11
  2     1    5    12     6    11
  3     1    5    11     4    13
  4     1    5    12     6    13
  5     2    7    21     8    15
  6     2    7    22     9    15
  7     2    7    21     8    19
  8     2    7    22     9    19
  9     2    7    21     8    17
 10     2    7    22     9    17
 11     3    9     .     .    23
 12     3    9     .     .    21

data merged ;
     merge d1(in=d) c1(in=c1 rename=(y=y1)) c2(in=c2 rename=(y=y2)) ;
     by ID ;
run;
proc print data = merged ;
title "***D1 merged with C1 and C2***";
run;
/**Results***/
***D1 merged with C1 and C2***

Obs       ID     x          z        y1         y2

 1        1     5        11        4     11
 2        1     5        12   6          13
 3        2          7        21         8         15
 4        2          7        22         9         17
 5        2          7        23         9         19
 6        3          9        31         .         21