Compute stock returns from prices

[mark_ph]

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; 

  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.

[RaviKommuri]

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;                       

[stat_sas · Accepted solution]

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;

[mark_ph]

Thank you guys!! Both solutions work.