Compare across observations with a conditional

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Compare across observations with a conditional

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;

 


Accepted Solutions
Solution
‎10-27-2017 09:46 AM
Super Contributor
Posts: 349

Re: Compare across observations with a conditional

To much code Smiley Wink

 

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;

View solution in original post


All Replies
Solution
‎10-27-2017 09:46 AM
Super Contributor
Posts: 349

Re: Compare across observations with a conditional

To much code Smiley Wink

 

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;
New Contributor
Posts: 2

Re: Compare across observations with a conditional

Posted in reply to andreas_lds
This works! Thanks.
PROC Star
Posts: 121

Re: Compare across observations with a conditional

@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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 109 views
  • 0 likes
  • 3 in conversation