Date:         Sun, 13 Apr 2003 18:15:42 -0700
Reply-To:     shiling zhang <shiling99@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         shiling zhang <shiling99@YAHOO.COM>
Organization: http://groups.google.com/
Subject:      Re: Merge/join tables and remove duplicates
Content-Type: text/plain; charset=ISO-8859-1

You can do it with then following trick.

But many to many of the key merge really does not make a lot of sense
unless one knows the implied the results.

I am not sure how many people do many of the key merge (except
cartisian product) in a real production job. I think SAS should issue
a warning message other than a note.

HTH.


data t1;
input
id var1;
var2=.;
cards;
1  1
1  2
1  3
2  5
3  11
3  12
3  13
3  14
3  15
4  21
4  22
4  23
;

data t2;
input
id var2;
cards;
1  .
2  .
3  51
3  52
3  53
3  54
4  61
;

data t3;
  merge t1 t2;
  by id;
run;

options nocenter;
proc print;run;

********************output************;
The SAS System                                                  20:54
Sunday, April 13, 2003   6

Obs    id    var1    var2

  1     1      1       .
  2     1      2       .
  3     1      3       .
  4     2      5       .
  5     3     11      51
  6     3     12      52
  7     3     13      53
  8     3     14      54
  9     3     15       .
 10     4     21      61
 11     4     22       .
 12     4     23       .


david5705@hotmail.com (David) wrote in message news:<de569bf2.0304130815.19ef02c@posting.google.com>...
> Dear All:
>
> I wish to merge/join two tables:
>
> Table 'a'
>
> id var1
> 1  1
> 1  2
> 1  3
> 2  5
> 3  11
> 3  12
> 3  13
> 3  14
> 3  15
> 4  21
> 4  22
> 4  23
>
> Table 'b'
>
> id var2
> 1  .
> 2  .
> 3  51
> 3  52
> 3  53
> 3  54
> 4  61
>
> Desired output after merge/join
>
> id var1 var2
> 1  1    .
> 1  2    .
> 1  3    .
> 2  5    .
> 3  11   51
> 3  12   52
> 3  13   53
> 3  14   54
> 3  15   .
> 4  31   61
> 4  32   .
> 4  33   .
>
> When I merge/join the tables I'm getting duplicates for var2 when using:
>
> data temp1 ;
>   merge temp2 temp3 ;
>   by id ;
> run ;
>
> ID var1 var2
> 1  1    .
> 1  2    .
> 1  3    .
> 2  5    .
> 3  11   51
> 3  12   52
> 3  13   53
> 3  14   54
> 3  15   54
> 4  31   61
> 4  32   61
> 4  33   61
>
> What do I need to add to the data step to eliminate the duplicates in var2?
> If possible what would be the equivalent proc sql code?
>
> Thanks in advance,
>
> David.