Date: Tue, 13 Mar 2007 20:05:53 -0800
Reply-To: David L Cassell <davidlcassell@MSN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: David L Cassell <davidlcassell@MSN.COM>
Subject: Re: SQL Monotonic()
Content-Type: text/plain; format=flowed
>First I would like to thank everyone who replied to my query. There are
>a couple of suggestions that address part of the problem.
>Original question: What are the perils of using the function
>Explicitly: Is it safe to use monotonic() or will it produce unexpected
>Here are a few extracts that set my alarm bells ringing.
>SAS web site
>Monotonic() is neither documented nor supported
>SAS-l Archives 2002 Ed Heaton:
>MONOTONIC() is not documented and there are some pecularities about it,
>so you should be careful that you get what you want.
>SAS-L Archives 2002 Kevin Myers
>Basically, if you know what you're doing, you can generally figure out a
>way to use monotonic() in an SQL query to return a distinct value for
>each row. But you should be extremely careful before doing anything
>where you might count on the values that are returned to be contiguous
>or ascending from one row to the next in the final result. That will
>probably become even more apparant in the future than it is now, as SAS
>begins to employ more multi-tasking/multi-threading.
>SAS-l Archives 2002 Sigurd Hermansen:
>I've posted a number of examples of how you can use MONOTONIC()
>selectively to convert implicit sequences in SAS datasets to explicit
>sequence numbers. Although MONOTONIC() has multiple arguments, the SI
>birdie who sent documentation to me advised against using the function
I see a number of really good replies, but I didn't see anyone bringing up
a key point:
Since it is undocumented, there is no way to depend on it re-setting to
start at 1 every time within a single PROC SQL step. I seem to recall that
Ian or Howard came up with an example a year or two ago that demonstrated
this quirk. Or was it Sig? It wasn't me.
As a result, I would not want to depend on MONOTONIC() generating
a sequence starting at 1 for all your in-line queries, so that your join
fail. I don't know whether this is a real concern or just paranoia. Howard
or Sig may be able to clarify this.
David L. Cassell
3115 NW Norwood Pl.
Corvallis OR 97330
Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.