topic Re: How to sum excluding a specific value within a row in arrays in SAS Programming

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

sri1 — Mon, 03 Aug 2020 16:59:33 GMT

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

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

ballardw — Mon, 03 Aug 2020 17:07:00 GMT

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.

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

PaigeMiller — Mon, 03 Aug 2020 17:42:50 GMT

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

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

andreas_lds — Tue, 04 Aug 2020 05:06:34 GMT

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.

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

Kurt_Bremser — Tue, 04 Aug 2020 06:25:13 GMT

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;

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

PaigeMiller — Tue, 04 Aug 2020 10:36:55 GMT

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.