Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: How to summarize a column with combination of sum and product with...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-29-2021 10:31 PM
(1231 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Macro,

You can also use PROC SQL:

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

COOL .

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi. You can make it easily by data step:

data temp;

set temp;

id+1;

run;

data temp;

set temp;

id+1;

run;

**Available on demand!**

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

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.