Date:         Mon, 18 Jun 2007 17:04:31 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Is there _N_ in SQL?
Comments: To: "Wang,Jue" <JWANG@travelers.com>
In-Reply-To:  <0C094BFB78FEBC42B1BF98C83F49DB849629@TEEXB0VD.prod.travp.net>
Content-Type: text/plain; charset="us-ascii"

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.