Hello Everybody!
I have a dataset defined as bellow:
BANK_AGENCY ID_Customer Date Overtaking
A 0001 10/02/2021 3
A 0002 12/03/2021 3
A 0003 13/04/2021 3
A 0004 25/05/2021 3
B 0005 18/01/2021 2
B 0006 25/02/2021 2
B 0007 03/06/2021 2
Overtaking is the number of rows to delete by agency and ordering by date
For simplicity, I would like to get this result:
BANK_AGENCY ID_Customer Date Overtaking
A 0002 12/03/2021 3
A 0003 13/04/2021 3
A 0004 25/05/2021 3
B 0005 18/01/2021 2
Can you help me please ? Any suggestions are welcome 🙂
Thank u in advance
data have;
input BANK_AGENCY $ ID_Customer $ Date :ddmmyy10. Overtaking;
format date ddmmyy10.;
datalines;
A 0001 10/02/2021 3
A 0002 12/03/2021 3
A 0003 13/04/2021 3
A 0004 25/05/2021 3
B 0005 18/01/2021 2
B 0006 25/02/2021 2
B 0007 03/06/2021 2
;
proc sort data=have;
by bank_agency date;
run;
data want;
count = 1;
do until (last.bank_agency);
set have;
by bank_agency;
if count le overtaking then output;
count + 1;
end;
drop count;
run;
I take it that your expected result misses an observation for bank_agency = "B".
Sorry the overtaking is the rows number to keep and not to delete
data have;
input BANK_AGENCY $ ID_Customer $ Date :ddmmyy10. Overtaking;
format date ddmmyy10.;
datalines;
A 0001 10/02/2021 3
A 0002 12/03/2021 3
A 0003 13/04/2021 3
A 0004 25/05/2021 3
B 0005 18/01/2021 2
B 0006 25/02/2021 2
B 0007 03/06/2021 2
;
proc sort data=have;
by bank_agency date;
run;
data want;
count = 1;
do until (last.bank_agency);
set have;
by bank_agency;
if count le overtaking then output;
count + 1;
end;
drop count;
run;
I take it that your expected result misses an observation for bank_agency = "B".
It works perfectly! thank you so much 🙂
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.