BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SMR
Fluorite | Level 6 SMR
Fluorite | Level 6
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
..
..
1 ACCEPTED SOLUTION

Accepted Solutions
Olivier
Pyrite | Level 9

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.

 

cc.png

View solution in original post

9 REPLIES 9
deleted_user
Not applicable
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;
SMR
Fluorite | Level 6 SMR
Fluorite | Level 6
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,
Olivier
Pyrite | Level 9

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.

 

cc.png

lmyers2
Obsidian | Level 7

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

KrishanAvtarSingh
Calcite | Level 5

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

imld
Calcite | Level 5

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

ashish112
Fluorite | Level 6

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


Nikit7
Calcite | Level 5

@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;
Nikit7
Calcite | Level 5

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


 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 91262 views
  • 8 likes
  • 8 in conversation