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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.