topic How to sum along row? in SAS Enterprise Guide

How to sum along row?

SMR — Tue, 17 Oct 2006 23:50:44 GMT

Hi,

Is there a way to add a column that would provide sum of values of columns (say there are 200 columns) along the row? For example, to add a new column of 'Sum' below?

Example

A B C D E Sum
2 2 5 1 0 10
..
..

Re: How to sum along row?

deleted_user — Wed, 18 Oct 2006 01:54:33 GMT

data test;
infile cards;
input A1 A2 A3 A4 A5;
cards;
2 2 5 1 0
;
run;

data test2;
set test;
sum = sum (of a1-a5);
run;

Re: How to sum along row?

SMR — Wed, 18 Oct 2006 03:02:04 GMT

Hi Dan,

Could pl. suggest it in using Enterprise Guide? (Not in the programming code,I don't understand the codes at this moment). Thanks
Regards,

Re: How to sum along row?

Olivier — Wed, 26 Apr 2017 12:20:24 GMT

First suggestion, using DATA step and the SUM function:

data test;
infile cards;
input A1 A2 A3 A4 A5;
cards;
2 2 5 1 0
;
run;

data sums;
 set test;

 /* method 1: array of all numerics */
 array n {*} _numeric_;
 sum_allnum=sum(of n[*]);

 /*method 2: variable range in data set */
 sum_range=sum(of a1-a5);
run;

If using EG and you want this in the Query Builder (SQL), you need to add a new computed column.

Its formula is SUM(var1, var2, var3, ...)
(you name each and every variable to sum, and separate them with commas).
Unfortunately the previous shortcut SUM(OF var1-var10) only works in datasteps and EG does not code them for you.

Re: How to sum along row?

KrishanAvtarSingh — Thu, 29 Jan 2015 05:43:04 GMT

hey can you please tell me how to sum all the columns with different names....i.e. not a1,a2,a3,a4....but a,b,c,d,....

Thanks

Krishan Avtar Singh

Re: How to sum along row?

imld — Thu, 03 Mar 2016 19:03:19 GMT

Use above formula but replace var1,var2,etc. with column name

Re: How to sum along row?

lmyers2 — Wed, 03 Jan 2018 02:09:17 GMT

Hello - I had a related question. Since the below is in a data step, how do you get the actual numeric sum? When I did proc freq below, sas gave me the list of values and not the actual sum.

data; set;
sum_range=sum(of a1-a5);
run;

proc freq; tables sum_range; run;

Thanks

Laura

Re: How to sum along row?

ashish112 — Mon, 19 Nov 2018 15:22:12 GMT

I want to sum of only positive numbers then how to do it?

for example I have 200 odd variables columns and values like

a b c .... n

1 -2 1... 2

2 2 -4... -1

and I want only positive sum of variables like

a b c .... n PositiveSum

1 -2 1... 2 4

2 1 -4... -1 3

Re: How to sum along row?

Nikit7 — Thu, 30 Jan 2020 16:47:57 GMT

Replying to Kishan Avtar,

Hi, you can use the same techniques shown above with slight modification;

for defining array,
array n {*} A--D;   /* or array n(*) _numeric_; */
sum_allnum=sum(of n[*]);
..
for direct approach,
sum = sum (of A--D); /*sum(of _numeric_)*/

@KrishanAvtarSingh wrote:
hey can you please tell me how to sum all the columns with different names....i.e. not a1,a2,a3,a4....but a,b,c,d,....
Thanks
Krishan Avtar Singh

Re: How to sum along row?

Nikit7 — Thu, 30 Jan 2020 20:15:31 GMT

@ashish112 wrote:
I want to sum of only positive numbers then how to do it?

for example I have 200 odd variables columns and values like

a b c .... n
1 -2 1... 2
2 2 -4... -1
and I want only positive sum of variables like

a b c .... n PositiveSum
1 -2 1... 2 4
2 1 -4... -1 3

data test;
infile cards;
input A1 A2 A3 A4 A5;
cards;
-2 2 5 1 0
1 -2 5 1 2
;
run;

proc print;
data sums;
 set test;
 array n(*) A:;
 do i=1 to dim(n);
 if n[i]<0 then n[i]=0;
 sum=sum(of n(*));
 end;
run;
proc print;
run;