Hi,
suppose I have stock price data of the follwing form (and I have many stocks but for simplicity here will include only 2):
date |
Stock1_price |
Stock2_price |
20/1/2015 |
20 |
6 |
19/1/2015 |
10 |
3 |
18/1/2015 |
5 |
1 |
What I would like to obtain is the stock returns for all of my stocks:
date |
Stock1_return |
Stock2_return |
20/1/2015 |
1 |
1 |
19/1/2015 |
1 |
1.5 |
18/1/2015 |
. |
. |
If I had only one (or a small number) stock I would just create a new colum "lag_price" and then calculate the return as (price-lag_price)/ lag_price, but in the case of many stocks its not practical...
Thank you!
It would be very easy for IML. Do you want IML code? OR you could use array. data have; input date : ddmmyy10. Stock1_price Stock2_price; format date ddmmyy10.; cards; 20/1/2015 20 6 19/1/2015 10 3 18/1/2015 5 1 ; run; proc sort data=have;by date;run; data want; set have; array x{*} return1-return2; array y{*} Stock1_price Stock2_price; do i=1 to dim(x); x{i}=dif(y{i})/lag(y{i}); end; run; proc sort data=want;by descending date;run;
It would be very easy for IML. Do you want IML code? OR you could use array. data have; input date : ddmmyy10. Stock1_price Stock2_price; format date ddmmyy10.; cards; 20/1/2015 20 6 19/1/2015 10 3 18/1/2015 5 1 ; run; proc sort data=have;by date;run; data want; set have; array x{*} return1-return2; array y{*} Stock1_price Stock2_price; do i=1 to dim(x); x{i}=dif(y{i})/lag(y{i}); end; run; proc sort data=want;by descending date;run;
Consider transposing your data from wide to long.
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.