topic Moving average with proc expand excluding the current observation in SAS Procedures

Moving average with proc expand excluding the current observation

Bright — Sat, 17 Apr 2021 05:05:58 GMT

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

Re: Moving average with proc expand excluding the current observation

sbxkoenk — Sat, 17 Apr 2021 07:37:37 GMT

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

Re: Moving average with proc expand excluding the current observation

Ksharp — Sat, 17 Apr 2021 10:38:09 GMT

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;