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

so have following dataset 3 variables: account, balance, , 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. starting time might before wrote , ending time might after wrote.

so question is:

i trying find maximum balance each account in prievious 12 monthes. example, account 3 on 05/2006, trying find max(account 3 balance @ 04/2006, account 3 balance @ 03/2006, account 3 balance @ 03/2006,............., account 3 balance @ 04/2006).

what in mind? did use lag function array. however, not efficient. because in trouble if need previous 120 months.

thank you.

best

xintong

try this:

proc sql; create table max_balance select * your_table group account having balance=max(balance) ; quit;