BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
emkotnik
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

 

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

11 REPLIES 11
Reeza
Super User

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

 

 

emkotnik
Calcite | Level 5

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

Reeza
Super User

 

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;

 

 

Reeza
Super User

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. 

emkotnik
Calcite | Level 5

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.

Astounding
PROC Star

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.

emkotnik
Calcite | Level 5

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.

mkeintz
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
emkotnik
Calcite | Level 5

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!

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
emkotnik
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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