Using SAS EG 7.1 and I have a table structured as such.
ID Acct Current Cat1 Cat2 Cat3 Cat4 KB OC Date
1 xxxxxx1 1234.34 0 0 0 0 0 0 31Aug2013
2 xxxxxx1 0 1234.34 0 0 0 0 0 30Sep2013
3 xxxxxx1 0 0 1234.34 0 0 0 0 31Oct2013
4 xxxxxx1 0 0 0 0 0 0 0 30Nov2013
5 xxxxxx1 0 0 0 0 0 0 0 31Dec2013
6 xxxxxx1 0 0 0 0 0 0 0 31Jan2014
7 xxxxxx1 0 0 0 0 0 0 0 28Feb2014
8 xxxxxx1 0 0 0 0 0 0 0 31Mar2014
9 xxxxxx1 0 0 0 0 0 0 0 30Apr2014
10 xxxxxx1 0 0 0 0 0 0 0 30May2014
11 xxxxxx2 0 12,302.23 0 0 0 0 0 31Aug2013
12 xxxxxx2 0 0 12,302.23 0 0 0 0 30Sep2013
13 xxxxxx2 0 0 0 0 0 0 0 31Oct2013
14 xxxxxx2 0 0 0 0 0 0 0 30Nov2013
15 xxxxxx2 0 0 0 0 0 0 0 31Dec2013
16 xxxxxx2 0 0 0 0 0 0 0 31Jan2014
17 xxxxxx3 9082.82 0 0 0 0 0 0 31Aug2013
18 xxxxxx3 8934.21 0 0 0 0 0 0 30Sep2013
19 xxxxxx3 8722.02 0 0 0 0 0 0 31Oct2013
20 xxxxxx3 8404.19 0 0 0 0 0 0 30Nov2013
21 xxxxxx3 8154.49 0 0 0 0 0 0 31Dec2013
22 xxxxxx3 7884.58 0 0 0 0 0 0 31Jan2014
23 xxxxxx3 7363.71 0 0 0 0 0 0 28Feb2014
....
22823484 5329485 1234.34 0 0 0 0 0 0 30Jun2014
I am trying to calculate the rates of movement from the Current column to Cat1 column in September 2013. For the September rate, it would be the (total sum of Cat1 column at 31Sep2013 for those accounts that had non-zero Current values at 31Aug2013 and had non-zero Cat1 values at 30Sep2013 divided by the total sum of Current column at 31Aug2013 for all accounts). Please advise on the best way to approach this and thank you in advance!
