topic Re: Function Multiply in SAS Programming

Function Multiply

EmersonK — Mon, 23 May 2011 20:01:43 GMT

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

Re: Function Multiply

darrylovia — Mon, 23 May 2011 20:23:41 GMT

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.

Re: Function Multiply

art297 — Mon, 23 May 2011 20:57:27 GMT

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?

Re: Function Multiply

DouglasMartin — Tue, 24 May 2011 16:50:34 GMT

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.

Re: Function Multiply

art297 — Tue, 24 May 2011 17:46:07 GMT

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.

Re: Function Multiply

Peter_C — Tue, 24 May 2011 18:57:22 GMT

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

Re: Function Multiply

Howles — Tue, 24 May 2011 21:53:35 GMT

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

Re: Function Multiply

Nelson_Jr — Wed, 25 May 2011 21:00:45 GMT

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

Re: Function Multiply

DouglasMartin — Thu, 26 May 2011 17:43:13 GMT

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

Re: Function Multiply

Howles — Sat, 28 May 2011 21:18:39 GMT

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.

Re: Function Multiply

art297 — Sat, 28 May 2011 22:28:46 GMT

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.

Re: Function Multiply

Peter_C — Sun, 29 May 2011 13:23:26 GMT

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

Re: Function Multiply

Howles — Tue, 31 May 2011 20:43:08 GMT

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

Re: Function Multiply

ArtC — Sun, 05 Jun 2011 06:36:41 GMT

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]

Re: Function Multiply

Ashraf_Morsi — Mon, 02 Mar 2020 07:58:30 GMT

/*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 */