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

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

[Ksharp]

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;

[Akarsh91]

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

[kiranv_]

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;

[Ksharp]

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;