topic Re: Join tables and drop observations in SAS Procedures

Join tables and drop observations

jenim514 — Wed, 16 Dec 2015 21:23:37 GMT

Hi! I'm haveing some difficulty correclty joining two tables. TableA contains all the SSNs that I need REMOVED from TableB. TableB has the remaining obervations and all the variables I need to keep. The problem is when I run this syntax, the values in SSN disapear...I'm assuming something to do with my null statement. But when I remove the Null statement, the join (checking oservation numbers) is not correct. Is there a way to correctly write this code where my SSNs to not disappear? Thanks!

proc sql;

create table outpatient_1 as

select a.ssn, b.*

from ssn_drop as a right join ssn_complete as b

on a.ssn = b.ssn

where a.ssn is null;

quit;

Re: Join tables and drop observations

FreelanceReinh — Wed, 16 Dec 2015 22:01:32 GMT

I would simply remove a.ssn from the SELECT statement. Due to the WHERE condition all selected a.ssn values are necessarily missing. For the matching observations you have b.ssn, which is equal to a.ssn in these cases, hence a.ssn would be redundant anyway (not to mention the name conflict, see the warning in your SAS log).

Re: Join tables and drop observations

mohamed_zaki — Wed, 16 Dec 2015 22:05:50 GMT

From what i understand that you need all observation in ssn_complete but not in ssn_drop.

Your join is correct but the problem is in the selected columns, you specified that the ssn column will be from a _a.ssn _ and your deired observation does not have in a but from b, so for that you was getting null for them.

So try this

proc sql;

create table outpatient_1 as

select b.*

from ssn_drop as a right join ssn_complete as b

on a.ssn = b.ssn

where a.ssn is null;

quit;

Re: Join tables and drop observations

Astounding — Wed, 16 Dec 2015 22:10:46 GMT

Your description of the problem is a little bit different than what your code is designed to do. And since my SQL is horrible, somebody may have to fix this to get it working. At any rate, I think this is the idea you are looking for:

proc sql;

select b.* from ssn_complete as b except b.ssn in (select ssn from ssn_drop);

quit;

Re: Join tables and drop observations

PGStats — Wed, 16 Dec 2015 22:15:08 GMT

Or express your request more directly:

proc sql;
create table outpatient_1 as
select *
from ssn_complete 
where ssn not in (select ssn from ssn_drop);
quit;