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. 🙂

[RW9]

I believe the reason is that SAS SQL supports only ANSI SQL.  The items you mention are not ANSI SQL, and are database specific, and each database has its own way of doing what you present, or methodlogy.  These are of course available in Pass Through as the database takes the load.  

The question will always be with such a thing, what database's functions and procedures do you think SAS should implement?  Because I am sure that half a dozen people will mention at least half a dozen databases, then they would need to support multiple systems procedure and functions (mostly proprietary too, so licence cost).  Its not just as simple as implementing something, need permission, need to maintain, need to support etc.

Don't get me wrong, there is some code from a few databases which would be great to have, I just can't see it happening with the amount of effort needed, costs etc.

[ChrisNZ]

>I believe the reason is that SAS SQL supports only ANSI SQL

Well not quite, SAS has its own SQL extensions such as the format= keyword or the data set options.

>  I just can't see it happening with the amount of effort needed, costs etc.

That's true of any suggestion made here.

Anyway, not holding my breath, very few of the ballot items ever make it to the product. Since this extension would definitely be a valuable improvement, it now has a warm cozy place in the ballot pages where I reckon it belongs, and where it can gather dust.

[RW9]

- Well not quite, SAS has its own SQL extensions such as the format= keyword or the data set options.

This is actually provided at the datastep layer, not the SQL layer.  But I see your point, they could create a SAS specific variety of this, much like the did with Monotonic() (if that's out of development now?) rather than try to re-implement a specific database function (which would require licenses and a lot of resource).  I could see it being quite helpful for those who SQL a lot.

[ChrisHemedinger]

PROC FEDSQL supports an updated ANSI standard (see the doc) and some additional SQL features -- but it does not support window functions.  I know that others have asked for this, but I'll have to check whether it's under consideration.

[ChrisNZ]

Thanks Chris

[Oligolas]

Hi,

just found this workaround which may work for you:

"the Pass-Though facility offers the ability to use the WHERE clause and the OBS= option at the same time"

OPTIONS OBS=50;

PROC SQL NOPRINT;
   CONNECT TO DB2 (SSID=DB22);
   CREATE TABLE RESULTl AS SELECT *
   FROM CONNECTION TO DB2
   (SELECT * FROM PROCESS.JUL Y
   WHERE DISTRICT = 51)
   ;
QUIT;
%PUT &SQLXMSG;

[Oligolas]

Source: https://www.lexjansen.com/nesug/nesug93/NESUG93061.pdf 

[ChrisNZ]

@Oligolas Your comment is not even remotely related to the question.

[Oligolas]

Oh sorry I see... I should have read it more carefully!

[JRoman]

Regarding the comment "SAS SQL supports only ANSI SQL", it looks like window functions were added way back in ANSI SQL:2003.  The ANSI specs don't appear to be freely available, but see for example here or here for mentions of the addition of window functions in the 2003 version of the specs.

So the absence of window functions in SAS SQL is actually an example of an ANSI SQL feature that is missing in SAS.