BookmarkSubscribeRSS Feed
EmersonK
Calcite | Level 5
Is there a function similar SUM function for use in multiply of values?
14 REPLIES 14
darrylovia
Quartz | Level 8
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
Opal | Level 21
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
Calcite | Level 5
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
Opal | Level 21
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
Calcite | Level 5
> 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
Rhodochrosite | Level 12
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
Quartz | Level 8
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
Quartz | Level 8
See http://www.sascommunity.org/wiki/Computing_Products for a workaround designed to handle negatives and zeroes.
Nelson_Jr
Calcite | Level 5
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
Quartz | Level 8
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
Opal | Level 21
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
Rhodochrosite | Level 12
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
Rhodochrosite | Level 12
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
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 28679 views
  • 1 like
  • 9 in conversation