|Date: ||Fri, 4 Mar 2005 17:49:00 -0800|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||"David L. Cassell" <cassell.david@EPAMAIL.EPA.GOV>|
|Subject: ||Re: SQL miscellany|
|Content-type: ||text/plain; charset=US-ASCII|
> I need a by group counter in SQL, one that resets as each new by group
> (defined by id) is processed. Does anyone have a technique for such? I
> aware of the monotonic() function (supposedly undocumented) and this
> provides an observation count similar to _N_ in the datastep. It's not
> to me how to reset this counter when the by group id changes. My
> I need to do a SQL join where a.id= b.id, but I need to limit this to
> first K matches (k=12). Note that the larger dataset being joined to
> sorted by id and date. I only need the first K records for each id.
As far as I have been able to determine, I cannot reset the
monotonic() function. At all. Of course, I've been wrong
twice this week (well, blatantly wrong once and misunderstood
the other time which is just as bad), so I could be aiming at
number three right here.
Also, I consider SQL functionality to be set-theoretic in nature,
and what you're doing is diamterically opposite to that concept.
So I'd recommend that you not do this using SQL. The sequential
character of this problem really makes it look like a straight
DATA step problem, and not a PROC SQL problem.
> Finally, all my reading so far has been mum as to table transposition
> SQL. Is it possible to transpose a table in Proc SQL?
It is possible, but PROC TRANSPOSE is a lot easier to use.
And you won't have to maintain all the bookkeeping either.
David Cassell, CSC
Senior computing specialist