Greetings!

I remember seeing something about a function that can be used in SQL
to mimic the _N_ function of the datastep, but I cannot find that
document anywhere.  Does anyone know of such a function?

Thanks!

Bruce Johnson

Hi Bruce,

select Monotonic() as myvar

Check out the many postings on SAS-L for
subtle caveats, etc.

http://listserv.uga.edu/archives/sas-l.html

In the search just enter monotonic and leave
off the parens.




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing







-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
bruce johnson
Sent: Thursday, August 04, 2005 7:54 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: _N_ in SQL?


Greetings!

I remember seeing something about a function that can be used in SQL to
mimic the _N_ function of the datastep, but I cannot find that document
anywhere.  Does anyone know of such a function?

Thanks!

Bruce Johnson

_N_ is not a function. It's an automatic variable.

You are thinking of MONOTONIC().

It's undocumented, but see the archives for discussions and caveats (many!).

On Thu, 4 Aug 2005 09:53:52 -0500, bruce johnson <chimanbj@GMAIL.COM> wrote:

>Greetings!
>
>I remember seeing something about a function that can be used in SQL
>to mimic the _N_ function of the datastep, but I cannot find that
>document anywhere.  Does anyone know of such a function?
>
>Thanks!
>
>Bruce Johnson

Bruce:
You are looking for documentation of the undocumented MONOTONIC() SAS
SQL function. You may not want to go there. The function remains
undocumented for a reason.

I posted this yesterday on SAS-L:
.... What you are showing suggests that the (obs=n) as well as
MONOTONIC() exports of Data step methods into SAS SQL introduce
frightening inconsistencies in SQL queries. Logic programming languages
such as SQL declare a product, not a procedure. A SQL programmer should
never have to wonder where a WHERE condition or other constraint occurs.
To keep SQL programs independent of implementation at the physical
layer, I'd recommend for problems such as Talbot's an alternative method
that puts a column of sequential numbers in a data stream as SAS reads
those data from a dataset:

   data test;
   do _n_ = 1 to 10;
      digit = mod(_n_,2);
      output;
      end;
   run;
/* (SWH) A Data step view adds a sequential number column to rows of
data as they are being read from a data source.
    Subsetting data to a subset of the sequential numbers produces
consistent results of queries with WHERE clauses. */
   data testVW/view=testVW;
      set test;
          __n=_N_;
   run;
   proc sql;
   create table d as
    select * from (select * from testVW where __n<=5)
     where digit=1;
   quit;
   proc sql;
   create table e as
    select * from (select * from testVW where __n<=5)
     where digit in (select digit from test where digit=1);
   quit;

I see a requirement for a row counter as evidence of poor database
design. A sequencing variable should be incorporated into tabular data
and recognized as part of a composite key. It should not be a primary
key on its own because it does not represent a real dimension of
whatever entity the tabular data represent.
Sig

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of bruce johnson
Sent: Thursday, August 04, 2005 10:54 AM
To: sas-l@listserv.uga.edu
Subject: _N_ in SQL?


Greetings!

I remember seeing something about a function that can be used in SQL to
mimic the _N_ function of the datastep, but I cannot find that document
anywhere.  Does anyone know of such a function?

Thanks!

Bruce Johnson

Howard's words are always worth paying attention to. As a simple
demonstration of the caveats of the monotonic() within a data step (yes, it
works in the data step too) see below:

44   data _null_ ;
45     do x = 1 to 5 ;
46       y = monotonic() ;
47       z = _n_ ;
48       put x = y = z= ;
49     end ;
50   run ;

x=1 y=1 z=1
x=2 y=2 z=1
x=3 y=3 z=1
x=4 y=4 z=1
x=5 y=5 z=1

The data step above iterates only once. The manifestation of the counter of
the implicit loop is the _n_ automatic variable. The monotonic() seems to
operate differently (at least above). In the example it seems to track an
explicit do loop. As mentioned previously, there are many caveats.

Venky

On Thu, 4 Aug 2005 11:01:23 -0400, Howard Schreier <hs AT dc-sug DOT org>
<nospam@HOWLES.COM> wrote:

>_N_ is not a function. It's an automatic variable.
>
>You are thinking of MONOTONIC().
>
>It's undocumented, but see the archives for discussions and caveats
(many!).
>
>On Thu, 4 Aug 2005 09:53:52 -0500, bruce johnson <chimanbj@GMAIL.COM>
wrote:
>
>>Greetings!
>>
>>I remember seeing something about a function that can be used in SQL
>>to mimic the _N_ function of the datastep, but I cannot find that
>>document anywhere.  Does anyone know of such a function?
>>
>>Thanks!
>>
>>Bruce Johnson

&sqlobs is the automatic macro variable that works like _n_. I hope that helps.

-Nishant Dholakia

On 8/4/05, bruce johnson <chimanbj@gmail.com> wrote:
> Greetings!
>
> I remember seeing something about a function that can be used in SQL
> to mimic the _N_ function of the datastep, but I cannot find that
> document anywhere.  Does anyone know of such a function?
>
> Thanks!
>
> Bruce Johnson
>


--
Nishant H. Dholakia
607 262 0860

"its your attitude not your aptitude that determines your altitude"

So, is the answer to take the database, and add a counter variable to
it, rather than using the monotonic() function?

On 8/4/05, Sigurd Hermansen <HERMANS1@westat.com> wrote:
> Bruce:
> You are looking for documentation of the undocumented MONOTONIC() SAS
> SQL function. You may not want to go there. The function remains
> undocumented for a reason.
>
> I posted this yesterday on SAS-L:
> ... What you are showing suggests that the (obs=n) as well as
> MONOTONIC() exports of Data step methods into SAS SQL introduce
> frightening inconsistencies in SQL queries. Logic programming languages
> such as SQL declare a product, not a procedure. A SQL programmer should
> never have to wonder where a WHERE condition or other constraint occurs.
> To keep SQL programs independent of implementation at the physical
> layer, I'd recommend for problems such as Talbot's an alternative method
> that puts a column of sequential numbers in a data stream as SAS reads
> those data from a dataset:
>
>   data test;
>   do _n_ = 1 to 10;
>      digit = mod(_n_,2);
>      output;
>      end;
>   run;
> /* (SWH) A Data step view adds a sequential number column to rows of
> data as they are being read from a data source.
>    Subsetting data to a subset of the sequential numbers produces
> consistent results of queries with WHERE clauses. */
>   data testVW/view=testVW;
>      set test;
>          __n=_N_;
>   run;
>   proc sql;
>   create table d as
>    select * from (select * from testVW where __n<=5)
>     where digit=1;
>   quit;
>   proc sql;
>   create table e as
>    select * from (select * from testVW where __n<=5)
>     where digit in (select digit from test where digit=1);
>   quit;
>
> I see a requirement for a row counter as evidence of poor database
> design. A sequencing variable should be incorporated into tabular data
> and recognized as part of a composite key. It should not be a primary
> key on its own because it does not represent a real dimension of
> whatever entity the tabular data represent.
> Sig
>
> -----Original Message-----
> From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
> On Behalf Of bruce johnson
> Sent: Thursday, August 04, 2005 10:54 AM
> To: sas-l@listserv.uga.edu
> Subject: _N_ in SQL?
>
>
> Greetings!
>
> I remember seeing something about a function that can be used in SQL to
> mimic the _N_ function of the datastep, but I cannot find that document
> anywhere.  Does anyone know of such a function?
>
> Thanks!
>
> Bruce Johnson
>