Help using Base SAS procedures

Multiple left joins on null values

Accepted Solution Solved
Reply
Frequent Learner
Posts: 1
Accepted Solution

Multiple left joins on null values

I've been looking online for what seems like hours. Is anyone able to explain why in the following code AND is used between where clauses rather than OR? I'm trying to say that if the variable is in any of the tables then it should be deleted not if it is in ALL of the tables. However, when I use OR it doesn't appear to delete any of the variables but with AND it does.

 

proc sql ;
create table spec2 as
select a.*
from spec a
left join qtnhst.Medpro_spec b
on a.accession_num = b.accession_num AND
a.lab = b.lab AND
a.specimen_first_rpt_date = b.specimen_first_rpt_date
left join qtnhst.DL_spec c
on a.accession_num = c.accession_num AND
a.lab = c.lab AND
a.specimen_first_rpt_date = c.specimen_first_rpt_date
left join qtnhst.EXCL_spec d
on a.accession_num = d.accession_num AND
a.lab = d.lab AND
a.specimen_first_rpt_date = d.specimen_first_rpt_date
left join qtnhst.chng_clnt_hst e
on a.accession_num = e.accession_num AND
a.lab = e.lab AND
a.client_num = e.new_client_num
where (b.accession_num is NULL AND b.lab is NULL AND b.specimen_first_rpt_date is NULL) AND
(c.accession_num is NULL AND c.lab is NULL AND c.specimen_first_rpt_date is NULL) AND
(d.accession_num is NULL AND d.lab is NULL AND d.specimen_first_rpt_date is NULL)
order by accession_num, lab, specimen_first_rpt_date, specimen_type, change_date desc ;
quit ;

 

Thanks!


Accepted Solutions
Solution
a month ago
Esteemed Advisor
Posts: 5,529

Re: Multiple left joins on null values

Posted in reply to ashhazard

"if the variable is in any of the tables then it should be deleted" ==> Keep the row if it is in none of the tables, i.e. it is not in table b and not in table c and not in table d

PG

View solution in original post


All Replies
Occasional Contributor
Posts: 11

Re: Multiple left joins on null values

Posted in reply to ashhazard

Kind of tough without seeing the data and being able to play with it. Does it work if you wrap the whole where clause in parentheses with OR? If your left table matches anything from b, c, or d, it is going to keep those rows from table a when you use OR. 

 

 WHERE ((b.accession_num is NULL AND b.lab is NULL AND b.specimen_first_rpt_date is NULL) OR
(c.accession_num is NULL AND c.lab is NULL AND c.specimen_first_rpt_date is NULL) OR
(d.accession_num is NULL AND d.lab is NULL AND d.specimen_first_rpt_date is NULL))

 

I think this will work if you want to keep the rows from A where you don't find a match in B or C or D.

Solution
a month ago
Esteemed Advisor
Posts: 5,529

Re: Multiple left joins on null values

Posted in reply to ashhazard

"if the variable is in any of the tables then it should be deleted" ==> Keep the row if it is in none of the tables, i.e. it is not in table b and not in table c and not in table d

PG
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 131 views
  • 2 likes
  • 3 in conversation