Average over number of variables where number of variables is dictated by separate column

pakalla 0
Viewed 50

I would like to create a new column whose values equal the average of values in other columns. But the number of columns I am taking the average of is dictated by a variable. My data look like this, with 'length' dictating the number of columns x1-x5 that I want to average:

data have;
    input ID $ length x1 x2 x3 x4 x5;
    datalines;
A 5 8 234 79 36 78
B 4 8 26 589 3 54
C 3 19 892 764 89 43
D 5 72 48 65 4 9
;
run;

I would like to end up with the below where 'avg' is the average of the specified columns.

data want;
    input ID $ length avg
    datalines;
A 5 87
B 4 156.5
C 3 558.3
D 5 39.6
;
run;

Any suggestions? Thanks! Sorry about the awful title, I did my best.

sas
4 Answers

You have to do a little more work since mean(of x[1]-x[length]) is not valid syntax. Instead, save the values to a temporary array and take the mean of it, then reset it at each row. For example:

tmp1 tmp2 tmp3 tmp4 tmp5
8    234  79   36   78
8    26   589  3    .
19   892  764  .    . 
72   48   65   4    9

data want;
    set have;
    array x[*] x:;
    array tmp[5] _temporary_;
    
    /* Reset the temp array */
    call missing(of tmp[*]);

    /* Save each value of x to the temp array */
    do i = 1 to length;
        tmp[i] = x[i];
    end;

    /* Get the average of the non-missing values in the temp array */
    avg = mean(of tmp[*]);

    drop i;
run;
Stu Sztukowski0

@Reeza's solution is good, but in case of missing values in x it will produce not always desirable result. It's better to use a function SUM. Also the code is little simplified:

data want (drop=i s);
  set have;
  array a{*} x:;
  s=0; nm=0;
  do i=1 to length;
    if missing(a{i}) then nm+1;
    s=sum(s,a{i});
  end;
  avg=s/(length-nm);
run;
crow163840

Use an array to average it by summing up the array for the length and then dividing by the length.

data have;
    input ID $ length x1 x2 x3 x4 x5;
datalines;
A 5 8 234 79 36 78
B 4 8 26 589 3 54
C 3 19 892 764 89 43
D 5 72 48 65 4 9
;

data want;
set have;

array x(5) x1-x5;

sum=0;

do i=1 to length;
    sum + x(i);
end;

avg = sum/length;

keep id length avg;
format avg 8.2;

run;
Reeza0

Rather than writing your own code to calculate means you could just calculate all of the possible means and then just use an index into an array to select the one you need.

data have;
    input ID $ length x1 x2 x3 x4 x5;
datalines;
A 5 8 234 79 36 78
B 4 8 26 589 3 54
C 3 19 892 764 89 43
D 5 72 48 65 4 9
;

data want;
  set have;
  array means[5] ;
  means[1]=x1;
  means[2]=mean(x1,x2);
  means[3]=mean(of x1-x3);
  means[4]=mean(of x1-x4);
  means[5]=mean(of x1-x5);
  want = means[length];
run;

Results: enter image description here

user7NPk0
Related