SAS Functions

SAS Function in an MDX Query

SAS functions are functions that anyone can reference in MDX expressions. SAS functions are slightly limited in the arguments that they accept and return. Here is an MDX query that uses a SAS function called “MDY”:

 WITH MEMBER measures.mdy AS 'SAS!mdy(2,9,2003)'
SELECT {cars.members} ON 0 FROM mddbcars 
WHERE (measures.mdy)

The resulting cells look like this:

NOTE:    0.3[0]: f=15742 (u=15742.00) 
NOTE:    0.3[1]: f=15742 (u=15742.00) 
NOTE:    0.3[2]: f=15742 (u=15742.00) 
NOTE:    0.3[3]: f=15742 (u=15742.00) 
NOTE:    0.3[4]: f=15742 (u=15742.00) 
NOTE:    0.3[5]: f=15742 (u=15742.00) 
NOTE:    0.3[6]: f=15742 (u=15742.00) 
NOTE:    0.3[7]: f=15742 (u=15742.00) 
NOTE:    0.3[8]: f=15742 (u=15742.00) 
NOTE:    0.3[9]: f=15742 (u=15742.00) 
NOTE:    0.3[10]: f=15742 (u=15742.00) 
NOTE:    0.3[11]: f=15742 (u=15742.00) 
NOTE:    0.3[12]: f=15742 (u=15742.00) 
NOTE:    0.3[13]: f=15742 (u=15742.00) 
NOTE:    0.3[14]: f=15742 (u=15742.00) 
NOTE:    0.3[15]: f=15742 (u=15742.00)

In order to gain access to a SAS function library and before you can use a SAS function in a query, you must define or open the library for the current session. To do this, apply the USE statement at the beginning of your MDX query:

USE
LIBRARY "SAS"

SAS Functions Available for Use in MDX Expressions

Function	Description	Argument
DATE	Returns the current date in SAS date format.	(none)
DATEJUL	Converts a Julian date to a SAS date value.	«julian-date»
DATEPART	Returns a SAS date value that corresponds to the date portion of a SAS datetime value .	«SAS datetime»
DATETIME	Returns the current data and time in SAS datetime format.	(none)
DAY	Returns an integer that represents the day of the month from a SAS date value.	«SAS date»
DHMS	Returns a SAS datetime value from a numeric expression that represents the date, hour, minute, and second.	«SAS date», «hour», «minute», «second»
HMS	Returns a SAS time value from a numeric expression that represents the hour, minute, and second.	«hour», «minute», «second»
HOUR	Returns a numeric value that represents the hour from a SAS time or datetime value.	«SAS time» \| «SAS datetime»
IN	Returns TRUE if the first expression is contained in the list of expressions that start from the second parameter to the end of the parameters provided; otherwise, FALSE.	«expression», «expression1», . . ., «expressionN»
JULDATE	Converts a SAS date value to a numeric value that represents a Julian date.	«SAS date»
JULDATE7	Converts a SAS date value to a numeric value that represents a Julian date with the year represented in 4 digits.	«SAS date»
LEFT	Returns the argument with leading blanks moved to the end of the value; the argument's length does not change.	«argument»
MDY	Returns a SAS date value from numeric expressions that represent the month, day, and year.	«month», «day», «year»
MINUTE	Returns a numeric value that represents the minute from a SAS time or datetime value.	«SAS time» \| «SAS datetime»
MONTH	Returns a numeric value that represents the month from a SAS time.	«SAS date»
QTR	Returns a value of 1, 2, 3, or 4 from a SAS date value to indicate the quarter of the year during which the SAS date value falls.	«SAS date»
RIGHT	Returns the argument with trailing blanks moved to the beginning of the value; the argument's length does not change.	«argument»
ROUND	Rounds the first argument to the nearest multiple of the second argument, or to the nearest integer when the second argument is omitted.	(argument <,rounding-unit>)
SECOND	Returns a numeric value that represents the second from a SAS time or datetime value.	«SAS time» \| «SAS datetime»
SUBSTR	Returns a portion of the string expression argument, starting at the index position and returning up to “n” characters. If “n” is not specified, then the rest of the string is returned.	«argument», «position» <, «n»>
TIME	Returns the current time in SAS time format.	(none)
TIMEPART	Returns a SAS time value that corresponds to the time portion of a SAS datetime value.	«SAS datetime»
TODAY	Returns the current date in SAS date format.	(none)
TRIM	Returns the argument with the trailing blanks removed; if the argument contains all blanks, then the result is a string with a single blank .	«argument»
TRIMN	Returns the argument with the trailing blanks removed; if the argument contains all blanks, then the result is a null string.	«argument»
TRUNC	Truncates a numeric value to a specified length.	(number,length)
UPCASE	Returns the argument with all lowercase characters converted to uppercase characters.	«argument»
WEEKDAY	Returns an integer that represents the day of the week, where 1 = Sunday, 2 = Monday, . . ., 7 = Saturday, from a SAS date value.	«SAS date»
YEAR	Returns a numeric value that represents the month from a SAS time.	«SAS date»
YYQ	Returns a SAS date value that corresponds to the first day of the specified quarter.	«year», «quarter»

Function Arguments and Return Types

Currently only floating-point (double) arguments, character string arguments, and return values are supported. There is no limit to the number of arguments. The promotion of arguments from MDX types to SAS data types is automatically performed when there is a difference between the two types.

Numeric Precision

Floating-point Representation

To store numbers of large magnitude and to perform computations that require many digits of precision to the right of the decimal point, the SAS OLAP Server stores all numeric values as floating-point representation. Floating-point representation is an implementation of scientific notation, in which numbers are represented as numbers between 0 and 1 times a power of 10.

In most situations, the way the SAS OLAP Server stores numeric values does not affect you as a user. However, floating-point representation can account for anomalies that you might notice in MDX numeric expressions. This section identifies the types of problems that can occur and how you can anticipate and avoid them.

Magnitude versus Precision

Floating-point representation allows for numbers of very large magnitude (such as 2³⁰) and high degrees of precision (many digits to the right of the decimal place). However, operating systems differ on how much precision and how much magnitude to allow.

Whether magnitude or precision is more important depends on the characteristics of your data. For example, if you are working with engineering data, very large numbers might be needed and magnitude will probably be more important. However, if you are working with financial data where every digit is important, but the number of digits is not great, then precision is more important. Most often, applications that are created with the SAS OLAP Server need a moderate amount of both magnitude and precision, which is handled well by floating-point representation.

Computational Considerations of Fractions

Regardless of how much precision is available, there is still the problem that some numbers cannot be represented exactly. For example, the fraction 1/3 cannot be rendered exactly in floating-point representation. Likewise, .1 cannot be rendered exactly in a base 2 or base 16 representation, so it also cannot be accurately rendered in floating-point representation. This lack of precision is aggravated by arithmetic operations. Consider the following example:

((10 * .1) = 1)

This expression might not always return TRUE due to differences in numeric precision. However, the following expression uses the ROUND function to compensate for numeric precision and therefore will always return TRUE:

(round((10 * .1), .001) = 1)

Usually, if you are doing comparisons with fractional values, it is good practice to use the ROUND function.

Using the TRUNC Function

The TRUNC function truncates a number to a requested length and then expands the number back to full precision. The truncation and subsequent expansion duplicate the effect of storing numbers in less than full precision. So in the following example, the first expression would return FALSE and the second would return TRUE:

((1/3) = .333)

(TRUNC((1/3), 3)
= .333)

When you compare the result of a numeric expression to be equal to a specific value, such as 0, it is important that you use the TRUNC and ROUND functions to ensure that the comparison evaluates as intended.

Differences with Microsoft Analysis Services 2000

Microsoft Analysis Services 2000 (AS2K) labels external functions as user-defined functions (UDFs). Because AS2K runs only on Windows, it supports calling COM libraries (usually written in Visual Basic). Because MDX evaluation can occur on either the client or the server, Microsoft provides a means to install and use libraries on either location (due to a dual-mode OLE DB for OLAP provider, MSOLAP).

If you use a client-side function, then all the execution is on the client. The SAS OLAP Server is a thin-client system that is designed for high volume and scalability, with all evaluation done on the server. Therefore, external function libraries such as SAS functions can be installed only on the server. In addition, with the proper license, you can run a server on your own computer and install any libraries that you need.

SAS MDX Reserved Keywords

A reserved keyword should not be used to reference a dimension, hierarchy, level, or member name unless the reference is enclosed in square brackets [ ]. Otherwise, the keyword might be interpreted incorrectly.

Note: The SAS OLAP Server currently does not support the use of square brackets in cube, dimension, hierarchy, level, or member names or captions.

SAS MDX Reserved Keywords

(	DRILLDOWNMEMBER	NONEMPTYCROSSJOIN
)	DRILLDOWNMEMBERBOTTOM	NOT
*	DRILLDOWNMEMBERTOP	NULL
+	DRILLTHROUGH	ON
'	DRILLUPLEVEL	OPENINGPERIOD
-	DRILLUPMEMBER	OR
.	DROP	ORDER
/	ELSE	ORDINAL
:	EMPTY	PAGES
<	END	PARALLELPERIOD
<=	EXCEPT	PARENT
<>	EXCLUDEEMPTY	PARENT_COUNT
=	EXTRACT	PARENT_LEVEL
>	FALSE	PARENT_UNIQUE_NAME
>=	FILTER	PERIODSTODATE
{	FIRSTCHILD	POST
}	FIRSTROWSET	PREDICT
\|\|	FIRSTSIBLING	PREVMEMBER
ABSOLUTE	FONT_FLAGS	PROPERTIES
ADDCALCULATEDMEMBERS	FONT_NAME	PTD
AFTER	FONT_SIZE	PUT
AGGREGATE	FORMATTED_VALUE	QTD
ALL	FORMAT_STRING	RANGE
ALLMEMBERS	FORE_COLOR	RANK
ANCESTOR	FROM	RECURSIVE
ANCESTORS	GENERATE	RELATIVE
AND	GLOBAL	ROLLUPCHILDREN
AS	HEAD	ROOT
ASC	HIERARCHIZE	ROWS
ASCENDANTS	HIERARCHY	SCHEMA_NAME
AVG	HIERARCHY_UNIQUE_NAME	SECTIONS
AXIS	IGNORE	SELECT
BACK_COLOR	IIF	SELF
BASC	INCLUDEEMPTY	SELF_AND_AFTER
BDESC	INTERSECT	SELF_AND_BEFORE
BEFORE	IS	SELF_BEFORE_AFTER
BEFORE_AND_AFTER	ISANCESTOR	SESSION
BOTTOMCOUNT	ISEMPTY	SET
BOTTOMPERCENT	ISGENERATION	SETTOARRAY
BOTTOMSUM	ISLEAF	SETTOSTR
CALCULATIONCURRENTPASS	ISSIBLING	SIBLINGS
CALCULATIONPASSVALUE	ITEM	S OLVE_ORDER
CALL	LAG	STDDEV
CAPTION	LASTCHILD	STDDEVP
CASE	LASTPERIODS	STDEV
CATALOG_NAME	LASTSIBLING	STDEVP
CELL	LEAD	STRIPCALCULATEDMEMBERS
CELL_ORDINAL	LEAVES	STRTOMEMBER
CHAPTERS	LEVEL	STRTOSET
CHILDREN	LEVELS	STRTOTUPLE
CHILDREN_CARDINALITY	LEVEL_NUMBER	STRTOVALUE
CLOSINGPERIOD	LEVEL_UNIQUE_NAME	SUBSET
COALESCEEMPTY	LIBRARY	SUM
COLUMNS	LINKMEMBER	TAIL
CORRELATION	LINREGINTERCEPT	THEN
COUNT	LINREGPOINT	TOGGLEDRILLSTATE
COUSIN	LINREGR2	TOPCOUNT
COVARIANCE	LINREGSLOPE	TOPPERCENT
COVARIANCEN	LINREGVARIANCE	TOPSUM
CREATE	LOOKUPCUBE	TRUE
CROSSJOIN	MAX	TUPLETOSTR
CUBE_NAME	MAXROWS	UNION
CURRENT	MEDIAN	UNIQUENAME
CURRENTMEMBER	MEMBER	USE
DATAMEMBER	MEMBERS	USERNAME
DEFAULTMEMBER	MEMBERTOSTR	VALIDMEASURE
DESC	MEMBER_CAPTION	VALUE
DESCENDANTS	MEMBER_GUID	VAR
DESCRIPTION	MEMBER_NAME	VARIANCE
DIMENSION	MEMBER_ORDINAL	VARIANCEP
DIMENSIONS	MEMBER_TYPE	VARP
DIMENSION_UNIQUE_NAME	MEMBER_UNIQUE_NAME	VISUALTOTALS
DISPLAY_INFO	MIN	WHEN
DISTINCT	MTD	WHERE
DISTINCTCOUNT	NAME	WITH
DRILLDOWNLEVEL	NAMETOSET	WTD
DRILLDOWNLEVELBOTTOM	NEXTMEMBER	XOR
DRILLDOWNLEVELTOP	NON	YTD