CODEWITHSUNDEEP
sas
rosa
rosa

Reputation: 25

Calculate a return for a period of time using SAS

My data is a set of columns as follows:

gvkey   date        div    price
1166    19970429    0   25.6
1166    19970502    0   26
1166    19970505    0   25.9
1166    19970506    0   22.1
1166    19970507    0   19
1166    19970509    0   20.4
1166    19970512    0   21.4
1166    19970513    0   21.7
1166    19970514    0   21.7
1166    19970515    0   21.1
1166    19970516    0   21.5
1166    19970520    0   21
1166    19970521    0   21.8
1166    19970522    0   22.2
1166    19970523    0   22.7
1166    19970526    0   23.9
1166    19970527    0   24
1166    19970528    0   24.2
1166    19970529    0   24.3
1166    19970530    0   23.7

In excel, I was able to calculate the return for the month of May just by adding a column that calculated the daily rtn ((price + div)/lag price)). Then I add a column of one plus rtn which multiplies today's daily rtn by the prior row one plus value. Of course, for the first day of the period, the one plus rtn value = daily rtn.

In excel I calculated 0.925781 as the one plus rtn for May 30th and -0.07422 as the rtn for that month. How can I make SAS do this for me? Thanks for your help!

Upvotes: 1

Views: 810

Answers (1)

DomPazz
DomPazz

Reputation: 12465

Try this:

data want;
set have;
format rtn crtn percent12.4;
retain crtn 1;
rtn = (price + div)/lag(price) - 1;
crtn = (1 + crtn) * (1 + sum(rtn,0)) - 1;
run;

This calculates return rtn as you describe.

We create a cumulative return crtn with a starting value of 1. RETAIN tells SAS to keep the value of this variable between rows. So we then update it on each row.

sum(rtn,0) = rtn + 0. On the first row, RTN will be null, but the SUM() function will ignore that null and return 0.

EDIT: Ask in comments about having the cumulative return reset each year. This assumes a variable fyear is on the data set and the data continue to be sorted in the correct order.

data want;
set have;

by fyear;

format rtn crtn percent12.4;
retain crtn 1;

if first.fyear then do; /*Check for the start of a new BY group*/
    crtn = 1;
end;

rtn = (price + div)/lag(price) - 1;
crtn = (1 + crtn) * (1 + sum(rtn,0)) - 1;
run;

Upvotes: 0

Related Questions