DATA Step, Macro, Functions and more

how to add a column of max, min, average rolling back 12 months using proc SQL?

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

how to add a column of max, min, average rolling back 12 months using proc SQL?

Hello everyone,

I have 100 accounts and for each account  i have 3 years worth of monthly data. here is an example:

         
 

Account Number

Date

Account Balance

 
 

321469

1/1/2010

52469

 
 

321469

2/1/2010

52708

 
 

321469

3/1/2010

52947

 
 

321469

4/1/2010

53186

 
 

321469

5/1/2010

53425

 
 

321469

6/1/2010

53664

 
 

321469

7/1/2010

53903

 
 

321469

8/1/2010

54142

 
 

321469

9/1/2010

54381

 
 

321469

10/1/2010

54620

 
 

321469

11/1/2010

54859

 
 

321469

12/1/2010

55098

 
 

321469

1/1/2011

55337

 
 

321469

2/1/2011

55576

 
 

321469

3/1/2011

55815

 
 

321469

4/1/2011

56054

 
 

321469

5/1/2011

56293

 
 

321469

6/1/2011

56532

 
 

321469

7/1/2011

56771

 
 

321469

8/1/2011

57010

 
 

321469

9/1/2011

57249

 
 

321469

10/1/2011

57488

 
 

321469

11/1/2011

57727

 
 

321469

12/1/2011

57966

 
 

321469

1/1/2012

58205

 
 

321469

2/1/2012

58444

 
 

321469

3/1/2012

58683

 
 

321469

4/1/2012

58922

 
 

321469

5/1/2012

59161

 
 

321469

6/1/2012

59400

 
 

321469

7/1/2012

59639

 
 

321469

8/1/2012

59878

 
 

321469

9/1/2012

60117

 
 

321469

10/1/2012

60356

 
 

321469

11/1/2012

60595

 
 

321469

12/1/2012

60834

 
 

7896666

1/1/2011

1115

 
 

7896666

2/1/2011

1215

 
 

7896666

3/1/2011

1315

 
 

7896666

4/1/2011

1415

 
 

7896666

5/1/2011

1515

 
 

7896666

6/1/2011

1615

 
 

7896666

7/1/2011

1715

 
 

7896666

8/1/2011

1815

 
 

7896666

9/1/2011

1915

 
 

7896666

10/1/2011

2015

 
 

7896666

11/1/2011

2115

 
 

7896666

12/1/2011

2215

 
 

7896666

1/1/2012

2315

 
 

7896666

2/1/2012

2415

 
 

7896666

3/1/2012

2515

 
 

7896666

4/1/2012

2615

 
 

7896666

5/1/2012

2715

 
 

7896666

6/1/2012

2815

 
 

7896666

7/1/2012

2915

 
 

7896666

8/1/2012

3015

 
 

7896666

9/1/2012

3115

 
 

7896666

10/1/2012

3215

 
 

7896666

11/1/2012

3315

 
 

7896666

12/1/2012

3415

 
 

7896666

1/1/2013

3515

 
 

7896666

2/1/2013

3615

 
 

7896666

3/1/2013

3715

 
 

7896666

4/1/2013

3815

 
 

7896666

5/1/2013

3915

 
 

7896666

6/1/2013

4015

 
 

7896666

7/1/2013

4115

 
 

7896666

8/1/2013

4215

 
 

7896666

9/1/2013

4315

 
 

7896666

10/1/2013

4415

 
 

7896666

11/1/2013

4515

 
 

7896666

12/1/2013

4615

 
         

 

Now what I am trying to do is to get min, max, and average balance at every month rolling back 12 months using proc SQL

In other words, if account number is 321469 and date is 12/1/2011 then I need minimum balance , maximum balance, and Average balance between these two dates 1/1/2011 and 12/1/2011 at  this month 12/1/2011 and so on...

Thanks in advance


Accepted Solutions
Solution
‎01-28-2016 06:03 PM
Occasional Contributor
Posts: 12

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976

Okay. This should be better

 

 

proc sql;

create table output_rolling as

     Select a.account_number

      ,        A.bal_date

      ,         Max(b.balance) as max_bal

      ,         Min(b.balance) as min_bal

      ,         Mean(b.balance) as avg_bal

    From HAVE as a

    Left join HAVE as b

    On a.account_number = b.account_number

     And intnx('month',a.bal_date,-11,'s') <= b.bal_date <= a.bal_date

    ;

quit;

 

so you join the data to itself and select all the balance between the current (12th) month and the 11 previous, for that account.

 

note.... For the early period, the summary values will be based on an incomplete sample (1 month, then 2, 3,4, etc) until you have a full 12 months history for that account. It is easy to set up a simple count and reject any output that is based on less than 12 balances. Ask if you need help with that.

View solution in original post


All Replies
Contributor
Posts: 28

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976

let me know if I need to provide more clarifications?

Occasional Contributor
Posts: 12

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976

Are you looking for the summary data to be "month to date"? I.e. ..... 1/1/12 to 10/1/12 / 1/1/12 to 11/1/12 / 1/1/12 to 12/1/12......

or grouping each calendar month into a single category?

 

if it's the latter then:

 

ProC SQL;

select account_number

  ,        bal_Month format = monyy5. 

  ,        Max(balance) as max_bal

  ,        Min(balance) as min_bal

 /*. Add additional summary statements here */

from (

         Select account_number

         ,          Bal_date - day(bal_date) + 1 as bal_month /* converts every date into the 1st of that month, or use INTNX */

         ,          Balance

        From HAVE

         )

group by account_number , bal_month

;

quit;

 

Occasional Contributor
Posts: 12

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Just noticed I missed the factor you want to keep a rolling 12mths from the full data.

This can be done as a simple "where" in a previous step or as part of the above query using a WHERE above the GROUP statement combined with a sub query.

If you need that element, I can add it in.
Contributor
Posts: 28

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

thank you for your respone!

what I need is min(balance),max(balance) and avereage (balance) at each date  but looking back 12 months ago. I have no clue how to do this

Thanks for your help!

Contributor
Posts: 28

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976

so min(balance), max(Balance),Max(balance) is year to date

Occasional Contributor
Posts: 12

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976

Just realised we are using different date formats (I'm using the English DD/MM/YY) hence my confusion. Smiley Happy

Hang on a sec, version 2......
Contributor
Posts: 28

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Thank you!

Solution
‎01-28-2016 06:03 PM
Occasional Contributor
Posts: 12

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976

Okay. This should be better

 

 

proc sql;

create table output_rolling as

     Select a.account_number

      ,        A.bal_date

      ,         Max(b.balance) as max_bal

      ,         Min(b.balance) as min_bal

      ,         Mean(b.balance) as avg_bal

    From HAVE as a

    Left join HAVE as b

    On a.account_number = b.account_number

     And intnx('month',a.bal_date,-11,'s') <= b.bal_date <= a.bal_date

    ;

quit;

 

so you join the data to itself and select all the balance between the current (12th) month and the 11 previous, for that account.

 

note.... For the early period, the summary values will be based on an incomplete sample (1 month, then 2, 3,4, etc) until you have a full 12 months history for that account. It is easy to set up a simple count and reject any output that is based on less than 12 balances. Ask if you need help with that.

Contributor
Posts: 28

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Thank you so much and I really appreciate your help, that resolved the issue

Occasional Contributor
Posts: 12

Re: how to add a column of max, min, average rolling back 12 months using proc SQL?

Posted in reply to nismail1976
No prob Smiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 734 views
  • 1 like
  • 2 in conversation