BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Guinaba
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Guinaba
Fluorite | Level 6

Thanks Murray! I appreciate your feedback! It worked.

View solution in original post

5 REPLIES 5
Murray_Court
Quartz | Level 8

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;

Guinaba
Fluorite | Level 6

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?

Murray_Court
Quartz | Level 8

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;

Guinaba
Fluorite | Level 6

Thanks Murray! I appreciate your feedback! It worked.

DR_Majeti
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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