BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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

mohamed_zaki
Barite | Level 11

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;

Astounding
PROC Star

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;

 

 

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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