I was using Enterprise Guide the other day to merge some data into a single table and I needed to generate a unique sequence for a derived column.
I asked around for the best option and was told that using ‘_n_’in a datastep would do i, but of course this would have to be a custom code node, and I was using the Enterprise Guide filter and query wizard, which meant I would have to create a second step within my process flow.
No biggy, but within Oracle I would have just called the Sequence function and all would have been good,
Then our resident SAS Geek (note the removal of Uber) mentioned that I could use ‘Monotonic()’. So I created a new computed column in the query, added Monotonic() as the expression and Bob’s you uncle (or even Aunt these days) EG produced a unique sequence in the new column.
The Monotonic() function is apparently experimental in SAS 9.1. But was mentioned in a SUGI paper : Helpful Undocumented Features in SAS
And according to Wikipedia it is:
“In mathematics, a monotonic function (or monotone function) is a function which preserves the given order.”
I said at the time that it wasn’t an absolutely reliable function, and I have found a post (and old one!) in SAS-L, from Sigmund Hermansen:
“I believe that this is the same problem that I reported a number of months ago on SAS-L. The undocumented MONOTONIC() function does not appear in the documentation for a reason. If invoked in a CREATE VIEW statement or an in-line view, it seems that the MAX() and MIN() functions do not recognize its calculated value. If instantiated in a prior CREATE TABLE or a Data step, the MAX() etc. functions do recognize it.
As you know, assuming ordering by row ID’s has its dangers in SQL queries. I usually attribute the need to fix a row order as a DB design failure. Even so, it often becomes a practical necessity.”
I agree with what he says – but in your case it worked as intended. If all you need to do is create some sort of identity column, it seems the perfect solution. If the query involves an ‘order by’, the target will not have the monotonic column in numerical order, but its contents *will* be unique.
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.