Dear All,
I have a dataset with company name, Date, and stock prices. I want to create a new variable- stock return volatility. To create this new variable, I first created stock returns calculated as (Stock price in year t minus stock return in year t-1)/ stock price in year t-1. For stock return volatility, I want to take standard deviation of monthly stock return for the 36 month period ending in the last month of the fiscal year.
The program I know to get standard deviation is as follows:
Stock_Vol = STD(lag1(stock return), lag2(stock return), lag3(stock return)........lag36(stock return);
Does anyone know any other code to get standard deviation? Also if a company has less than 36 months of stock return information, I want to delete the company. Can someone please tell me how I can do so?
The subset of the dataset looks as follows:
TICKER | date | price | returns |
IBM | 20010131 | 112 | . |
IBM | 20010228 | 99.9 | -0.10804 |
IBM | 20010330 | 96.18 | -0.03724 |
IBM | 20010430 | 115.14 | 0.19713 |
IBM | 20010531 | 111.8 | -0.02901 |
IBM | 20010629 | 113 | 0.010733 |
IBM | 20010731 | 105.21 | -0.06894 |
IBM | 20010831 | 99.95 | -0.05 |
IBM | 20010928 | 91.72 | -0.08234 |
IBM | 20011031 | 108.07 | 0.17826 |
IBM | 20011130 | 115.59 | 0.069585 |
IBM | 20011231 | 120.96 | 0.046457 |
IBM | 20020131 | 107.89 | -0.10805 |
IBM | 20020228 | 98.12 | -0.09056 |
IBM | 20020328 | 104 | 0.059927 |
IBM | 20020430 | 83.76 | -0.19462 |
IBM | 20020531 | 80.45 | -0.03952 |
IBM | 20020628 | 72 | -0.10503 |
IBM | 20020731 | 70.4 | -0.02222 |
IBM | 20020830 | 75.38 | 0.070739 |
IBM | 20020930 | 58.31 | -0.22645 |
IBM | 20021031 | 78.94 | 0.353799 |
IBM | 20021129 | 86.92 | 0.101089 |
IBM | 20021231 | 77.5 | -0.10838 |
IBM | 20030131 | 105.21 | -0.06894 |
IBM | 20030228 | 99.95 | -0.05 |
IBM | 20030328 | 91.72 | -0.08234 |
IBM | 20030430 | 108.07 | 0.17826 |
IBM | 20030531 | 115.59 | 0.069585 |
IBM | 20030628 | 120.96 | 0.046457 |
IBM | 20030731 | 107.89 | -0.10805 |
IBM | 20030830 | 98.12 | -0.09056 |
IBM | 20030930 | 104 | 0.059927 |
IBM | 20031031 | 83.76 | -0.19462 |
IBM | 20031129 | 80.45 | -0.03952 |
IBM | 20031231 | 72 | -0.10503 |
How can I calculate stock volatility of IBM for 2003?
Thank you for the time.
I think you might find it quite a bit easier using something like:
data have;
informat date date9.;
format date date9.;
input company $ date stock_price;
cards;
IBM 31DEC2011 100
IBM 31JAN2012 200
IBM 29FEB2012 300
IBM 31MAR2012 400
HP 29FEB2012 300
HP 31MAR2012 400
MBI 31JAN2012 400
MBI 29FEB2012 600
MBI 31MAR2012 800
;
proc summary data=have (where=('31JAN2012'D LE DATE LE '31MAR2012'D)) nway;
var stock_price;
class company;
output out=want (drop=_: where=(n eq 3)) n=n stddev=sd;
run;
I think you might find it quite a bit easier using something like:
data have;
informat date date9.;
format date date9.;
input company $ date stock_price;
cards;
IBM 31DEC2011 100
IBM 31JAN2012 200
IBM 29FEB2012 300
IBM 31MAR2012 400
HP 29FEB2012 300
HP 31MAR2012 400
MBI 31JAN2012 400
MBI 29FEB2012 600
MBI 31MAR2012 800
;
proc summary data=have (where=('31JAN2012'D LE DATE LE '31MAR2012'D)) nway;
var stock_price;
class company;
output out=want (drop=_: where=(n eq 3)) n=n stddev=sd;
run;
Thanks Arthur for the code.
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.