how to find the maximum value across dynamically change observations in sas?

so i have the following dataset with three variables: account, balance, and time.

```account balance time
1       110     01/2006
1       111     02/2006
1       88      03/2006
1       61      04/2006
1       1203    05/2006
2       112     01/2006
2       111     02/2006
2       665     03/2006
2       61      04/2006
2       1243    05/2006
3       110     01/2006
3       111     02/2006
3       88      03/2006
3       61      04/2006
3       1203    05/2006
```

each account has more records. so the starting time might be before what I wrote and the ending time might be after what i wrote.

so my question is:

I am trying to find the maximum balance for each account in prievious 12 monthes. For example, for account 3 on 05/2006, i am trying to find max(account 3 balance at 04/2006, account 3 balance at 03/2006, account 3 balance at 03/2006,............., account 3 balance at 04/2006).

what is in your mind? what i did is to use lag function with array. however, it is NOT so efficient. because i will be in trouble if i need previous 120 months.

Thank you.

Best

Xintong

Try this:

```PROC SQL;
create table max_balance as
select *
from your_table
group by account
having balance=max(balance)
;
QUIT;
```

```options mprint;
%macro lag;
data temp (drop=count i);
set balance;
by acct time;
array x(*) lag_bal1 - lag_bal3;
%do i=1 %to 3;
lag_bal&i=lag&i.(balance);
%end;
if first.acct then count=1;
do i=count to dim(x);
call missing(x(i));
end;
count+1;
max_ball=max(balance, of lag_bal1-lag_bal3);
%mend;

%lag;
```