BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to summarize a column with combination of sum and product within a data column

Hi,

I have a dataset with two columns like the following:

data temp;

input item count;

datalines;

a1   n1

a2   n2

a3   n3

...

ak   nk

;

run;

Basically, {n1, n2, ...,n k} are the counts of {a1,... ak}. I would like to compute the number b= (n1*n2 + n1*n3+ ... +n1*nk) + (n2*n3 + n2*n4+...n2*nk) + ...+

(n_(k-1) *nk). Is there any efficient way to do it?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Jade | Level 19

## Re: How to summarize a column with combination of sum and product within a data column

Hi @Macro,

You can also use PROC SQL:

``````proc sql;
select (sum(count)**2-uss(count))/2 as b
from temp;
quit;``````
13 REPLIES 13
Super User

## Re: How to summarize a column with combination of sum and product within a data column

Suggest that you supply some actual numbers to the example data and the expected numeric result as a data set.

Your data step doesn't run because of value type mismatch. N1 is not a number.

If by "

Basically, {n1, n2, ...,n k} are the counts of {a1,... ak}. I would like to compute the number b= (n1*n2 + n1*n3+ ... +n1*nk) + (n2*n3 + n2*n4+...n2*nk) + ...+

(n_(k-1) *nk). "

If you actually want A in there somewhere you should use it. If A values have nothing to do with the calculation then don't bother to bring them into the data for the question.

Obsidian | Level 7

## Re: How to summarize a column with combination of sum and product within a data column

data temp;

input count;

datalines;

2

3

5

6

8

9

;

run;

How about this? This is actually abstract question, no data needed. but data may help the verification.

Super User

## Re: How to summarize a column with combination of sum and product within a data column

If I understand your requirement correctly, this should do it:

``````data temp;
input count;
datalines;
2
3
5
6
8
9
;

proc sql noprint;
select nobs into :nobs from dictionary.tables
where libname = "WORK" and memname = "TEMP";
quit;

data want;
set temp;
array c {&nobs.} _temporary_;
c{_n_} = count;
sum = 0;
b = 0;
do i = 1 to _n_;
do j = i + 1 to _n_;
sum = sum + c{j};
end;
b = b + c{i} * sum;
end;
run;``````
Obsidian | Level 7

## Re: How to summarize a column with combination of sum and product within a data column

Thanks Kurt. This is the data step array way I am lookin for.

Obsidian | Level 7

## Re: How to summarize a column with combination of sum and product within a data column

Hi Kurt,

When I run your code, the data step way, I found I got different answer from other sql code. So what is the problem here? Thanks

Jade | Level 19

## Re: How to summarize a column with combination of sum and product within a data column

Hi @Macro,

You can also use PROC SQL:

``````proc sql;
select (sum(count)**2-uss(count))/2 as b
from temp;
quit;``````
Super User

## Re: How to summarize a column with combination of sum and product within a data column

COOL .
Rhodochrosite | Level 12

## Re: How to summarize a column with combination of sum and product within a data column

never knew what uss function was. I learn so much on this forum!

Jade | Level 19

## Re: How to summarize a column with combination of sum and product within a data column

@tarheel13 wrote:

I learn so much on this forum!

Thanks! So do I. In fact, this particular solution was inspired by some of @Ksharp's posts where he cleverly used functions or even procedures to condense lengthy calculations.

Obsidian | Level 7

## Re: How to summarize a column with combination of sum and product within a data column

Thanks FreelanceReinhard. You really think mathematically in addition to programming, to recognize this sum is simply the sum of cross product terms from a complete square, and implement it with an existing function uss().

Super User

## Re: How to summarize a column with combination of sum and product within a data column

```data temp;
input id count;
datalines;
1 2
2 3
3 5
4 6
5 8
6 9
;
run;

proc sql;
select sum(a.count*b.count) as total
from temp as a , temp as b
where a.id<b.id;
quit;```
Obsidian | Level 7

## Re: How to summarize a column with combination of sum and product within a data column

Hi Ksharp,

Very neat solution using where statement of proc sql. In case my data temp does not have an ID column that is monotonic, but have an item column (numeric) that is not monotonic. How do we create an ID column, or can proc sql have an internal Id variable to make the where statement work, or other ways? Thanks.

Super User

## Re: How to summarize a column with combination of sum and product within a data column

Hi. You can make it easily by data step:

data temp;
set temp;
id+1;
run;
Discussion stats
• 13 replies
• 1232 views
• 12 likes
• 6 in conversation