murff@byu.edu wrote: > 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 am > aware of the monotonic() function (supposedly undocumented) and this > provides an observation count similar to _N_ in the datastep. It's not clear > to me how to reset this counter when the by group id changes. My problem is > I need to do a SQL join where a.id= b.id, but I need to limit this to the > first K matches (k=12). Note that the larger dataset being joined to in > 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 in > 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.

HTH, David -- David Cassell, CSC Cassell.David@epa.gov Senior computing specialist mathematical statistician