DATA Step, Macro, Functions and more

getting max and min value by group

Occasional Contributor
Posts: 10

getting max and min value by group



I have


account   date   arrears 

1              Jan      0         

1              Feb      1             

1              Mar      0             

1              Apr       2            

2              Jan       0             

2              Feb       1             

2              Mar       2             



I need


account   date   arrears  max_arrears

1              Jan      0              0

1              Feb      1              1

1              Mar      0              1

1              Apr       2              2

2              Jan       0              0

2              Feb       1              1

2              Mar       2              2


Any coding suggestions please?


Thanks in advance



Super User
Posts: 5,257

Re: getting max and min value by group

Not sure if you request matches your subject...

But, use RETAIN within a data step.

Data never sleeps
Super User
Posts: 17,868

Re: getting max and min value by group

There is no max or min that I see, only keeping the previous value. Can you clarify your requirements? 


It may be as a simple as:


if first.account then max_value =arrears;

else max_value = max(max_value, arrears);

Occasional Contributor
Posts: 10

Re: getting max and min value by group

Apologies for the confusion.


What I require is  the max arrears value by account and date.





Super User
Super User
Posts: 7,407

Re: getting max and min value by group

Post test data in the form of a datastep.


Just a guess:

data want;
  set have;
  retain max_arrears;
  by account;
  if first.account then max_arrears=0;
  if arrears > max_arrears then max_arrears=arrears;
Ask a Question
Discussion stats
  • 4 replies
  • 4 in conversation