DATA Step, Macro, Functions and more

Function Multiply

Reply
New Contributor
Posts: 2

Function Multiply

Is there a function similar SUM function for use in multiply of values?
Frequent Contributor
Posts: 139

Re: Function Multiply

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.
PROC Star
Posts: 7,474

Re: Function Multiply

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?
Contributor
Posts: 35

Re: Function Multiply

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.
PROC Star
Posts: 7,474

Re: Function Multiply

Posted in reply to 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?

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.
Contributor
Posts: 35

Re: Function Multiply

> 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 . .
Valued Guide
Posts: 2,177

Re: Function Multiply

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 <:-)
Regular Contributor
Posts: 184

Re: Function Multiply

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
Regular Contributor
Posts: 184

Re: Function Multiply

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

Re: Function Multiply

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.. ??
Regular Contributor
Posts: 184

Re: Function Multiply

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.
PROC Star
Posts: 7,474

Re: Function Multiply

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 xSmiley Happy**(n(of xSmiley Happy);
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.
Valued Guide
Posts: 2,177

Re: Function Multiply

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
Valued Guide
Posts: 634

Re: Function Multiply

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]
Ask a Question
Discussion stats
  • 13 replies
  • 11337 views
  • 1 like
  • 8 in conversation