topic Re: Merge 2 tables and send matches to out table A and non matches to out table B in SAS Procedures

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

podarum — Tue, 30 Sep 2014 15:38:55 GMT

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

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

RW9 — Tue, 30 Sep 2014 16:55:40 GMT

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;

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

Haikuo — Tue, 30 Sep 2014 18:01:33 GMT

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

Haikuo

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

RW9 — Wed, 01 Oct 2014 09:37:08 GMT

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.

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

Ksharp — Wed, 01 Oct 2014 13:20:42 GMT

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