DATA Step, Macro, Functions and more

ARRAY: Avg of Last X Number of Months

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

ARRAY: Avg of Last X Number of Months

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


Accepted Solutions
Solution
‎09-07-2016 05:26 PM
Super User
Posts: 5,499

Re: ARRAY: Avg of Last X Number of Months

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.

View solution in original post


All Replies
Super User
Posts: 19,772

Re: ARRAY: Avg of Last X Number of Months

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. 

Super User
Posts: 11,343

Re: ARRAY: Avg of Last X Number of Months

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.

 

 

 

 

Occasional Contributor
Posts: 15

Re: ARRAY: Avg of Last X Number of Months

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.

 

 


 

Solution
‎09-07-2016 05:26 PM
Super User
Posts: 5,499

Re: ARRAY: Avg of Last X Number of Months

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.

Occasional Contributor
Posts: 15

Re: ARRAY: Avg of Last X Number of Months

Posted in reply to Astounding

Thanks! This worked!!

 

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 351 views
  • 1 like
  • 4 in conversation