SAS Monotonic (would you like Gin and a slice of Lemon with that)
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.”
April 9th, 2008 at 12:13 pm
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.