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

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

Akarsh91 — Fri, 02 Jun 2017 14:13:40 GMT

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

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

Akarsh91 — Fri, 02 Jun 2017 14:20:56 GMT

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

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

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

PaigeMiller — Fri, 02 Jun 2017 15:06:00 GMT

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;

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

kiranv_ — Fri, 02 Jun 2017 14:36:41 GMT

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;

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

PaigeMiller — Fri, 02 Jun 2017 14:40:26 GMT

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

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

kiranv_ — Fri, 02 Jun 2017 14:41:48 GMT

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

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

kiranv_ — Fri, 02 Jun 2017 14:56:07 GMT

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;

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

ballardw — Fri, 02 Jun 2017 14:55:53 GMT

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?

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

PaigeMiller — Fri, 02 Jun 2017 15:09:36 GMT

@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.

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

Ksharp — Fri, 02 Jun 2017 15:36:18 GMT

Is it what you are looking for ?

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;
data want;
 if _n_=1 then do;
  if 0 then set have(rename=(returns=r));
  declare hash h(dataset:'have(rename=(returns=r))');
  h.definekey('id','date');
  h.definedata('r');
  h.definedone();
 end;
set have;
cum_prod=1;
do i=0 to 12;
 _date=intnx('month',date,i,'s');
 if h.find(key:id,key:_date)=0 then cum_prod=cum_prod*r;
end;
drop i _date r;
run;

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

Akarsh91 — Fri, 02 Jun 2017 16:41:57 GMT

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.

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

kiranv_ — Fri, 02 Jun 2017 17:09:32 GMT

if possible, can you show how your output should be

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

ballardw — Fri, 02 Jun 2017 17:38:54 GMT

@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.

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

Akarsh91 — Fri, 02 Jun 2017 17:46:59 GMT

@Ksharp

Not exactly. I just want to multiply the value of return in the first row with the value of ret in the second row, value of return of the second row by the value of return in the third row and so on. Is there a procedure or a code in SAS?

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

Akarsh91 — Fri, 02 Jun 2017 18:07:36 GMT

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?

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

Akarsh91 — Fri, 02 Jun 2017 18:10:05 GMT

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

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

kiranv_ — Fri, 02 Jun 2017 19:09:57 GMT

did you try this, it worked for me

proc sql;

create table want as

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

from have

group by year;

quit;

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

Akarsh91 — Fri, 02 Jun 2017 20:11:20 GMT

Thanks, it worked.

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

Ksharp — Sat, 03 Jun 2017 04:34:36 GMT

If you have missing RETURN, try PROC IML + function CUPROD().

Assuming there is only one ID.

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;

proc iml;
use have;
read all var{returns year};
close;
y=unique(year);
cum_prod=j(ncol(y),1,.);
do i=1 to ncol(y);
 idx=loc(year=y[i]);
 cum_prod[i]=cuprod(returns[idx])[ncol(idx)];
end;
print (y`) cum_prod;
quit;

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

PaigeMiller — Sat, 03 Jun 2017 13:30:05 GMT

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.