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!
"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
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.
"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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.