I am currently trying to order the months in my dataset Sep-May and have been unable to find a solution that works. I have sorted the data by month to transpose it into the format I needed but this ordered it alphabetically. I have submitted my code below and included the resulting table.
Thank you!
proc sort data=perm.snow1a out=perm.snowy2;
by month;
where Year >=1992 and Year<=2001;
run;
proc Transpose data=perm.snowy2 name= Month out=perm.snowyy2 (rename=(col1=Year1992 col2=Year1993 col3=Year1994
col4=Year1995 col5=Year1996 col6=Year1997 col7=Year1998 col8=Year1999 col9=Year2000 col10=Year2001));
var Snowfall;
by Month;
run;
proc print data=perm.snowyy2 noobs;
title "Monthly Snowfall by Year";
run;
proc Transpose data=perm.snowy2 name= Month out=perm.snowyy2 prefix=Year;
var Snowfall;
by Month;
ID Year;
idlabel year;
run;
Change your proc transpose though....
And convert the month to a SAS date then, use any year.
data have;
set perm2.snowyy2;
date_var = input( catt('01', substr(month, 1,3), '2018'), date9.);
date_order =month( intnx('month', date, 4, 'b'));
run;
proc sort data=have;
by date_order ;
run;
proc print data=have;
var Month Year: ;
run;
Is month a SAS date? If so, add a new variable such as below and sort by that variable instead. You don't need to display it in the report.
date_order =month( intnx('month', date, 4, 'b'));
No, Month wasn't a SAS date, it was a character variable in the original dataset.
proc Transpose data=perm.snowy2 name= Month out=perm.snowyy2 prefix=Year;
var Snowfall;
by Month;
ID Year;
idlabel year;
run;
Change your proc transpose though....
And convert the month to a SAS date then, use any year.
data have;
set perm2.snowyy2;
date_var = input( catt('01', substr(month, 1,3), '2018'), date9.);
date_order =month( intnx('month', date, 4, 'b'));
run;
proc sort data=have;
by date_order ;
run;
proc print data=have;
var Month Year: ;
run;
FYI - leaving PROC TRANSPOSE as is would be dangerous because you're renaming the columns manually but its possible if the data is in a different order in a different run the years would be mixed up. So using the ID statement to automatically label the years is a better approach.
Thank you so much! Everything looks great! I am just learning SAS so I either forget about or am unaware of a lot of the options available for each procedure. The help with renaming the columns was a nice added bonus! Thank you again, I really appreciate it.
An easy way: create a new variable.
data want;
set have;
length newmonth $ 8;
select (month);
when ('Sep') newmonth='01 = Sep';
when ('Oct') newmonth='02 = Oct';
when ('Nov') newmonth='03 = Nov';
when ('Dec') newmonth='04 = Dec';
when ('Jan') newmonth='05 = Jan';
when ('Feb') newmonth='06 = Feb';
when ('Mar') newmonth='07 = Mar';
when ('Apr') newmonth='08 = Apr';
when ('May') newmonth='09 = May';
when ('Jun') newmonth='10 = Jun';
when ('Jul') newmonth='11 = Jul';
when ('Aug') newmonth='12 = Aug';
otherwise;
end;
run;
Now you have a variable (NEWMONTH) that can be used for sorting, and you can choose which variable (MONTH or NEWMONTH) should be used for reporting.
Thank you very much! I had tried this as a simple solution but was wondering if there was a more elegant solution. I'm still learning SAS and can find a solution but know that its likely not the most efficient or elegant way.
Since your goal is to produce a report, I think a better solution is to make a calendar date, and use it to generate a variable (call it SNOWYEAR) to use as a classification variable in proc tabulate or proc report. No need for sorting, whether alphabetically or chronologically:
data need;
date=input(cats('01',month,year),date9.);
snowyear=year(intnx('year.6',date,0,'E'));
run;
proc tabulate data=need noseps ;
where not (month in ('JUN','JUL','AUG'));
class snowyear date;
format date monname3. ;
var snow;
tables
date= ' '
, snowyear="Monthly Snow by Snow Season"*sum=' '*snow=' '*f=6.0
/ rts=6;
run;
Thank you! I really appreciate that you took the time to explain the functions and steps you took, I'm still learning SAS and the explanations really help clarify everything. I did have one quick question though. What is the purpose of the *f=6.0 option in the table statement?
Thank you again!
The "f=6.0" is the way to tell proc tabulate what format to use for the cell values (and the width of the column). The default format would have presented 2 decimal point values.
Great, that makes sense. I recognized the 6.0 as a format but the *f was unfamiliar to me. Thank you very much, I appreciate it!
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.