Multiplying observations-values

[dataMart87]

https://communities.sas.com/t5/General-SAS-Programming/Multiplying-observations-values-in-row-1-by-v...

This is a follow question to the link above.  The solution works for non-zero values.  Is there a solution if one of values in the group is 0 (zero)?

data have;
infile datalines dlm=',' dsd truncover;
input ID Date:anydtdte. Returns Delisting_return month year;
format date date9.;
datalines;
1,1967-10-28,0,,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 sql;

create table want as

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

from have

group by year;

quit;

[Reeza]

LOG(0) is undefined so what do you want to return in that case? 0? Missing?

[dataMart87]

I would like it to return a 0, just like multiplication by 0 returns a 0.

[Reeza]

But log of 0 is undefined.....

So then you want something like below I'd guess:

exp(sum(case when returns = 0 then 0 else log(returns) end))

[dataMart87]

Thanks.  Does not work though.  The group year=1967 has one Returns value=0, but newcol has a non-zero value (1.027*1.026).

[Ksharp]

It would be all 0 ,since 0 multiply anything is 0 .

[Ksharp]

If you have SAS/IML, could try PROD() function.

data have;
infile datalines dlm=',' dsd truncover;
input ID Date:anydtdte. Returns Delisting_return month year;
format date date9.;
datalines;
1,1967-10-28,0,,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  nobs nobs;
read all var {year Returns};

first=uniqueby(year);
last=remove(first,1)-1||nobs;

product=j(nrow(first),1,.);
do i=1 to nrow(first);
 product[i]=prod(Returns[first[i]:last[i]]);
end;

print (year[first]) product;
quit;

[Ksharp · Accepted solution]

Or try data step ?

data have;
infile datalines dlm=',' dsd truncover;
input ID Date:anydtdte. Returns Delisting_return month year;
format date date9.;
datalines;
1,1967-10-28,0,,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;
 do until(last.year);
  set have;
  by id year;
  if first.year then want=1;
  want=want*Returns;
 end;
  do until(last.year);
  set have;
  by id year;
  output;
 end;
run;

[dataMart87]

Don't have SAS/IML, but DATA step works.  Thanks, @Ksharp 

[FreelanceReinh]

Hello @dataMart87,

To fix the PROC SQL approach, I suggest this:

proc sql;
create table want as
select year, returns,
       min(returns~=0)*exp(sum(log(ifn(returns,returns,1)))) as newcol
from have
group by year;
quit;

This assumes that

1. returns are positive, zero or missing (i.e., not negative)
2. there is no year with only missing returns
3. you want to compute the product of all non-missing returns.

If condition 2 is possibly not met, this special case should be handled in a CASE expression (or another call of the IFN function), e.g., then setting newcol to missing:

proc sql;
create table want as
select year, returns,
       case when n(returns) then min(returns~=0)*exp(sum(log(ifn(returns,returns,1))))
            else . /* i.e., in the special case "only missing returns" */
       end as newcol
from have
group by year;
quit;

Note that the IFN(...) expression (or an equivalent CASE expression) in the argument of the LOG function prevents unwanted notes about invalid or missing arguments in the SAS log (as long as no negative returns occur).

[dataMart87]

This works too.  Thanks, @FreelanceReinh