topic Re: Aggregating rows in SAS Procedures

Aggregating rows

Guinaba — Thu, 22 Aug 2013 10:48:58 GMT

May I ask you for your help? I am trying to sum up the values from COL1 – COL3 and group them by COL (_NAME_), having just one row for JAN11 and so forth…Any suggestion? I've tried to group by but didn't worked.

proc transpose data= Aero_Final

out= Aero_Final_Trans;

var '26/1/2011'N -- '16/12/2012'N;

run;

data Aero_Final_Trans;

set Aero_Final_Trans (rename=(_NAME_= TEMP));

_NAME_= input(TEMP,ddmmyy10.);

Format _NAME_ MONYY5.;

DROP temp;

run;

_NAME_ COL1 COL2 COL3

JAN11 $99 $87 $56

JAN11 $68 $30 $29

JAN11 $69 $30 $29

JAN11 $60 $30 $25

FEB11 $20 $30 $24

FEB11 $30 $30 $24

MAR11 $38 $30 $23

Re: Aggregating rows

Murray_Court — Thu, 22 Aug 2013 11:16:51 GMT

Hi,

It looks like you are trying to group by month, when the month name is only the format, and the underlying date values can still be distinct within each month. This will make aggregation difficult.

What do you mean by 'group them by NAME', are you trying to sum COL1 - COL3 and then sum again by Name?

If so then try:

Proc sql;

create table want as

select _NAME_, sum(col1 + col2 + col3) as total

from Aero_Final_Trans

group by _NAME_;

quit;

Re: Aggregating rows

Guinaba — Thu, 22 Aug 2013 11:56:43 GMT

Hi Murray,

Thank you for you reply! What I would like to do is group the data by month, so I would have only one row per month, I don't need to sum up the col1, col2, col3, etc. Any suggestion?

Re: Aggregating rows

Murray_Court — Thu, 22 Aug 2013 12:17:06 GMT

Ok, then use this code

Proc sql;

create table want as

select _NAME_,

sum(col1) as col1tot,

sum(col2) as col2tot,

sum(col3) as col3tot

from Aero_Final_Trans

group by _NAME_;

quit;

I suspect that it will not group by _NAME_ properly, as you have changed the format to display the month only but not the underlying value. If it gives you far too many monthly totals then you should use the PUT function as opposed to the INPUT one in your initial stage, this will give you a character variable and not just the facade of one.

data Aero_Final_Trans;

set Aero_Final_Trans (rename=(_NAME_= TEMP));

_NAME_= put(TEMP,MONYY5.);

Format _NAME_ MONYY5.;

DROP temp;

run;

Re: Aggregating rows

DR_Majeti — Thu, 22 Aug 2013 17:07:52 GMT

Hi,

This might also Helps you, but in two steps..

Proc sort data=aero_final_trans out=want;
by _name_;
Run;

Proc means data=want sum noprint;
Var col1-col3;
By _name_;
output out=sum_name(drop=_type_ _freq_)
sum= /autoname;

Run;

Durga

Re: Aggregating rows

Guinaba — Thu, 22 Aug 2013 22:52:44 GMT

Thanks Murray! I appreciate your feedback! It worked.