Hey Guys,
I am trying to figure out how to do a mean of the last 6 months of balances. I have a table that I keep on appending to every new end of month. E.g. Bal01012016, Bal 02012016, Bal 03012016, etc..
I have an array as follows:
array T1Table{*} T1:;
T1Last=T1Table[hbound(T1Table)];
Which works fine, I am able to get the Hbound and Lbound values, however, I want to be able to figure out how to get the mean from the last 6 months for balances, so basically get the last T1 Value and do the mean calculation against that one and the previous 5 T1 values..
Any idea on how I can achieve this? My array usage is limited so I don't recall.
Thanks in advance for the help!
Jose S
If you are sure that the variables are defined in order, here would be an alternate way to get your last one instead of using hbound:
n_elements = dim(T1Table);
Then you can refer to any array element:
T1_Last = T1Table{n_elements};
T1_Back_1mo = T1Table{n_elements-1};
T1_Back_2mo = T1Table{n_elements-2};
But for this to give you the right pieces to work with, you have to be certain that the elements in the array are in order.
If you have any control of your data structure change it to a long format. Keeping data in a wide format isn't efficient for so many reasons. This becomes trivial if you switch to a long format.
Otherwise you can use some calculations to determine what your field values should be based on your current date and use VVALUE/VVALUEX to retrieve the correct values and average them.
Are your T1 values separate months within one record or are you looking across records?
How do you define "last 6 months" in terms of the data. There is no indication in your description what may be needed.
@ballardw wrote:
Are your T1 values separate months within one record or are you looking across records?
How do you define "last 6 months" in terms of the data. There is no indication in your description what may be needed.
Yes sorry about that, The data is sequentially ordered by date. E.G. T1-01012016, T1-02012016, T1-03012016
I was defining the last 6 months as Hbound - 6 months basically.
If you are sure that the variables are defined in order, here would be an alternate way to get your last one instead of using hbound:
n_elements = dim(T1Table);
Then you can refer to any array element:
T1_Last = T1Table{n_elements};
T1_Back_1mo = T1Table{n_elements-1};
T1_Back_2mo = T1Table{n_elements-2};
But for this to give you the right pieces to work with, you have to be certain that the elements in the array are in order.
Thanks! This worked!!
I then did a mean(T1Table{n_elements},......) and it did the trick!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.