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()
In-Reply-To:  <200703080923.l289NGoY021338@listserv.uga.edu>
Content-Type: text/plain; format=flowed

DavidMcNulty@HALIFAX.CO.UK wrote:
>
>Hi Folks,
>
>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
>monotonic()?
>Explicitly: Is it safe to use monotonic() or will it produce unexpected
>results?
>
>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
>less selectively.
>
>
>Regards
>
>
>Dave

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
might
fail.  I don't know whether this is a real concern or just paranoia.  Howard
or Sig may be able to clarify this.

HTH,
David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330

_________________________________________________________________
Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
Intro*Terms
https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117