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

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
FreelanceReinh
Jade | Level 19

Hi @Macro,

 

You can also use PROC SQL:

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

View solution in original post

13 REPLIES 13
ballardw
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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

Macro
Obsidian | Level 7

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
Jade | Level 19

Hi @Macro,

 

You can also use PROC SQL:

proc sql;
select (sum(count)**2-uss(count))/2 as b
from temp;
quit;
tarheel13
Rhodochrosite | Level 12

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

FreelanceReinh
Jade | Level 19

@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
Obsidian | Level 7

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
Super User
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
Obsidian | Level 7

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
Super User
Hi. You can make it easily by data step:

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1232 views
  • 12 likes
  • 6 in conversation