Contributor
Posts: 21

# How to Sum except one value in a row?

I have a table looks like

ID1234
1100203040
21050105
320306010
4204010100

I would like to summarize the value except one value when two id matches.

For instance, the regular sum for row one is (100+20+30+40)=190

I would like to exclude the first cell and return the sum to 20+30+40=90

For second row, my expected sum would be 10+10+5=25

Can anyone let me know how to do it?

PROC Star
Posts: 8,165

## Re: How to Sum except one value in a row?

Are you asking for something like?:

data have;

input ID col1-col4;

cards;

1 100 20 30 40

2 10 50 10 5

3 20 30 60 10

4 20 40 10 100

;

data want;

set have;

array vals(*) col:;

sum=sum(of vals(*))-vals(id);

run;

Contributor
Posts: 21

## Re: How to Sum except one value in a row?

Thank you Arthur! That's exactly what I'm looking for. However, my dataset is very large. I have a 200*200 tables created. I tried to use your code but it returns that

ERROR: Array subscript out of range at line 48 column 11.

Can you let me know how to fix it? Thank you.

Posts: 3,852

## Re: How to Sum except one value in a row?

You should have explained that in your initial question.  Maybe you can calculate the diagonal index using MOD function.

If you want a good answer give the proper detail in the question.

Contributor
Posts: 21

## Re: How to Sum except one value in a row?

Eventually I would like to add percentage to my table to make it look like:

 ID 1 2 3 4 1 100 (100%) 20 (20%) 30 (30%) 40 (40%) 2 10 (20%) 50 (100%) 10 (20%) 5 (10%) 3 20 30 60 (100%) 10 4 20 40 10 100 (100%)
PROC Star
Posts: 8,165

## Re: How to Sum except one value in a row?

Without getting into the percentage issue, from your error I presume that IDs are not simply 1, 2, etc.

Do you want to exclude the column based on the record number? If so, does the following do what you want?:

data want;

set have;

array vals(*) col:;

sum=sum(of vals(*))-vals(_n_);

run;

Super User
Posts: 10,784

## Re: How to Sum except one value in a row?

I think you should follow Arthur.T 's code. Here is IML code, just for fun.

```data have;
input ID col1-col4;
cards;
1 100 20 30 40
2 10 50 10 5
3 20 30 60 10
4 20 40 10 100
;
proc iml;
use have(drop=id);
read all var _num_ into x;
close;
w=x[,+]-vecdiag(x);
create want var{w};
append;
close;
quit;
```

Xia Keshan

Discussion stats
• 6 replies
• 332 views
• 0 likes
• 4 in conversation