Outputting specific results from data check using PROC SQL

Question

I have this code to check for correct baseline lab values in SDTM.

/* Checking for no baseline results*/
proc sql;
create table dca as
select a.usubjid, a.lbtestcd,a.lbblfl,a.lbdy,a.lbstresn,b.good_value
from lb as a
left join
(select usubjid,lbtestcd,lbdy as good_value label='Baesline record' from lb
where lbdy < 0 group by usubjid, lbtestcd having lbdy= max(lbdy)) as b 
on a.usubjid = b.usubjid and a.lbtestcd = b.lbtestcd;

create table dcb as 
select unique 'LB' as domain,
compbl("USUBJID/lbblfl Subset") as key_grp_vr length = 50
, case when lbblfl='Y' and ^(lbdy=good_value) then 
'FAIL: At least one Baseline is Not correct'
else 'PASS: All Baselines are correct' end as dc_rslt label="Data Check Results for:" length =75
from dca
;
quit;

proc sort data=dca;
by usubjid lbtestcd lbdy;
run; 

proc print data=dcb; run;

DCA creates GOOD_VALUE using LEFT JOIN and subquery with WHERE condition to filter records. HAVING condition to filter by summary function and GROUP BY.DCB has case block with condition to confirm LBBLFL and GOOD_VALUE. This is the output

So since DCB shows both PASS and FAIL this means there at least one baseline flag which is incorrect. While this is useful I was wondering if there was some way to create another table to output the exact records where this is the case. Would anyone have any suggestions on how to do this?

Answer 1

Sort DCB and return values where they're the same but it doesn't seem like you have any unique identifier in that table as well?

proc sort data=dcb;
by domain key_gpr_vr;
run;

data mismatched;
set dcb;
by domain key_grp_vr;
IF not(first.key_grp_vr and last.key_grp_vr);
run;