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