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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.