Hello!
Do you know if we can use SAS functions vertically like in excel instead of horizontally?
For example if I want the sum of each column A,B,C
A | B | C | |
i | 1 | 23 | 12 |
ii | 3 | 2 | 21 |
iii | 7 | 5 | 25 |
I transpose to be able to use the SUM function horizontally
i | ii | iii | Sum | |
A | 1 | 3 | 7 | 11 |
B | 23 | 2 | 5 | 30 |
C | 12 | 21 | 25 | 58 |
then I transpose back To get the Sum the way I want it.
A | B | C | |
i | 1 | 23 | 12 |
ii | 3 | 2 | 21 |
iii | 7 | 5 | 25 |
Sum | 11 | 30 | 58 |
THERE HAS TO BE A BETTER WAY!
If all you want is a report, PROC PRINT will do that easily. Just add this statement:
sum a b c;
If you want a data set instead of a report, it can still be done in one step. This assumes that your first variable is named ID:
data want;
set have end=alldone;
cum_a + a;
cum_b + b;
cum_c + c;
output;
if alldone;
id='Sum';
a = cum_a;
b = cum_b;
c = cum_c;
output;
drop cum_a cum_b cum_c;
run;
Good luck.
Hi.
One quicker way is to use the SUM function in PROC SQL:
data have;
input id $ A B C;
cards;
i 1 23 12
ii 3 2 21
iii 7 5 25
;
proc sql;
create table want as
select sum(a) as A, sum(b) as B, sum(c) as C
from have ;
quit;
If you actually want to have the sum appended at the bottom of your original data, then you can do this data step:
data really_want;
set have want;
if id = "" then id = "Sum";
run;
Good luck!
Anca.
Proc SQL with a union operator would also work . . .
proc sql ;
create table want as
select * from have
union
select 'sum' as id
, sum(A) as A
, sum(B) as B
, sum(C) as C
from have
order by id
;
quit;
PROC MEANS does the job.
In fact, all PROCs in SAS work "vertically" on your data.
If all you want is a report, PROC PRINT will do that easily. Just add this statement:
sum a b c;
If you want a data set instead of a report, it can still be done in one step. This assumes that your first variable is named ID:
data want;
set have end=alldone;
cum_a + a;
cum_b + b;
cum_c + c;
output;
if alldone;
id='Sum';
a = cum_a;
b = cum_b;
c = cum_c;
output;
drop cum_a cum_b cum_c;
run;
Good luck.
Dear Astounding,
I tried this code and I experienced some problems... I expected to create a sixth line with the word 'sum' under ID and the actual sums under everything else... Do you know what's wrong?
ods html;
data report;
input ID employees midpoint pay;
datalines;
1 20 1877000 1789000
2 17 1827000 1672000
3 17 1616000 1599000
4 28 2844000 2828000
5 18 1734000 1678000
run;
data report;
set report end=done;
cumemployees+employees;
cummidpoint+midpoint;
cumpay+pay; output;
if done; ID='Sum'; employees=cumemployees; midpoint=cummidpoint; pay=cumpay;
run;
proc print data=report;
run;
ods html close;
Hi.
There is one thing that you did not repeat from Astounding's code: output statement right before the run statement
data report;
set report end=done;
cumemployees+employees;
cummidpoint+midpoint;
cumpay+pay; output;
if done; ID='Sum'; employees=cumemployees; midpoint=cummidpoint; pay=cumpay;output;
run;
Another issue is that you define ID as numeric (1,2,3...) and then you try to assign the value "Sum" (character, right?) so SAS it's confused, so it will set it to "." (missing)
So, you want to define your variable ID as character...
then you are golden!
Good luck,
Anca.
You're on the right track here. You need to add another OUTPUT statement before the RUN statement.
Also, you need to change the INPUT statement to make ID a character variable. You won't be able to store the word "Sum" in a numeric variable.
If I missed any of the problems, just post again.
Also note, that you can run PROC PRINT on the original data before adding the "Sum" line. Just add a statement to PROC PRINT and see what you get:
sum employees midpoint pay;
Good luck.
I don't know how to use SQL, but I keep on bumping into it. I guess I will have to learn to do cool stuff.
Thank you all!
IT WORKED!
Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: