Add window functions in SAS SQL

[ChrisNZ]

I am surprised this has not been suggested yet. Or I couldn't find it.

Some databases allow subsetting a table by taking the first n rows of each subgroups with a data set, sorted by a chosen criterion.

The query usually looks similar to this:

  select row_number() over (partition by ID order by DATE desc) RANK from table

Even more useful (luxurious even!) would be to support row-limiting clauses in this new language feature, like:

  row_number() over (partition by ID order by DATE desc) fetch first 5 rows only

or                                                       fetch first 10 percent with ties

I am aware that the very useful first. and last. operators allow something similar in a data step. That is not the point. 🙂

[Tom]

SAS implements an older ANSI SQL standard (or at least comes close).

So essentially the request of the ballot item is to update PROC SQL (or perhaps define a new PROC) that supports a more modern version of ANSI SQL standard that includes the windowing functions.

[LinusH]

Since SAS haven't adopted PROC SQL for CAS, I think it's a fair assuption PROC SQL will not be develop any further.

So the Ballot should be directed towards PROC FedSQL (based on ANSI 1999!).

My guess that an upgrade to ANSI 2003 would be a big deal.

[Patrick]

@LinusH makes a very valid point for Proc SQL. ....but would be great to have this with Proc FEDSQL

[Quentin]

Ugh, I wish SAS would be more open about which products/features are "functionally stable" and which have a development life ahead of them, rather than users having to 'read the tea leaves.'