turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Function Multiply

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-23-2011 04:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to EmersonK

05-23-2011 04:23 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to EmersonK

05-23-2011 04:57 PM

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?

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

05-24-2011 12:50 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DouglasMartin

05-24-2011 01:46 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

05-26-2011 01:43 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to EmersonK

05-24-2011 02:57 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Peter_C

05-24-2011 05:53 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Howles

05-31-2011 04:43 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to EmersonK

05-25-2011 05:00 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to EmersonK

05-28-2011 05:18 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Howles

05-28-2011 06:28 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

05-29-2011 09:23 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Peter_C

06-05-2011 02:36 AM

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]

[pre]

data a;

x =64**(1/6);

y = geomean(64,1,1,1,1,1);

put x= y=;

run;

[/pre]