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
Thanks Murray! I appreciate your feedback! It worked.
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;
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?
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;
Thanks Murray! I appreciate your feedback! It worked.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.