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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.