Here is an Oracle SQL code / algorithm that you can use directly if your data is already in a database or convert to SAS to perform such reconciliation. It identifies missing records on either data-set as well as discrepancies in the modality column. select pks.*,
nvl(c.date_of_exam, v.date_of_exam) as date_of_exam,
case
when c.modality is null then
'Missing in Client Database'
when v.modaility is null then
'Missing in Vendor Database'
when c.modality <> v.modailty then
'Client has ' || c.modality || ' and vendor has ' || v.modality
else
null
end as type_of_discrepancy
(select distinct subject, visit
from client
union
select distinct subject, visit
from vendor) pks left join client c on pks.subject = c.subject and pks.visit = c.visit left join vendor v on pks.subject = v.subject and pks.visit = v.visit
... View more