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.
Hi @Macro,
You can also use PROC SQL:
proc sql;
select (sum(count)**2-uss(count))/2 as b
from temp;
quit;
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.
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.
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;
Thanks Kurt. This is the data step array way I am lookin for.
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
Hi @Macro,
You can also use PROC SQL:
proc sql;
select (sum(count)**2-uss(count))/2 as b
from temp;
quit;
never knew what uss function was. I learn so much on this forum!
@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.
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().
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.