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
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
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
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.