topic Re: column wise sum positive numbers in SAS Programming

column wise sum positive numbers

pavank — Tue, 28 Nov 2023 04:03:15 GMT

data dsn;
input var1 var2 var3;
datalines;
1  -2   3
4   5  -6
-7  8   9
6  -1  -7
5   2   -4
-6  9   1
;
run;

How to sum column wise only positive rows ?

Re: column wise sum positive numbers

Tom — Tue, 28 Nov 2023 04:14:37 GMT

What is a "positive row"?

Do you mean only the positive values?

Or do you want to sum the three values for an observations and then decide whether to include that observation in the overall sum?

The later is easier.

proc means data=dsn sum;
 where sum(var1,var2,var3) > 0;
  var var1-var3;
run;

To do the former you will have to do a lot of the work yourself.

data want;
 set dsn end=eof;
  array actual var1-var3;
  array sums (3) _temporary_;
  do index=1 to dim(actual);
    if actual[index] > 0 then sums[index] + actual[index];
  end;
  if eof then do;
    do index=1 to dim(actual);
      actual[index]=sums[index];
    end;
    output;
  end;
  drop index;
run;

Result

Obs    var1    var2    var3

 1      16      24      13

It might be easier to first transpose the data. But you would need an unique identifier for each observation.

data dsn2;
  row+1;
  set dsn;
run;

proc transpose data=dsn2 out=dsn3;
  by row;
  var var1-var3;
run;

proc means data=dsn3 sum;
  where col1 > 0;
  class _name_;
  var col1;
run;

Result

The MEANS Procedure

   Analysis Variable : COL1

NAME OF
FORMER        N
VARIABLE    Obs             Sum
-------------------------------
var1          4      16.0000000

var2          4      24.0000000

var3          3      13.0000000
-------------------------------

Re: column wise sum positive numbers

Patrick — Tue, 28 Nov 2023 05:08:05 GMT

Thank you for providing sample data. What also often helps to clarify a question is to show the expected result.

Below code assuming you want to get the sum of positive values within a row.

data have;
  input var1 var2 var3;
datalines;
1  -2   3
4   5  -6
-7  8   9
6  -1  -7
5   2   -4
-6  9   1
;

data  want(keep=var: cum_vars: sum_var)
      want_grandtotal(keep=cum_vars: grandtotal)
      ;
  set have end=last;

  array vars {*} var:;
  array cum_vars {3} 8;
  retain cum_vars;

  do i=1 to dim(vars);
    if vars[i]>=0 then 
      do;
        sum_var=sum(sum_var,vars[i]);
        cum_vars[i]=sum(cum_vars[i],vars[i]);
      end;
  end;
  output want;

  grandtotal+sum_var;
  if last then output want_grandtotal;
  drop i;
run;

proc print data=want;
run;
proc print data=want_grandtotal;
run;

Re: column wise sum positive numbers

Ksharp — Tue, 28 Nov 2023 05:47:52 GMT

data dsn;
input var1 var2 var3;
datalines;
1  -2   3
4   5  -6
-7  8   9
6  -1  -7
5   2   -4
-6  9   1
;
run;
proc sql;
create table want as
select sum(ifn(var1>0,var1,0)) as var1,
       sum(ifn(var2>0,var2,0)) as var2,
	   sum(ifn(var3>0,var3,0)) as var3
from dsn;
quit;

Re: column wise sum positive numbers

Ksharp — Tue, 28 Nov 2023 05:53:34 GMT

If you have SAS/IML. That would be very convenient.

data dsn;
input var1 var2 var3;
datalines;
1  -2   3
4   5  -6
-7  8   9
6  -1  -7
5   2   -4
-6  9   1
;
run;
proc iml;
use dsn;
read all var _all_ into x[c=vname];
close;
want=(x#(x>0))[+,];
create want from want[c=vname];
append from want;
close;
quit;

Re: column wise sum positive numbers

yabwon — Tue, 28 Nov 2023 09:11:50 GMT

if you want just to sum positive (negative) values from a column try this:

data dsn;
input var1 var2 var3;
datalines;
1  -2   3
4   5  -6
-7  8   9
6  -1  -7
5   2   -4
-6  9   1
;
run;

data want;
  set dsn end=e;
  array A var1-var3;
  array B pos1-pos3; /* positive */
  array C neg1-neg3; /* negative, as a bonus*/
  do over A;
    B + (A<>0);
    C + (A><0);
  end;
  if e;
  drop var:;
run;

Bart

Re: column wise sum positive numbers

pavank — Wed, 29 Nov 2023 04:40:58 GMT

Hi Patrick

Thank you for your solution