SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

calculating rate of return for all variables

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

calculating rate of return for all variables

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!


Accepted Solutions
Solution
‎06-04-2016 01:01 PM
Super User
Posts: 10,046

Re: calculating rate of return for all variables

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;

View solution in original post


All Replies
Solution
‎06-04-2016 01:01 PM
Super User
Posts: 10,046

Re: calculating rate of return for all variables

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;

Super User
Posts: 19,877

Re: calculating rate of return for all variables

Consider transposing your data from wide to long. 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 215 views
  • 1 like
  • 3 in conversation