topic Re: Multiple left joins on null values in SAS Procedures

Multiple left joins on null values

ashhazard — Tue, 12 Jun 2018 22:24:08 GMT

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!

Re: Multiple left joins on null values

bobpep212 — Tue, 12 Jun 2018 23:05:29 GMT

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.

Re: Multiple left joins on null values

PGStats — Wed, 13 Jun 2018 05:14:27 GMT

"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