DATA Step, Macro, Functions and more

Ordering Months

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Ordering Months

[ Edited ]

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;

3b2.JPG


Accepted Solutions
Solution
‎03-02-2018 09:09 PM
Super User
Posts: 22,874

Re: Ordering Months Starting with September

 

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;

 

 

View solution in original post


All Replies
Super User
Posts: 22,874

Re: Ordering Months Starting with September

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

 

 

Occasional Contributor
Posts: 9

Re: Ordering Months Starting with September

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

Solution
‎03-02-2018 09:09 PM
Super User
Posts: 22,874

Re: Ordering Months Starting with September

 

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;

 

 

Super User
Posts: 22,874

Re: Ordering Months Starting with September

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. 

Occasional Contributor
Posts: 9

Re: Ordering Months Starting with September

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.

Super User
Posts: 6,543

Re: Ordering Months Starting with September

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.

Occasional Contributor
Posts: 9

Re: Ordering Months Starting with September

Posted in reply to Astounding

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.

Trusted Advisor
Posts: 1,289

Re: Ordering Months

[ Edited ]

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;

 

  1. 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.

  2. 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
  3. 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.
Occasional Contributor
Posts: 9

Re: Ordering Months

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!

Trusted Advisor
Posts: 1,289

Re: Ordering Months

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.

Occasional Contributor
Posts: 9

Re: Ordering Months

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 208 views
  • 3 likes
  • 4 in conversation