A many-to-one format applied to the level(s) of a dimension in an OLAP 
cube can result in the display of the values in an unexpected order. 
The summary objects used by PROC OLAP, as well as by PROC MEANS and 
PROC SUMMARY, exhibit a particular sorting behavior when using many to 
one formats. By many-to-one formats, we mean that there could be more 
than one row in your table that maps to one formatted value. The most 
common case is a Time dimension. In this case, the values of the time 
dimension might not display in chronological order. For example, with 
the MONNAME format, which writes date values as the name of the month, 
you could have up to 31 date values in your table that map to the 
formatted string "Jan" or "January".

If your formatted values are actually a range of dates but are displayed
as only a single value, as they would be with a date format such as
MONNAME, the sort order will be determined by the first "bin" of values
encountered by the summary object. For example, if your first year only
has the month April, then the sort order will begin with April. The next
bin's values will be appended to this order, until all bins have been
loaded.

Consider the following data example using the table SASHELP.PRDSALE. In
this table, there is the column YEAR (values of 1993 and 1994) and the
column MONTH (SAS date values formatted with MONNAME.). To demonstrate
what happens with sort order when a "bin" is missing, create a copy of
this table in which there are no rows for YEAR=1993 and MONTH=JAN. For
example,

   data prdsale_miss;
      set sashelp.prdsale;
      if year = 1993 and month( month ) = 1 then delete;
   run;

In the table PRDSALE_MISS, there are month values of 2-12 for 1993 and
month values of 1-12 for 1994. If you now execute PROC SUMMARY, which
uses the same summary object as PROC OLAP does,

   proc summary nway data=prdsale_miss;
      class month / ascending;
      output out =summary_test;
   run;
   proc print;
   run;

you will see that the rows in the output table, SUMMARY_TEST, order the
month JAN after the month DEC.

You can avoid this result by creating new columns in your input table
that are assigned the numeric value that represents the day, month,
quarter, and/or year for your SAS date value. For example, if the column
MONTH is a SAS date value, then you can define the new column, NEWMONTH,
with the MONTH function as follows:

   newMonth=month(month);

If the value of MONTH is the SAS data value of 13546 (01FEB1997), then
newMonth has a value of 2. If you want to format the values for the
NEWMONTH column, you can easily do so by creating a user-defined format
such as the following:

   proc format lib=library;
      value myMonth   1 = 'January'
                      2 = 'February'
                      3 = 'March'
                      4 = 'April'
                      5 = 'May'
                      6 = 'June'
                      7 = 'July'
                      8 = 'August'
                      9 = 'September'
                     10 = 'October'
                     11 = 'November'
                     12 = 'December';
   run;

Because the values of the new column NEWMONTH have a one to one format,
chronological order is preserved.

Operating System and Release Information