BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hrts4him
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

3 REPLIES 3
andreas_lds
Jade | Level 19

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
Calcite | Level 5
This works! Thanks.
s_lassen
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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