To check if a value is same all through the dataset for a particular variable

[r3570]

Dear All,

I have a query.

I have variables in a dataset USUBJID, LBTESTCD, LBDTC and LBREFID.

On a particular day (LBDTC), a subject (USUBJID) has undergone a test (LBTESTCD) for which a lab reference number (LBREFID) is allocated.

Now assume there are so many tests performed for a particular subject on a particular day and every test has a unique lbrefid. I would like to write a program to check if for a particular USUBJID and for particular LBREFID the dates LBDTC should be same all through the dataset else i need to mark it as query.

ex: USUBJID        LBTESTCD   LBREFID           LBDTC

       A101                   Hb               21989         21-08-2020

       A102                   Crea            22989        22-08-2020

       A101                    RBC           21989        11-09-2020

       A102                  WBC            22989        22-08-2020

In the above example for subject A101 the LBREFID is same but the LBDTC is different. Ideally as per my requirement LBDTC should be same for both Hb and RBC as it has same LBREFID, but here it is different. So i need to separate out such records from a dataset which has around 25000 observations.

Kindly help to write a program.

[Ksharp]

Assuming I understand what you mean.

data have;
input USUBJID   $     LBTESTCD  $ LBREFID           LBDTC : ddmmyy10.;
format LBDTC  ddmmyy10.;
cards;
       A101                   Hb               21989         21-08-2020
       A102                   Crea            22989        22-08-2020
       A101                    RBC           21989        11-09-2020
       A102                  WBC            22989        22-08-2020
;

proc sql;
create table want as
 select *
  from have
   group by USUBJID,LBREFID   
    having count(distinct LBDTC ) ne 1;
quit;

[PaigeMiller]

This will identify all USUBJID LBREFID combinations where the dates are not all the same. It assumes that LBDTC is numeric.

proc summary data=have nway;
    class usubjid lbrefid;  
    var lbdtc;
    output out=_range_ range=range;
run;

data want;
    set _range_;
    if _freq_>1 and range^=0;
run;