Date:         Tue, 19 Jun 2007 07:28:13 -0400
Reply-To:     Samuel Croker <samuel.croker@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Samuel Croker <samuel.croker@GMAIL.COM>
Subject:      Re: Is there _N_ in SQL?
In-Reply-To:  <CA8F89971ADA9F47A6C915BA2397844203714235@MAILBE2.westat.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Hi Jue,

It might not please the purists but I have found OLAP functions useful
for such things.  If you are working with a dbms there are supported
OLAP functions that do this database-side.  You must issue the query
using pass through sql since this sql will be native to the dbms and
not SAS.

http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10759/functions122.htm#i86310
http://msdn2.microsoft.com/en-us/library/ms186734(SQL.90).aspx
http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2s0/olapfunc.htm
maybe others...

Pass through utility in SAS:
http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a001626059.htm
pass through in SAS basically sends dbms specific sql to the dbms
directly and it looks something like:

    select * from connection to db2 ( dbms specific query ) by db2;

- Sam

On 6/18/07, Sigurd Hermansen <HERMANS1@westat.com> wrote:
> Jue:
> The SQL language descended from relational database access languages
> that tried to maintain a strict separation between the logical layer of
> a database system and the physical implementation layer. As such SQL
> does not recognize physical ordering of tuples (rows) in a relation
> (table). You are asking how to do something in SQL that SQL should not
> do.
>
> SQL does recognize ordering of an attribute (column variable). I've
> advised creating a virtual ordering attribute for those special cases
> where a query must do more than GROUP BY (in this problem) x,y and find
> a minimum or maximum value per group:
>
> proc sort data=a;
> by x y;
> run;
> data vwa/view=vwa;
>   set a;
>   N=_N_;
> run;
>
> None of this will work, of course, if you are trying to execute SAS
> procedures or Data steps on a DBMS server. In that situation, use the
> SQL ORDER BY clause to replace PROC SORT, then create a Data step view
> to capture the ordering of data.
> S
> -----Original Message-----
> From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
> On Behalf Of Wang,Jue
> Sent: Monday, June 18, 2007 4:14 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: RE: Is there _N_ in SQL?
>
>
> I am wondering if there any equivalent statement in SQL that does the
> following:
>
> Proc sort data=a;
> by x y;
> Run;
>
> Data a;
> Set a;
> N=_N_;
> Run;
>
> monotonic() doesn't gives the correct N within each group
>
> Thanks
>
> ========================================================================
> ======
> This communication, together with any attachments hereto or links
> contained herein, is for the sole use of the intended recipient(s) and
> may contain information that is confidential or legally protected. If
> you are not the intended recipient, you are hereby notified that any
> review, disclosure, copying, dissemination, distribution or use of this
> communication is STRICTLY PROHIBITED.  If you have received this
> communication in error, please notify the sender immediately by return
> e-mail message and delete the original and all copies of the
> communication, along with any attachments hereto or links herein, from
> your system.
>
> ========================================================================
> ======
> The Travelers e-mail system made this annotation on 06/18/07, 16:13:37.
>


--
Samuel T. Croker
Lexington, SC & Bethesda, MD