Date:         Tue, 27 Jun 2000 16:30:23 -0500
Reply-To:     Hays McLean <hays.mclean@AMSOUTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Hays McLean <hays.mclean@AMSOUTH.COM>
Subject:      Re: SQL merge question
Comments: To: john.zhang@QUINTILES.COM

I think that you will have to do this:
proc sql;
create table merge1 as
select     case when a.id is null then b.id else a.id end as id
           ,var1
           ,var2
from       dataset1 as a
      full join dataset2 b
           on
      a.id=b.id;
quit;


Hays McLean
AmSouth Bank
hays.mclean@amsouth.com
205-326-4849




john.zhang@QUINTILES.COM on 06/27/2000 03:27:11 PM

Please respond to john.zhang@QUINTILES.COM



 To:      SAS-L@LISTSERV.UGA.EDU

 From:


 cc:      (bcc: Hays McLean/BIRMINGHAM/AL/AMSOUTH BANK)



 Subject: SQL merge question








Hi:
I have two datasets and like to use SQL to merge them.

Dataset1 like this:

Id         value1
001       1111
002       2222
004       4444

Dataset2 like this:

Id         value2
001       121
002       222
003       323
004       424


proc sql;

create table merge1 as select *
     from dataset1 as a full join dataset2 b
     on a.id=b.id;
quit;

What I got is this:

id             value1         value2
001  1111              121
002         2222              222
                                       323
004         4444              424

I lost id for value 323.

What I like to have is:

What I got is this:

id             value1         value2
001  1111              121
002         2222              222
003                                323
004         4444              424

Anybody have idea how I can achieve this goal?

TIA


John Zhang