Calculating moving averages and price changes of past and future intervals

brophymj — Thu, 08 Feb 2018 12:35:05 GMT

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

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

brophymj — Thu, 08 Feb 2018 13:49:31 GMT

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;

topic Calculating moving averages and price changes of past and future intervals in SAS Programming

Calculating moving averages and price changes of past and future intervals

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