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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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

[ Edited ]

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  


Accepted Solutions
Solution
‎06-02-2017 04:10 PM
Trusted Advisor
Posts: 1,770

Re: Multiplying observations that is values in the first row by the second row and so on.


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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,770

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

[ Edited ]

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;
Occasional Contributor
Posts: 17

Multiplying observations that is values in the first row by the second row and so on.

 <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  

PROC Star
Posts: 277

Re: Multiplying observations that is values in the first row by the second row and so on.

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;

Trusted Advisor
Posts: 1,770

Re: Multiplying observations that is values in the first row by the second row and so on.

[ Edited ]

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?)

PROC Star
Posts: 277

Re: Multiplying observations that is values in the first row by the second row and so on.

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

PROC Star
Posts: 277

Re: Multiplying observations that is values in the first row by the second row and so on.

[ Edited ]

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;

Solution
‎06-02-2017 04:10 PM
Trusted Advisor
Posts: 1,770

Re: Multiplying observations that is values in the first row by the second row and so on.


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.

Occasional Contributor
Posts: 17

Re: Multiplying observations that is values in the first row by the second row and so on.

Thanks, it worked. 

Trusted Advisor
Posts: 1,770

Re: Multiplying observations that is values in the first row by the second row and so on.

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.

Super User
Posts: 11,101

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

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?

 

 

Occasional Contributor
Posts: 17

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

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.
PROC Star
Posts: 277

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

if possible, can you show how your output should be

Occasional Contributor
Posts: 17

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

 

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? 

Super User
Posts: 11,101

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


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 278 views
  • 4 likes
  • 5 in conversation