使用SAS计算一段时间的回报(Calculate a return for a period of time using SAS)

我的数据是一组列，如下所示：

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

在Excel中，我可以通过添加一个计算每日rtn（（价格+ div）/滞后价格））的列来计算5月份的回报。 然后我添加一列加上rtn，它将今天的每日rtn乘以前一行加上值。 当然，对于该期间的第一天，一加rtn值=每日rtn。

在Excel中，我计算了0.925781作为5月30日的rtn和-0.07422作​​为该月的rtn。 我如何让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!

最满意答案

尝试这个：

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;

这会根据您的描述计算返回rtn 。

我们创建一个起始值为1的累积返回crtn告诉SAS在行之间保留这个变量的值。 所以我们在每一行都进行更新。

sum(rtn,0) = rtn + 0 。 在第一行，RTN将为空，但SUM()函数将忽略该空值并返回0。

编辑：在评论中询问有关每年累计回报重置的信息。 假设变量fyear位于数据集上，并且数据继续按正确的顺序排序。

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;

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;使用SAS计算一段时间的回报(Calculate a return for a period of time using SAS)

我的数据是一组列，如下所示：

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

在Excel中，我可以通过添加一个计算每日rtn（（价格+ div）/滞后价格））的列来计算5月份的回报。 然后我添加一列加上rtn，它将今天的每日rtn乘以前一行加上值。 当然，对于该期间的第一天，一加rtn值=每日rtn。

在Excel中，我计算了0.925781作为5月30日的rtn和-0.07422作​​为该月的rtn。 我如何让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!

最满意答案

尝试这个：

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;

这会根据您的描述计算返回rtn 。

我们创建一个起始值为1的累积返回crtn告诉SAS在行之间保留这个变量的值。 所以我们在每一行都进行更新。

sum(rtn,0) = rtn + 0 。 在第一行，RTN将为空，但SUM()函数将忽略该空值并返回0。

编辑：在评论中询问有关每年累计回报重置的信息。 假设变量fyear位于数据集上，并且数据继续按正确的顺序排序。

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;

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;