Quartz | Level 8

## Multiplying observations-values

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Multiplying observations-values

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;```
10 REPLIES 10
Super User

## Re: Multiplying observations-values

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

Quartz | Level 8

## Re: Multiplying observations-values

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

Super User

## Re: Multiplying observations-values

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

Quartz | Level 8

## Re: Multiplying observations-values

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

Super User

## Re: Multiplying observations-values

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

## Re: Multiplying observations-values

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;

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;```
Super User

## Re: Multiplying observations-values

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;```
Quartz | Level 8

## Re: Multiplying observations-values

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

## Re: Multiplying observations-values

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

Quartz | Level 8

## Re: Multiplying observations-values

This works too.  Thanks, @FreelanceReinh

Discussion stats
• 10 replies
• 952 views
• 2 likes
• 4 in conversation