You know, at this point I'm still not clear on what you want, so I hope someone else can jump in and give you an answer.
Thank a lot for your help anyway.
I think I finally get what you want. Try this code:
data have;
input account_nr $ month balance;
cards;
1A 1 90
1B 1 500
1C 1 600
1D 1 80
2A 1 300
2B 1 200
2C 1 75
2D 1 780
3A 1 650
3B 1 90
3C 1 336
3D 1 296
1A 2 250
1B 2 525
1C 2 630
1D 2 60
2A 2 90
2B 2 95
2C 2 125
2D 2 80
3A 2 300
3B 2 145
3C 2 300
3D 2 128
4A 2 125
4B 2 150
1A 3 45
1B 3 130
1C 3 150
1D 3 50
2A 3 50
2B 3 157
2C 3 100
2D 3 60
3A 3 150
3B 3 300
3C 3 145
3D 3 530
4A 3 90
4B 3 115
;
run;
proc sort data=have;
by account_nr month;
run;
/* set up a dataset that marks accounts for deletion,
and the month where the deletion should start */
data deletes (keep=account_nr startmonth);
set have;
by account_nr;
retain flag startmonth;
if first.account_nr then flag = 0;
if balance < 100
then do;
if not flag then startmonth = month;
flag = 1;
end;
else flag = 0; /* reset when balance >= 100 */
if last.account_nr and flag then output;
run;
data want (drop=startmonth);
merge
have (in=a)
deletes (in=b)
;
by account_nr;
if a;
if not b or month < startmonth; /* no delete at all, or startmonth not yet reached */
run;
/* restore original order */
proc sort data=want;
by month account_nr;
run;
Note how I presented your original example data. Posting data in a data step allows people to easily and exactly recreate your example data.
Thank you so much for your help. This is exactly what I want. I really appreciate that. You are such a genius
Then please mark the post that did it as the solution.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: