Help using Base SAS procedures

Join tables and drop observations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 142
Accepted Solution

Join tables and drop observations

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;


Accepted Solutions
Solution
‎12-16-2015 05:54 PM
Respected Advisor
Posts: 4,936

Re: Join tables and drop observations

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;
PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,118

Re: Join tables and drop observations

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).

Super Contributor
Posts: 490

Re: Join tables and drop observations

[ Edited ]

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;

Super User
Posts: 5,518

Re: Join tables and drop observations

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;

 

 

Solution
‎12-16-2015 05:54 PM
Respected Advisor
Posts: 4,936

Re: Join tables and drop observations

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;
PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 390 views
  • 2 likes
  • 5 in conversation