topic Re: Using SAS functions vertically in SAS Procedures

Using SAS functions vertically

Greek — Wed, 07 Aug 2013 18:27:57 GMT

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!

Re: Using SAS functions vertically

AncaTilea — Wed, 07 Aug 2013 18:38:48 GMT

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.

Re: Using SAS functions vertically

Fugue — Wed, 07 Aug 2013 19:39:51 GMT

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;

Re: Using SAS functions vertically

PaigeMiller — Wed, 07 Aug 2013 19:48:40 GMT

PROC MEANS does the job.

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

Re: Using SAS functions vertically

Astounding — Wed, 07 Aug 2013 19:49:13 GMT

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.

Re: Using SAS functions vertically

Greek — Wed, 07 Aug 2013 20:34:32 GMT

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!

Re: Using SAS functions vertically

Greek — Thu, 08 Aug 2013 13:42:20 GMT

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;

Re: Using SAS functions vertically

AncaTilea — Thu, 08 Aug 2013 14:39:41 GMT

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.

Re: Using SAS functions vertically

Astounding — Thu, 08 Aug 2013 14:40:47 GMT

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.

Re: Using SAS functions vertically

Greek — Fri, 09 Aug 2013 17:58:23 GMT

IT WORKED!

Thank you so much!