topic Re: ARRAY: Avg of Last X Number of Months in SAS Programming

ARRAY: Avg of Last X Number of Months

Josers18 — Wed, 07 Sep 2016 20:46:24 GMT

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

Re: ARRAY: Avg of Last X Number of Months

Reeza — Wed, 07 Sep 2016 20:58:31 GMT

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.

Re: ARRAY: Avg of Last X Number of Months

ballardw — Wed, 07 Sep 2016 21:00:46 GMT

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.

Re: ARRAY: Avg of Last X Number of Months

Astounding — Wed, 07 Sep 2016 21:06:40 GMT

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.

Re: ARRAY: Avg of Last X Number of Months

Josers18 — Wed, 07 Sep 2016 21:13:08 GMT

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

Re: ARRAY: Avg of Last X Number of Months

Josers18 — Wed, 07 Sep 2016 21:25:52 GMT

Thanks! This worked!!

I then did a mean(T1Table{n_elements},......) and it did the trick!