topic Re: How to summarize a column with combination of sum and product within a data column in SAS Programming

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

Macro — Thu, 30 Sep 2021 02:31:15 GMT

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.

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

ballardw — Thu, 30 Sep 2021 04:01:57 GMT

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.

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

Macro — Thu, 30 Sep 2021 04:20:32 GMT

data temp;

input count;

datalines;

;

run;

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

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

Kurt_Bremser — Thu, 30 Sep 2021 07:21:09 GMT

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;

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

FreelanceReinh — Thu, 30 Sep 2021 08:56:15 GMT

Hi @Macro,

You can also use PROC SQL:

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

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

Ksharp — Thu, 30 Sep 2021 09:59:18 GMT

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;

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

Ksharp — Thu, 30 Sep 2021 10:01:29 GMT

COOL .

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

Macro — Thu, 30 Sep 2021 14:33:32 GMT

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.

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

tarheel13 — Thu, 30 Sep 2021 15:59:09 GMT

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

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

FreelanceReinh — Thu, 30 Sep 2021 16:27:29 GMT

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

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

Macro — Thu, 30 Sep 2021 18:54:21 GMT

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

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

Macro — Thu, 30 Sep 2021 18:56:43 GMT

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

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

Macro — Thu, 30 Sep 2021 20:12:41 GMT

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

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

Ksharp — Sat, 02 Oct 2021 06:03:57 GMT

Hi. You can make it easily by data step:

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