Solved
Contributor
Posts: 48

# Using SAS functions vertically

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!

Accepted Solutions
Solution
‎08-07-2013 03:49 PM
Super User
Posts: 6,753

## Re: Using SAS functions vertically

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.

All Replies
Super Contributor
Posts: 543

## Re: Using SAS functions vertically

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.

Super Contributor
Posts: 307

## Re: Using SAS functions vertically

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;

Posts: 2,985

## Re: Using SAS functions vertically

PROC MEANS does the job.

In fact, all PROCs in SAS work "vertically" on your data.

--
Paige Miller
Solution
‎08-07-2013 03:49 PM
Super User
Posts: 6,753

## Re: Using SAS functions vertically

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.

Contributor
Posts: 48

## Re: Using SAS functions vertically

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;

Super Contributor
Posts: 543

## Re: Using SAS functions vertically

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.

Super User
Posts: 6,753

## Re: Using SAS functions vertically

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.

Contributor
Posts: 48

## Re: Using SAS functions vertically

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!

Contributor
Posts: 48

## Re: Using SAS functions vertically

IT WORKED!

Thank you so much!

🔒 This topic is solved and locked.