Compare across observations with a conditional

[hrts4him]

I am using SAS EG 6.1. I have several million row table that I need to see if there is a status of DNM_Duplicate that there is another record with that same contract_nbr that has status that is not DNM_Duplicate . Here is what the data looks like:

Contract_Nbr	Status
123	Complete
456	Complete
456	DNM_Duplicate
789	DNM_Duplicate
991	DNM_Duplicate
991	DNM_Duplicate
772	Complete

For my needed logic the only records I would see in the output would be Contrac_nbr 789 and 991. I have tried the first.last dot and the do until but can't get that conditional to work and I only get the 1st and last compare.

data test59;

set test59a;

 by ConCode_Nbr Status;

    do until (last.ConCode_Nbr);

     if first.Status = last.status ; 

    end;

if ConCode_Combo = ' ' then delete;

run;

[andreas_lds · Accepted solution]

To much code 😉

data have;
length Contract_Nbr 8 Status $ 20;
input Contract_Nbr Status;

datalines;
123 Complete
456 DNM_Duplicate
456 Complete
789 DNM_Duplicate
991 DNM_Duplicate
991 DNM_Duplicate
772 Complete
;
run;

proc sort data=have;
   by Contract_Nbr Status;
run;

data want;
   set have;
   by Contract_Nbr;
   
   if first.Contract_Nbr and Status = 'DNM_Duplicate';
run;

[hrts4him]

This works! Thanks.

[s_lassen]

@hrts4him

Here is a possible solution: use WHERE clauses and merge:

data have;
length Contract_Nbr 8 Status $ 20;
input Contract_Nbr Status;
cards;
123 Complete
456 DNM_Duplicate
456 Complete
789 DNM_Duplicate
991 DNM_Duplicate
991 DNM_Duplicate
772 Complete
;
run;

proc sort data=have;
  by contract_nbr;
run;

data want;
  merge
    have(where=(status^='DNM_Duplicate') in=drop)
    have(where=(status='DNM_Duplicate') in=OK)
    ;
  by contract_nbr;
  if ok and not drop;
run;