Using SAS functions vertically

[Greek]

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!

[AncaTilea]

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.

[Fugue]

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;

[PaigeMiller]

PROC MEANS does the job.

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

[Astounding · Accepted solution]

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.

[Greek]

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;

[AncaTilea]

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.

[Astounding]

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.

[Greek]

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!

[Greek]

IT WORKED!

Thank you so much!