Multiplying observations-values in row 1 by values in row 2 and so on.

[Akarsh91]

Dear experts,

I have the following information

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,1967
1,1967-11-28,1.026,,11,1967
1,1967-12-28,1.027,,12,1967
1,1968-01-28,1.01,,1,1968
1,1968-02-28,1.04,,2,1968
1,1968-03-28,1.001,,3,1968
1,1968-04-28,1.005,,4,1968
1,1968-05-28,1.02,,5,1968
1,1968-06-28,0.02,,6,1968
1,1968-07-28,0.06,,7,1968
1,1968-08-28,0.06,,8,1968
1,1968-09-28,0.07,,9,1968
1,1968-10-28,0.07,,10,1968
1,1968-11-28,0.08,,11,1968
1,1968-12-28,0.01,,12,1968
1,1969-01-28,0.01,,1,1969
1,1969-02-28,0.04,,2,1969
1,1969-03-28,0.001,,3,1969
1,1969-04-28,0.005,,4,1969
; 
run;

ID    Date                Returns      Delisting return        month         year

1   1967-10-28     1.025                                            10            1967

1  1967-11-28      1.026                                            11            1967

1   1967-12-28     1.027                                            12            1967

1    1968-01-28      1.01                                             1             1968

1    1968-02-28    1.04                                              2              1968

1    1968-03-28     1.001                                           3              1968

1   1968 -04-28     1.005   .                                       4              1968

1   1968-05-28     1.02                                              5              1968

1   1968-06-28      1.02                                             6               1968

1    1968-07-28     1.06                                              7              1968

1    1968-08-28      1.06                                             8             1968

1   1968-09-28       1.07                                             9            1968

1   1968-10-28       1.07                                            10            1968

1    1968-11-28     1.08                                             11            1968

1    1968-12-28     1.01                                             12            1968

1   1969-01-28     1.01                                               1            1969

1   1969-02-28      1.04                                              2            1969

1    1969-03-28     1.001                                            3            1969

1    1969-04-28     1.005                                            4             1969

I would like to conduct multiplication operations on rows. The data is a monthly return data. I would like to find yearly returns using monthly returns, so  I need to multiply monthly returns.  For instance starting from the beginning (when i buy this stock on 1967). I want to conduct the operations as follows:  1.025*1.026*1.027...........*1.07 that is multiply the returns from 1967-10-28 to 1968-10-28, to find the annual return for holding the stock for a given period.

Could  anyone please help me with the code to do this task in SAS? Please write if you need more information or clarification. All help is appreciated.

I am using SAS 9.4

Regards

Akarsh  

[Akarsh91]

 <code></code>

Dear experts, 

I have the following information. 

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,1967
1,1967-11-28,1.026,,11,1967
1,1967-12-28,1.027,,12,1967
1,1968-01-28,1.01,,1,1968
1,1968-02-28,1.04,,2,1968
1,1968-03-28,1.001,,3,1968
1,1968-04-28,1.005,,4,1968
1,1968-05-28,1.02,,5,1968
1,1968-06-28,1.02,,6,1968
1,1968-07-28,1.06,,7,1968
1,1968-08-28,1.06,,8,1968
1,1968-09-28,1.07,,9,1968
1,1968-10-28,1.07,,10,1968
1,1968-11-28,1.08,,11,1968
1,1968-12-28,1.01,,12,1968
1,1969-01-28,1.01,,1,1969
1,1969-02-28,1.04,,2,1969
1,1969-03-28,1.001,,3,1969
1,1969-04-28,1.005,,4,1969
; 
run;

ID    Date                Returns      Delisting return        month         year

1   1967-10-28     1.025                                            10            1967

1  1967-11-28      1.026                                            11            1967

1   1967-12-28     1.027                                            12            1967

1    1968-01-28      1.01                                             1             1968

1    1968-02-28    1.04                                              2              1968

1    1968-03-28     1.001                                           3              1968

1   1968 -04-28     1.005   .                                       4              1968

1   1968-05-28     1.02                                              5              1968

1   1968-06-28      1.02                                             6               1968

1    1968-07-28     1.06                                              7              1968

1    1968-08-28      1.06                                             8             1968

1   1968-09-28       1.07                                             9            1968

1   1968-10-28       1.07                                            10            1968

1    1968-11-28     1.08                                             11            1968

1    1968-12-28     1.01                                             12            1968

1   1969-01-28     1.01                                               1            1969

1   1969-02-28      1.04                                              2            1969

1    1969-03-28     1.001                                            3            1969

1    1969-04-28     1.005                                            4             1969

I would like to conduct multiplication operations on rows. The data is a monthly return data. I would like to find yearly returns using monthly returns, so  I need to multiply monthly returns.  For instance starting from the beginning (when i buy this stock on 1967). I want to conduct the operations as follows:  1.025*1.026*1.027...........*1.07 that is multiply the returns from 1967-10-28 to 1968-10-28, to find the annual return for holding the stock for a given period.

Could  anyone please help me with the code to do this task in SAS? Please write if you need more information or clarification. All help is appreciated.

I am using SAS 9.4

Regards

Akarsh  

[kiranv_]

something like this

proc sql;

create table want as

select id, date, returns, Delisting_return, month, year, sum(returns) as totalperyear

from have

group by year;

quit;

[PaigeMiller]

---

@kiranv_ wrote:

something like this

proc sql;

create table want as

select id, date, returns, Delisting_return, month, year, sum(returns) as totalperyear

from have

group by year;

quit;

---

The question was not to create a sum, it was to create a product of the returns and as far as I know, there is no PROD function in PROC SQL. (Is there a way to compute products of a column in PROC SQL?)

[kiranv_]

you are right @PaigeMiller. I did not read the question properly.

[kiranv_]

try this . Please be careful with format of your new col

proc sql;

create table want as

select year, returns, exp(sum(log(returns))) as newcol

from have

group by year;

quit;

[PaigeMiller · Accepted solution]

---

@kiranv_ wrote:

try this . Please be careful with format of your new col

proc sql;

create table want as

select year, returns, exp(sum(log(returns))) as newcol

from have

group by year;

quit;

---

Interesting, @kiranv_, in all my years of using PROC SQL, this never occurred to me as a way to get the product of a column.

[Akarsh91]

Thanks, it worked. 

[PaigeMiller]

Well @Akarsh91, that's nice that you have selected my comment as the answer, but my comment was in reply to a solution from @kiranv_, who provided the code that worked. Of course, the PROC EXPAND solution I provided earlier in the thread gives the same answer, and I suspect the other solutions in this thread also give the same answer.

[PaigeMiller]

I think the easiest way might be PROC EXPAND

UNTESTED CODE

proc expand data=have out=want method=none;
    by year;
    convert returns/transformout=(cuprod);
run;

[ballardw]

Since your data goes past more than one twelve month period you may need to provide some additional details about the whole process.

For instance what records are in the output? Do you need the monthly cumulative or only the "annual return"? Is something supposed to reset on an anniversary(Month=10 for your example) and do 2 or 3 or 4 annual returns or only the first?

You did not explicitly provide any ID other than 1 so one might assume that each ID is treated separately and likely to have a different first date and hence "annual return" period.

What output should occur if there are not at least 12 values? Nothing? return to date?

[Akarsh91]

In output, I would like to see the annual return, for the time period mentioned in my original post. My main goal is however to find the product of the rows. On the anniversary (month=10) the stock is supposed to be sold. As to the ID, it refers to the stock I am holding. ID 1 means that I am only holding 1 stock with ID 1.

[kiranv_]

if possible, can you show how your output should be

[Akarsh91]

In output i would like to see as follows: 

Assuming that I am holding my stock from 1967-10-28 to 1968-10-28

ID    Date                Returns      Delisting return        month         year      Ann_ret

1   1967-10-28     1.025                                            10            1967        1.527

1  1967-11-28      1.026                                            11            1967         1.527

1   1967-12-28     1.027                                            12            1967         1.527 

1    1968-01-28      1.01                                             1             1968         1.527

1    1968-02-28    1.04                                              2              1968          1.527

1    1968-03-28     1.001                                           3              1968          1.527

1   1968 -04-28     1.005   .                                       4              1968         1.527

1   1968-05-28     1.02                                              5              1968          1.527

1   1968-06-28      1.02                                             6               1968         1.527

1    1968-07-28     1.06                                              7              1968          1.527

1    1968-08-28      1.06                                             8             1968           1.527

1   1968-09-28       1.07                                             9            1968            1.527

1   1968-10-28       1.07                                            10            1968           1.527

Ann_ret is found by multiplying the monthly returns as 1.025*1.026*1.027*1.01*1.04*1.001*1.005*1.02*1.02*1.06*1.06*1.07*1.07 that is multiplying the values in each rows. Since i wouldnt be holding my stock any longer than 10-1968 ann_ret wont be calculated for after that period. Is there a procedure to compute Ann_ret in the way mentioned above in SAS? 

[ballardw]

---

@Akarsh91 wrote:
In output, I would like to see the annual return, for the time period mentioned in my original post. My main goal is however to find the product of the rows. On the anniversary (month=10) the stock is supposed to be sold. As to the ID, it refers to the stock I am holding. ID 1 means that I am only holding 1 stock with ID 1.

---

Since your example data does shows that the stock wasn't sold at month = 10 what should we do?

And by output example is should look like something like:

ID    Date                Returns      Delisting return        month         year

1   1967-10-28     1.025                                            10            1967

1  1967-11-28      1.026                                            11            1967

1   1967-12-28     1.027                                            12            1967

1    1968-01-28      1.01                                             1             1968

1    1968-02-28    1.04                                              2              1968

1    1968-03-28     1.001                                           3              1968

1   1968 -04-28     1.005   .                                       4              1968

1   1968-05-28     1.02                                              5              1968

1   1968-06-28      1.02                                             6               1968

1    1968-07-28     1.06                                              7              1968

1    1968-08-28      1.06                                             8             1968

1   1968-09-28       1.07                                             9            1968

1   1968-10-28       1.07                                            10            1968

1    1968-11-28     1.08                                             11            1968

1    1968-12-28     1.01                                             12            1968

1   1969-01-28     1.01                                               1            1969

1   1969-02-28      1.04                                              2            1969

1    1969-03-28     1.001                                            3            1969

1    1969-04-28     1.005                                            4             1969

 only with the added column value.