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

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

1 ACCEPTED SOLUTION

Accepted Solutions
sbxkoenk
SAS Super FREQ

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

View solution in original post

2 REPLIES 2
sbxkoenk
SAS Super FREQ

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
Super User
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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 889 views
  • 1 like
  • 3 in conversation