MACRO SAS DO loop

[PaigeMiller]

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.

[tampham92]

Thank a lot for your help anyway.

[Kurt_Bremser]

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.

[tampham92]

Thank you so much for your help. This is exactly what I want. I really appreciate that. You are such a genius

[Kurt_Bremser · Accepted solution]

Then please mark the post that did it as the solution.