- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
let me know if I need to provide more clarifications?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
so min(balance), max(Balance),Max(balance) is year to date
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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......
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much and I really appreciate your help, that resolved the issue
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content