Function Multiply

[EmersonK]

Is there a function similar SUM function for use in multiply of values?

[darrylovia]

Unfortunately not.
I would love to have a PRODUCT function and a SUMPRODUCT function, but in the mean time you'll have to use an ARRAY to calculated it.

[art297]

What exactly are you looking for? The sum function can take products. E.g.,
[pre]
data have;
input x y;
sumxy=sum(x*y);
cards;
1 1
2 2
3 3
4 .
5 .
;
[/pre]

Art
--------
> Is there a function similar SUM function for use in
> multiply of values?

[DouglasMartin]

That wouldn't solve the problem. The SUM function takes the sum of the non-missing arguments. What is being asked for is a function that would take the product of the non-missing arguments. Saying something like SUM(A*B) won't help; the result would be missing if either A or B was missing.

[art297]

Then, I don't understand what would be the expected result. One responder indicated a feature like Excel's sumproduct function which also only provides the sum of only non-missing products as well. What value would you expect to result if there was a missing value?

The following provides the same result as an Excel sumproduct call:
[pre]
data want;
input x1-x3 y1-y3;
array xvals(*) x1-x3;
array yvals(*) y1-y3;
do i=1 to 3;
sumproduct=sum(sumproduct,xvals(i)*yvals(i));
end;
cards;
1 . 3 1 2 3
2 4 6 2 4 6
;
[/pre]
Art
> That wouldn't solve the problem. The SUM function
> takes the sum of the non-missing arguments. What is
> being asked for is a function that would take the
> product of the non-missing arguments. Saying
> something like SUM(A*B) won't help; the result would
> be missing if either A or B was missing.

[DouglasMartin]

> Then, I don't understand what would be the expected
> result. One responder indicated a feature like
> Excel's sumproduct function which also only provides
> the sum of only non-missing products as well. What
> value would you expect to result if there was a
> missing value?

In the original question he was asking for something similar to SUM but for products. SUM(1, 2, . , 4, . ) results in 7 even though 1+2+.+4+. results in . I understood his question as wanting something like PRODUCT(1, 2, ., 4, .) giving a result of 8 even though 1*2*.*4*. results in . .

[Peter_C]

would it be satisfactory to sum logs of the values and then antilog the totals provided by proc means[pre]%let prod_list = this that and the other ;
%let inputDS = your_data ;
%let outputDS= products ;
data v /view=v ;
set &inputDS ;
array those &prod_list ;
do over those ;
those = log( those ) ;
end ;
run ;
proc means noprint nway data= v ;
var &prod_list ;
output sum= out= _data_ ;
run ;
%let loseit = &syslast ;
data &outputDS ;
set ;
array those &prod_list ;
do over those ;
those = antilog( those) ;
end ;
run ;
proc datasets nolist ;
delete &loseit / mt= data ;
delete v / mt= view ;
run;
quit ;[/pre] Of course (last time I looked) the required function was not antilog(), but something else that I can't remember.
But if this is your solution, I'm sure it is worth the effort of the search <:-)

[Howles]

SQL can do it, but beware of the way it handles zero and negative factors.

data demo ;
input groupid $ numval ;
cards ;
a 2
a .
a 3
b 1
b 0
c 1
c -1
d -2
d 1
d -3
;

proc sql ;
select groupid, exp( sum( log( numval ) ) ) as product
from demo
group by groupid ;
quit ;

Result:

groupid product

a 6
b 1
c 1
d 1

[Howles]

See http://www.sascommunity.org/wiki/Computing_Products for a workaround designed to handle negatives and zeroes.

[Nelson_Jr]

Hi everybody... this is so. so.. easy..

data test;
input x y;
cards;
-1 1
2 2
3 3
4 4
-5 5
6 6
7 7
8 .
9 .
10 .
;
run;
data test2;
set test;
t=sum(missing(y) , y ) *x; /*This result is the same results the SUM function*/
run;

data test3;
set test;
t=sum(0, y ) *x; /*This result is the same results generated in multiply per 0 */
run;

Nice.. ??

[Howles]

It's surprising that there is no PRODUCT function when there is the closely related GEOMEAN function:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002595249.htm.

[art297]

Howard,

You're asking a lot of someone who failed even high school math, and I'm not even sure this is what the OP was asking for, but ... While we don't have an nthroot function to calculate the geometric mean we do have, as you mentioned, the geometric function itself (at least as of 9.2). Thus, why not simply reverse the operation. I.e.,
[pre]
data want;
input x1 x2 x3 x4;
sumproduct=geomean(of x:)**(n(of x:));
cards;
1 . 3 4
2 4 6 8
;
[/pre]
Art
----------
> It's surprising that there is no PRODUCT function
> when there is the closely related GEOMEAN function:
>
> http://support.sas.com/documentation/cdl/en/lrdict/643
> 16/HTML/default/viewer.htm#a002595249.htm.

[Peter_C]

Art
Thank you for introducing the GEOMEAN()
great function!
Even though you might not be a math wizard you might need the N-th root sometime! When the day comes just use
( your_number) ** ( 1/ N )
If I recall correctly a root is just the power of the inverse

peterC

[ArtC]

As an aside. Peter shows what I believe is the correct way to calculate the Nth root, but the GEOMEAN also calculates this value. The following shows two ways to calculate the the 6th root of 64 (a number even I can double check).
[pre]
data a;
x =64**(1/6);
y = geomean(64,1,1,1,1,1);
put x= y=;
run;
[/pre]

[Ashraf_Morsi]

     /*You can try this*/

     Multiply_Result = Sum(X,0.0)*Sum(Y,0.0);    

     /*Sum function will handle missing values for you. Any missing value for X or Y will be set to (Zero)   */

     /*You can use Sum function also in complex equations like X/Y*Z-N/E    to be   Sum(X,0.0)/Sum(Y,0.0)*Sum(Z,0.0)-Sum(N,0.0)/Sum(E,0.0)       */

     /*I use "Sum" function as if it was like "ISNULL" in sql server    */