How to sum excluding a specific value within a row in arrays

[sri1]

Hi 

I have a dataset as below

data scores;

input m1 m2 m3 m4 m5 m6;

datalines;

8 4 3 8 8 6

4 8 2 1 3 1 

8 8 2  2 8 8

;

run;

I want to get sum of values which are not equal to 8, i.e for ex  for  first row I need only sum of 4+3+6 .Is there anyway I can do this by arrays  and I need to keep the values as they are .

Thanks

[ballardw]

Is 8 supposed to be a generic code for "this is not really a value" such as a response to a question with something like "I don't know" or "I don't want to answer this question"?

If so, you might be better off recoding the variable to a special missing value so you don't have to keep excluding the value from specific operations.

[PaigeMiller · Accepted solution]

---

@sri1 wrote:

I want to get sum of values which are not equal to 8, i.e for ex  for  first row I need only sum of 4+3+6 .Is there anyway I can do this by arrays  and I need to keep the values as they are .

---

data scores;
input m1 m2 m3 m4 m5 m6;
array m(*) m1-m6;
sum=0;
do i=1 to dim(m);
    if m(i)^=8 then sum=sum+m(i);
end;
drop i;
datalines;
8 4 3 8 8 6
4 8 2 1 3 1 
8 8 2  2 8 8
;
run;

[andreas_lds]

The problem could be solved by using a trivial where-statement in proc summary, but since you don't want to change the format of the data, that path is locked.

[PaigeMiller]

 I need to keep the values as they are .

Yes, I agree with @Kurt_Bremser and @andreas_lds. People often state restrictions on the data or restrictions on what SAS solutions are acceptable without good reason, and this just makes their life harder. Even if you have to keep the values as they are, a transpose leaves the original data set intact and provides and easier solution to obtaining the desired sum. A better restriction, in my mind, is to find the easiest programming path.

[Kurt_Bremser]

With a proper data structure, PROC SUMMARY does this quite easily:

proc summary data=long (where=(m ne 8));
by id;
var m;
output out=sum (keep=id sum_m) sum(m)=sum_m;
run;

To achieve this, you need to transpose, and you can merge back if needed:

data scores;
input m1 m2 m3 m4 m5 m6;
id = _n_;
datalines;
8 4 3 8 8 6
4 8 2 1 3 1 
8 8 2  2 8 8
;

proc transpose
  data=scores
  out=long (keep=id instance col1 rename=(col1=m))
  name=instance
;
by id;
var m:;
run;

proc summary data=long (where=(m ne 8));
by id;
var m;
output out=sum (keep=id sum_m) sum(m)=sum_m;
run;

data want;
merge
  scores
  sum
;
by id;
run;