BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Josers18
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Reeza
Super User

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. 

ballardw
Super User

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.

 

 

 

 

Josers18
Obsidian | Level 7
@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.

 

 


 

Astounding
PROC Star

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.

Josers18
Obsidian | Level 7

Thanks! This worked!!

 

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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