Usage Note 19651: Member values in a SAS® OLAP cube do not display in expected order
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
SAS System | SAS OLAP Server | Tru64 UNIX | 9.1 TS1M0 | |
64-bit Enabled AIX | 9.1 TS1M0 | |
OpenVMS Alpha | 9.1 TS1M0 | |
64-bit Enabled HP-UX | 9.1 TS1M0 | |
HP-UX IPF | 9.1 TS1M0 | |
Linux | 9.1 TS1M0 | |
z/OS | 9.1 TS1M0 | |
64-bit Enabled Solaris | 9.1 TS1M0 | |
Microsoft Windows 2000 Advanced Server | 9.1 TS1M0 | |
Microsoft Windows 2000 Datacenter Server | 9.1 TS1M0 | |
Microsoft Windows 2000 Professional | 9.1 TS1M0 | |
Microsoft Windows 2000 Server | 9.1 TS1M0 | |
Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M0 | |
Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M0 | |
Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M0 | |
Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M0 | |
Microsoft Windows NT Workstation | 9.1 TS1M0 | |
Microsoft Windows XP Professional | 9.1 TS1M0 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Type: | Usage Note |
Priority: | |
Topic: | Query and Reporting ==> OLAP (Online Analytical Processing) System Administration ==> Servers ==> OLAP
|
Date Modified: | 2007-03-07 11:53:49 |
Date Created: | 2007-02-28 11:57:27 |