How to calculate moving average by taking current calculated moving average observation?

[himsharma]

Hi Everyone, 
I am new to sas and got stuck to a problem.

Say,

week value 4_week_moving_ave

1      10       

2      5          

3     20         

4     30         

5     50         

6    No Value  26.25

7.   No Value  31.56

So i want to calculate the moving average by group of variable.

Here in week 6 which i need to forecast so my moving average will be (50+30+20+5)/4 = 26.25

In Week 7, moving average will be (26.25+50+30+20)/4= 31.56.

Is there a way to as shown in example?

Thanks in advance

[s_lassen]

Please present your sample data as data step code, like this:

data have;                                                                                                                              
  input week value;                                                                                                                     
cards;                                                                                                                                  
1 10                                                                                                                                    
2 5                                                                                                                                     
3 20                                                                                                                                    
4 30                                                                                                                                    
5 50                                                                                                                                    
6 .                                                                                                                                     
7 .                                                                                                                                     
;run; 

The easiest way is probably to have a temporary array that contain the previous 4 values:

data want;                                                                                                                              
  set have;                                                                                                                             
  array values (0:3) 8 _temporary_;                                                                                                     
  if missing(value) then                                                                                                                
    value=mean(of values(*));                                                                                                           
  values(mod(_N_,4))=value;                                                                                                             
run;

I indexed the temporary array as (0:3), because MOD(_N_,4) returns values 0 to 3, not 1 to 4.

[himsharma]

Hi Thanks for solution, with lil changes it works for me.
Just one question, can this solution can be used using by variables/statement like by category week?

[s_lassen]

I asssume that if you use a BY statement, you do not want the data from the previous BY group to be part of the rolling average for the next by group. The solution is quite simple: set the temporary array missing when you get a new BY value, e.g.:

data want;                                                                                                                              
  set have;       
  by category;                                                                                                                      
  array values (0:3) 8 _temporary_;                                                                                                     
  if first.category then call missing(of values(*));
  if missing(value) then                                                                                                                
    value=mean(of values(*));                                                                                                           
  values(mod(_N_,4))=value;                                                                                                             
run;

[ballardw]

You should provide variables and rules to identify "the moving average by group of variable."

I can write an example that does the moving average ONLY for week 6 and week 7. You haven't given us any idea what might be done with week 8 or 23. Examples are fine but they are used to demonstrate how a rule is resolved.

For example, it might be from your data that you only need this calculated for the "no value" records.

On this forum it is best practice to provide the data you currently have in the form of a data step such as:

data have;
input week value ;
datalines;
1  10       
2  5          
3  20         
4  30         
5  50         
6  .  
7  .
;

The dot when read will be a missing value (SAS for "no value"). The code is pasted into a text box opened on the forum with the </> icon that appears above the main message window. Text pasted into the main message window gets reformatted by the forum software such as reducing white space and inserting html tags. That means that code pasted into the main window may not execute properly when copied and pasted into a SAS editor for execution.

You should, when feasible, provide the results in a separate data step such as below. That way we can tell what you already have and the actual results per variable. Note that the name of the variable I used is Moving_average. While it is now possible to get a name like "4_week_moving_ave" the headaches to have a 4 start the name are not worth it my opinion. If you want nicer text use a LABEL to provide that information about the Moving_average variable.

data want;
   input week value moving_average;
datalines;
1  10  .     
2  5   .       
3  20  .       
4  30  .       
5  50  .       
6  .   26.25
7  .   31.56
;

Now try to provide rules based on values of the variables to tell when the moving average is calculated and how to know which values are used. If you have more than 7 records in your actual data then you may need to give us an idea just how many may be involved. Also, if there are groups of records involved, such as location, branch office, account number where this needs to be done

[himsharma]

Hi @ballardw 

I will Reframe my problem statement as per your suggestions.

Here i have weekly data.

data have;
input week value ;
datalines;
1  10       
2  5          
3           
4  30         
5  50         
;

 Here i have data till week 5 and from next week i.e. week 6 i need to forecast by calculating four weeks moving average.

data want;
   input week value moving_average;
datalines;
1  10  .     
2  5   .       
3  .   .       
4  30  .       
5  50  .       
6  .   28.33
7  .   36.11
8  .   34.86

  SO here is logic: 

For Week 6: (50+30+5)/3 = 28.33
For Week 7: (28.33+50+30)/3 = 36.11
For Week 8: (36.11+28.33+50+30)/4 = 34.86

Hope this time i have made clear my problem statement.

Thanks

[ghosh]

data have;                                                                                                                              
  input week value;                                                                                                                     
cards;                                                                                                                                  
1 10                                                                                                                                    
2 5                                                                                                                                     
3 20                                                                                                                                    
4 30                                                                                                                                    
5 50                                                                                                                                    
6 .                                                                                                                                     
7 .                                                                                                                                     
;
proc expand data=have out=want method=none;
    id week;
    convert value = values_ma4 / transout=(movave 4);
run;
proc print;
var week value values_ma4;
run;