Help using Base SAS procedures

Using SAS functions vertically

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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

ABC
i12312
ii3221
iii7525

I transpose to be able to use the SUM function horizontally

iiiiiiSum
A13711
B232530
C12212558

then I transpose back To get the Sum the way I want it.

ABC
i12312
ii3221
iii7525
Sum113058

THERE HAS TO BE A BETTER WAY!


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

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.

View solution in original post


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;

Trusted Advisor
Posts: 1,908

Re: Using SAS functions vertically

PROC MEANS does the job.

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

Solution
‎08-07-2013 03:49 PM
Super User
Posts: 5,495

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: 45

Re: Using SAS functions vertically

Posted in reply to Astounding

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: 5,495

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: 45

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! Smiley Happy

Contributor
Posts: 45

Re: Using SAS functions vertically

IT WORKED!

Thank you so much!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 433 views
  • 6 likes
  • 5 in conversation