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

[nismail1976]

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

[nismail1976]

let me know if I need to provide more clarifications?

[BMiller]

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;

[BMiller]

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.

[nismail1976]

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!

[nismail1976]

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

[BMiller]

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

Hang on a sec, version 2......

[nismail1976]

Thank you!

[BMiller · Accepted solution]

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.

[nismail1976]

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

[BMiller]

No prob 🙂