Help using Base SAS procedures

Aggregating rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Aggregating rows

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


Accepted Solutions
Solution
‎08-22-2013 06:52 PM
Occasional Contributor
Posts: 15

Re: Aggregating rows

Posted in reply to Murray_Court

Thanks Murray! I appreciate your feedback! It worked.

View solution in original post


All Replies
Contributor
Posts: 45

Re: Aggregating rows

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;

Occasional Contributor
Posts: 15

Re: Aggregating rows

Posted in reply to Murray_Court

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?

Contributor
Posts: 45

Re: Aggregating rows

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;

Solution
‎08-22-2013 06:52 PM
Occasional Contributor
Posts: 15

Re: Aggregating rows

Posted in reply to Murray_Court

Thanks Murray! I appreciate your feedback! It worked.

Contributor
Posts: 64

Re: Aggregating rows

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 304 views
  • 4 likes
  • 3 in conversation