Moving average with proc expand excluding the current observation

[Bright]

Hi,

I am trying to use proc expand to calculate moving average of for example 3 last observations excluding the current observation. Suppose the data is like this:

ID      Price    

1         10        

2          0

3         20

4         50

Using this code

proc expand data=have out=want method=none;
convert price=price_avg / TRANSFORMOUT=(movave 3);
run;

I get 

ID      Price    Price_avg

1         10        10

2          0          5

3         20        10

4         70         30

Now, consider observation 4, I want the moving average does not consider the current observation. So the Price-avg should be (20+0+10)/3=10. Is there any way to do that directly in the proc expand?

Thanks

[sbxkoenk · Accepted solution]

Hello,

You should specify a window with weights in parentheses for the MOVAVE transformation operation. And you give a weight of 0 to the current observation.

Like this:

data have;
input ID Price;
cards;
1         10
2          0
3         20
4         50
;
run;
proc expand data=have out=want method=none;
convert price=price_avg / TRANSFORMOUT=(movave (0.33333 0.33333 0.33333 0));
run;

(If you don't like this approach, you will have to use PROC TIMEDATA or the datastep.)

Cheers,

Koen

[Ksharp]

data have;
input ID Price;
cards;
1         10
2          0
3         20
4         50
;

data want;
 set have;
 array x{0:2} _temporary_;
 want=mean(of x{*});
 x{mod(_n_,3)}=price;
run;