DATA Step, Macro, Functions and more

Calculating moving averages and price changes of past and future intervals

Accepted Solution Solved
Reply
Super Contributor
Posts: 261
Accepted Solution

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 


Accepted Solutions
Solution
‎02-08-2018 08:49 AM
Super Contributor
Posts: 261

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;

View solution in original post


All Replies
Solution
‎02-08-2018 08:49 AM
Super Contributor
Posts: 261

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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