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

## Calculating moving averages and price changes of past and future intervals

I've got a dataset with 100s of companies and historic prices. The data is sorted by company and date (descending order).

I want to calculate a couple of variables and I was hoping to get some guidance on the best functions to use:

Average price in the last 5/10/x periods (but I don't want to overlap with the previous company's data).

Price change in the last 5/10/x periods. Again, there won't be any data for this in the first 4 periods of data FOR EACH company

Future price change i.e. the price in 5 periods time/price (this period)

I've attached an extract from the data with Company A and Company B as an example and I've highlighted in grey the columns where I'm trying to calculate the above metrics.

I'm pretty sure I can use lag for the price change but not sure about the average or the future price. Also, not sure how I would go about starting the calculation for each new company i.e. Company A and Company B in the attached

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: Calculating moving averages and price changes of past and future intervals

I actually found a solution from another thread.

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

data want;
set stocks;
by stock;
retain pre1-pre12;
array pre(12);

if first.stock then
do;
call missing(of pre(*));
count=0;
end;
count+1;
index=mod(count, 12)+1;
pre(index)=open;

if count>=12 then
moving_average=mean(of pre(*));
drop count index pre1-pre12;
run;

proc sort data=want;
by stock date;
run;

data want1;
set want;
by stock;
change=ifn(first.stock,.,open/lag5(open)-1);
run;

proc sort data=want1;
by stock descending date;
run;

1 REPLY 1
Quartz | Level 8

## Re: Calculating moving averages and price changes of past and future intervals

I actually found a solution from another thread.

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

data want;
set stocks;
by stock;
retain pre1-pre12;
array pre(12);

if first.stock then
do;
call missing(of pre(*));
count=0;
end;
count+1;
index=mod(count, 12)+1;
pre(index)=open;

if count>=12 then
moving_average=mean(of pre(*));
drop count index pre1-pre12;
run;

proc sort data=want;
by stock date;
run;

data want1;
set want;
by stock;
change=ifn(first.stock,.,open/lag5(open)-1);
run;

proc sort data=want1;
by stock descending date;
run;

Discussion stats
• 1 reply
• 874 views
• 0 likes
• 1 in conversation