BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
tampham92
Obsidian | Level 7

Thank a lot for your help anyway.

Kurt_Bremser
Super User

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
Obsidian | Level 7

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1391 views
  • 0 likes
  • 3 in conversation