Date:         Tue, 27 Nov 2001 14:54:41 -0800
Reply-To:     Lea <lea_mw4@YAHOO.CA>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lea <lea_mw4@YAHOO.CA>
Organization: http://groups.google.com/
Subject:      SQL Many-to-Many merge didnt work... help!
Content-Type: text/plain; charset=ISO-8859-1

Hello all

I used proc sql to perform a many-to-many merge and it went horribly
wrong!
I noticed after 3 weeks of work that I mysteriously had patients
missing from my records, and much to my horror, I noticed just now
that they were lost in my proc sql statement!

this is the code I used:

proc sql;
create table bigfile
as
Select *
from file1, file2
where file1.id=file2.id;
quit;

The patients who were lost seem to be ones who weren't on file2.

I absolutely need a many-to-many merge as I have the following
scenario:

From file 1:

Obs  id  var1  var2
1     1   a     0
2     1   b     1
3     2   a     1
4     3   c     1
5     3   b     2
6     4   a     0
7     4   d     1
8     5   b     0
(*note: id #6 is not present in this file)

From File 2:

Obs  id  var3  var 4
1     1   7     x
2     2   8     y
3     2   5     y
4     4   5     x
5     4   1     q
5     5   3     y
6     5   4     z
7     6   7     x
8     6   8     z

(*note: id #3 is not present in this file)

and what I need to obtain is this :

Obs  id  var1  var2  var3   var4
 1     1   a     0     7      x
 2     1   b     1     7      x

 3     2   a     1     8      y
 4     2   a     1     5      y

 5     3   c     1     .      .
 6     3   b     2     .      .

 7     4   a     0     5      x
 8     4   d     1     5      x
 9     4   a     0     1      q
10     4   d     1     1      q

11     5   b     0     3      y
12     5   b     0     4      z

13     6   .     .     7      x
14     6   .     .     8      z

What am I doing wrong here?

Please help- I have alot of re-running of programs to do when I get
the answer!

thanks!

Lea