How to Sum except one value in a row?

Reply
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: 7,431

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.

Respected Advisor
Posts: 3,786

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

1234
1100 (100%)20 (20%)30 (30%)40 (40%)
210 (20%)50 (100%)10 (20%)5 (10%)
3203060 (100%)10
4204010100 (100%)
PROC Star
Posts: 7,431

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: 9,856

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

Ask a Question
Discussion stats
  • 6 replies
  • 271 views
  • 0 likes
  • 4 in conversation