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

[Macro]

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.

[ballardw]

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.

[Macro]

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. 

[Kurt_Bremser]

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;

[Macro]

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

[Macro]

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

[FreelanceReinh · Accepted solution]

Hi @Macro,

You can also use PROC SQL:

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

[Ksharp]

COOL .

[tarheel13]

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

[FreelanceReinh]

---

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

[Macro]

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

[Ksharp]

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;

[Macro]

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.

[Ksharp]

Hi. You can make it easily by data step:

data temp;
set temp;
id+1;
run;