topic Re: Ordering Months Starting with September in SAS Programming

Ordering Months

emkotnik — Fri, 02 Mar 2018 17:52:29 GMT

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;

Re: Ordering Months Starting with September

Reeza — Fri, 02 Mar 2018 17:12:43 GMT

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'));

Re: Ordering Months Starting with September

emkotnik — Fri, 02 Mar 2018 17:19:29 GMT

No, Month wasn't a SAS date, it was a character variable in the original dataset.

Re: Ordering Months Starting with September

Reeza — Fri, 02 Mar 2018 17:24:20 GMT

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;

Re: Ordering Months Starting with September

Reeza — Fri, 02 Mar 2018 17:25:38 GMT

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.

Re: Ordering Months Starting with September

emkotnik — Fri, 02 Mar 2018 17:47:27 GMT

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.

Re: Ordering Months Starting with September

Astounding — Fri, 02 Mar 2018 17:51:20 GMT

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.

Re: Ordering Months

mkeintz — Fri, 02 Mar 2018 21:54:40 GMT

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;

The CATS function will concatenate both numeric and character values, generating strings like "01OCT1996", which is then converted into a sas date value via the INPUT function.
Snowyear is then generated by
1. Using INTNX to get the last date in the time-span known as "YEAR.6" (years beginning in June, and ending in May.
  
  So INTNX('YEAR.6','01OCT1994'D,0,'E') says to add zero YEAR.6 spans to 01oct1994 and get the endding date in that span (that's the 'E' in the function) - resulting in 31may1995
2. Then use the YEAR function to get snowyear = 1995
proc tabulate then just organizes the table per your desired layout. I should add that the rows in proc tabulate are in ascending chronological order, even thought the "format date monname3." shows the 3-letter month abbreviations.

Re: Ordering Months Starting with September

emkotnik — Sat, 03 Mar 2018 01:46:39 GMT

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.

Re: Ordering Months

emkotnik — Sat, 03 Mar 2018 02:00:12 GMT

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!

Re: Ordering Months

mkeintz — Sat, 03 Mar 2018 02:03:14 GMT

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.

Re: Ordering Months

emkotnik — Sat, 03 Mar 2018 02:12:33 GMT

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!