calculating rate of return for all variables

[ilikesas]

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!

[Ksharp · Accepted solution]

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;

[Reeza]

Consider transposing your data from wide to long.