Merge 2 tables and send matches to out table A and non matches to out table B

[podarum]

Hi, I'm trying to use proc sql to merge 2 tables.. I want the matches (between the 2 tables) to be sent to Table A and the non matches to table B.  I'm sure this is extremely easy for some.

Thanks

[RW9]

Well, its easier in datastep (note id is just adhoc, change to your matching vars):

data both others;

     merge have1 (in=a) have2 (in=b); /* assumes sorted */

     by id;

     if a and b then output both;

     else output others;

run;

Alternatively in SQL (no need to sort):

proc sql;

     create table BOTH as

     select      A.*,

                     B.*

     from        HAVE1 A

     inner join    HAVE2 B

     on           A.ID=B.ID;

     create table OTHERS as    

     select     A.*,

                     B.*

     from        HAVE1 A

     full outer join HAVE2 B

     on           A.ID=B.ID;

quit;

[Haikuo]

,

Are you sure that  your data step solution is equivalent to your SQL one?

Haikuo

[RW9]

Well, just typed that out from memory so may not be.  Not actually tested it as no SAS at the moment, so take with a pinch of salt.  Thanks for pointing out Haikuo.

[Ksharp]

When you use LEFT JOIN or RIGHT JOIN , make sure using WHERE not ON clause .