Monotonic (part duex)
Kevin Myers posted a comment on our monotonic post, which provided a lot more detail on the monotonic function (thanks Kevin) so I thought I would post them as an article.
Kevin says….
Here are some very preliminary unpublished docs that were put together when MONOTONIC was added in SAS 8.2. This has not received any official testing/verification/approval by SAS Institute personnel:
MONOTONIC
Returns a series of monotonically increasing values
Category: Special (Is this the right category?)
Syntax
Arguments
Details
Examples
Example 1: Creating Row Identifiers for a Simple SQL Query
Example 2: Generating Distinct Streams of Values
Example 3: Influencing MONOTONIC Evaluation with Arguments
See Also
Syntax
MONOTONIC(>>)
Arguments
argument
is any valid numeric or character expression. No arguments are required, but any number of arguments may be provided.
Details
The MONOTONIC function returns the series of monotonically increasing integers (1, 2, 3, …). Each time the function is evaluated, it returns the next integer from the series. It is particularly useful for returning a series of values that may be used for purposes such as dynamically generating row identifiers or counters in PROC SQL. See Generating Row Identifiers for a Simple SQL Query.
To generate a series of values that does not start with 1 or does not increment by 1, you may wish to create an expression that applies an offset and/or a multiplier to the values that are returned from the MONOTONIC function.
Each separate use of the MONTONIC function within a data step or SQL query will return a separate series of values. If values from the same MONOTONIC series are required at multiple points within the same data step or SQL query, then a variable should be used to store the result of a single call to the MONOTONIC function, and that variable can then be used to provide the additional values where they are required. See Generating Distinct Streams of Values.
Arguments that are passed to the MONOTONIC function have no direct impact on the series of values that is returned. Instead, these arguments may be optionally provided in order to serve as hints for PROC SQL to help influence when the MONOTONIC function will be evaluated during query processing. One must be cautious when using MONOTONIC in a complex PROC SQL query, because join processing, subsetting, and query optimization may have results that are difficult to predict. See Influencing MONOTONIC Evaluation with Arguments.
Examples
Example 1: Generating Row Identifiers for a Simple SQL Query
The following program illustrates generating a unique identifier for each row that is returned as the result of a simple SQL query:
data test;
do x=1 to 5;
output;
end;
run;
proc sql;
select *, monotonic() as rowid from test
having x ne 3;
quit;
The SAS System 10:00 Thursday,
August 5, 1999 10
x rowid
ffffffffffffffffff
1 1
2 2
4 4
5 5
Note in the output from the above example that the value of rowid skips from 2 to 4. This is because the data row for which the rowid value of 3 was generated by the MONOTONIC function was deleted by the HAVING clause after the function had already been evaluated for that row. Using a simple WHERE clause such as WHERE X NE 3 would not produce the same result, because the third data row would have been skipped entirely, and the MONOTONIC function would not have been evaluated at all for that row. This shows that when it is important for values resulting from use of the MONOTONIC function in an SQL query to maintain a constant increment with no skipped values, one must carefully consider the effects that any joins or subsetting criteria may have on the results.
Example 2: Generating distinct streams of values
The following program illustrates that independent streams of values are returned by each separate use of the MONOTONIC function. A data step is used here to illustrate this point for simplicity, but similar situations can also occur with multiple uses of MONOTONIC within a single SQL statement.
data _null_;
do x=1 to 2;
a=monotonic();
do y=1 to 2;
b=monotonic();
put x= y= a= b=;
end;
end;
run;
x=1 y=1 a=1 b=1
x=1 y=2 a=1 b=2
x=2 y=1 a=2 b=3
x=2 y=2 a=2 b=4
NOTE: DATA statement used:
real time 0.02 seconds
cpu time 0.02 seconds
Note the differing values for variables a and b which were generated by two different calls to the MONOTONIC function, one in the outer DO loop for the data step, and the other in the inner DO loop.
Example 3: Influencing MONOTONIC Evaluation with Arguments
The following example shows that it is possible to influence when evaluation of the MONOTONIC function occurs in an SQL query by providing arguments.
data test1;
do x=1 to 5;
output;
end;
run;
data test2;
do x=0 to 6 by 2;
output;
end;
run;
proc sql;
select t1.x,
monotonic() as m,
monotonic(t1.x) as m1,
monotonic(t2.x) as m2,
monotonic(t1.x,t2.x) as m12
from test1 t1, test2 t2
where t2.x=t1.x;
quit;
The SAS System 10:00 Thursday,
August 5, 1999 14
x m m1 m2 m12
ffffffffffffffffffffffffffffffffffffffffffffffff
2 1 2 2 1
4 2 4 3 2
Note in the output from the above example that providing different arguments to the MONOTONIC function calls caused them to be evaluated at different points during the query processing according to the table dependencies of the arguments, thereby resulting in different values for each call in the resulting joined data.
CAUTION: Even though it is often possible to successfully influence the evaluation order of the MONOTONIC function in a predictable manner as shown above, one must be very careful when attempting to do so. In complex queries, the SQL query optimizer may alter the apparent evaluation order to improve performance. Care should be taken when developing any SQL queries that use the MONTONIC function which also involve joins and/or subsetting criteria, to be certain that the results from using the MONOTONIC function are as intended. Furthermore, differences in query optimization between different releases of the SAS System could produce different results from use of the montonic function in the same SQL query.
