Help using Base SAS procedures

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

Reply
Super Contributor
Posts: 401

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

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

Super User
Super User
Posts: 7,942

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

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;

Respected Advisor
Posts: 3,156

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

,

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

Haikuo

Super User
Super User
Posts: 7,942

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

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.

Super User
Posts: 10,020

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

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

Ask a Question
Discussion stats
  • 4 replies
  • 183 views
  • 0 likes
  • 4 in conversation