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

Answers


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;

Need Your Help

Java - Differences in AWT from 1.4 to 1.5 ( working on Unix and Windows )

java tomcat awt headless

This is with reference to my question: java.awt.HeadlessException Applets not displayed.

Asynchronous programming in ADO.Net

c# asp.net .net wcf asynchronous

The following link http://msdn.microsoft.com/en-us/library/hh211418(v=vs.110).aspx explains the new asynchronous programming features with the 4.5 .Net framework, which for the most part is fairly