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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.