topic Re: Multiplying observations-values in SAS Programming

Multiplying observations-values

dataMart87 — Fri, 03 Sep 2021 17:44:28 GMT

https://communities.sas.com/t5/General-SAS-Programming/Multiplying-observations-values-in-row-1-by-values-in-row-2-and/td-p/363815

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;

Re: Multiplying observations-values

Reeza — Fri, 03 Sep 2021 17:46:05 GMT

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

Re: Multiplying observations-values

dataMart87 — Fri, 03 Sep 2021 17:48:28 GMT

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

Re: Multiplying observations-values

Reeza — Fri, 03 Sep 2021 19:30:30 GMT

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

Re: Multiplying observations-values

dataMart87 — Sat, 04 Sep 2021 00:45:53 GMT

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

Re: Multiplying observations-values

Ksharp — Sat, 04 Sep 2021 11:33:13 GMT

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

Re: Multiplying observations-values

Ksharp — Sat, 04 Sep 2021 12:01:44 GMT

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;

Re: Multiplying observations-values

Ksharp — Sat, 04 Sep 2021 12:07:27 GMT

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;

Re: Multiplying observations-values

FreelanceReinh — Sat, 04 Sep 2021 13:48:19 GMT

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

returns are positive, zero or missing (i.e., not negative)
there is no year with only missing returns
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).

Re: Multiplying observations-values

dataMart87 — Sat, 11 Sep 2021 01:18:09 GMT

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

Re: Multiplying observations-values

dataMart87 — Sat, 11 Sep 2021 01:19:15 GMT

This works too. Thanks, @FreelanceReinh