topic Re: Compute stock returns from prices in SAS Programming

Compute stock returns from prices

mark_ph — Tue, 12 Aug 2014 17:46:00 GMT

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.

Re: Compute stock returns from prices

RaviKommuri — Tue, 12 Aug 2014 17:58:30 GMT

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;

Re: Compute stock returns from prices

stat_sas — Tue, 12 Aug 2014 18:09:56 GMT

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;

Re: Compute stock returns from prices

mark_ph — Wed, 13 Aug 2014 13:00:29 GMT

Thank you guys!! Both solutions work.