Calcite | Level 5

## 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).

 key V1 V2 V3 1 0.1 10  . 1 0.2 15 0.5 = (15/10)-1 2 0.3 25 2 0.4 30 0.2 = (30/25)-1 2 0.5 33 0.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;

then V3 = V2/V2_lead - 1;

else V3 = .;

run;

Any help would be highly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Ammonite | Level 13

## 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;

3 REPLIES 3
Fluorite | Level 6

## 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;

Ammonite | Level 13

## 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;

Calcite | Level 5

## Re: Compute stock returns from prices

Thank you guys!! Both solutions work.

Discussion stats
• 3 replies
• 3327 views
• 4 likes
• 3 in conversation