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

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

nismail1976 — Thu, 28 Jan 2016 20:44:53 GMT

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

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

nismail1976 — Thu, 28 Jan 2016 20:46:30 GMT

let me know if I need to provide more clarifications?

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

BMiller — Thu, 28 Jan 2016 21:35:13 GMT

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;

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

BMiller — Thu, 28 Jan 2016 21:39:41 GMT

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.

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

nismail1976 — Thu, 28 Jan 2016 21:54:55 GMT

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!

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

nismail1976 — Thu, 28 Jan 2016 22:08:00 GMT

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

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

BMiller — Thu, 28 Jan 2016 22:16:16 GMT

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......

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

nismail1976 — Thu, 28 Jan 2016 22:17:19 GMT

Thank you!

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

BMiller — Thu, 28 Jan 2016 22:35:21 GMT

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.

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

nismail1976 — Thu, 28 Jan 2016 23:02:46 GMT

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

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

BMiller — Thu, 28 Jan 2016 23:03:56 GMT

No prob 🙂