topic Re: Compare across observations with a conditional in SAS Programming

Compare across observations with a conditional

hrts4him — Thu, 26 Oct 2017 17:37:10 GMT

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;

Re: Compare across observations with a conditional

andreas_lds — Thu, 26 Oct 2017 18:39:35 GMT

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;

Re: Compare across observations with a conditional

s_lassen — Fri, 27 Oct 2017 11:15:39 GMT

@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;

Re: Compare across observations with a conditional

hrts4him — Fri, 27 Oct 2017 13:48:08 GMT

This works! Thanks.