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