Compute stock returns from prices

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Compute stock returns from prices

Dear All,

I would like to compute stock returns from prices. I have the following dataset:

data stocks;

   input key V1 V2;

datalines;

1 0.1   10

1 0.2   15

2 0.3   25

2 0.4   30

2 0.5   33

;

where "key" is the company id and "V2" is the share price. I would like to obtain the variable V3 = V2(i)/V2(i-1) -1, but only for observations with the same value of the variable key. I would like to have a blank cell when either the V2(i-1) observation is missing or key(i) is different from key(i-1).

keyV1V2V3
10.110  .
10.2 150.5 = (15/10)-1
20.325
20.4 300.2 = (30/25)-1
20.5330.1 = (33/30)-1

My attempt involves the procedure expand. First, I was trying to obtain the variables key and V2 shifted backword by 1 (i.e., key_lead and V2_lead). Then, I was using a conditional statement to check whether two consecutive observations have the same value of the variable "key" and computing the variable V3 as V2/V2_lead -1. However, SAS returns me an error.


proc expand data=stocks out=stocks_lead method = none; 

  convert key = key_lead  / transformout=(lead 1);

  convert V2 = V2_lead  / transformout=(lead 1);

  if key_lead = key

  then V3 = V2/V2_lead - 1;

  else V3 = .;

run;

Any help would be highly appreciated.


Accepted Solutions
Solution
‎08-12-2014 02:09 PM
Trusted Advisor
Posts: 1,228

Re: Compute stock returns from prices

data stocks;
input key V1 V2;
datalines;
1 0.1   10
1 0.2   15
2 0.3   25
2 0.4   30
2 0.5   33
;

data want;
set stocks;
by key v1 v2 notsorted;
v3=v2/lag(v2)-1;
if first.key then v3=.;
run;

View solution in original post


All Replies
Contributor
Posts: 45

Re: Compute stock returns from prices

Hi Mark,

You can try below mentioned code....

data stocks;                                     

   input key V1 V2;                              

datalines;                                       

1 0.1   10                                       

1 0.2   15                                       

2 0.3   25                                       

2 0.4   30                                       

2 0.5   33                                       

;                                                

                                                 

data want(drop=prev_key prev_v2);                

set stocks;                                     

if prev_key = key then v3 = (v2/prev_v2) - 1;   

prev_key = key;                                 

prev_v2 = v2;                                   

retain prev_key prev_v2;                       

Solution
‎08-12-2014 02:09 PM
Trusted Advisor
Posts: 1,228

Re: Compute stock returns from prices

data stocks;
input key V1 V2;
datalines;
1 0.1   10
1 0.2   15
2 0.3   25
2 0.4   30
2 0.5   33
;

data want;
set stocks;
by key v1 v2 notsorted;
v3=v2/lag(v2)-1;
if first.key then v3=.;
run;

Contributor
Posts: 35

Re: Compute stock returns from prices

Thank you guys!! Both solutions work.

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 434 views
  • 3 likes
  • 3 in conversation