BookmarkSubscribeRSS Feed

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

 

 

 

 

14 Comments
RW9
Diamond | Level 26
Diamond | Level 26

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
Tourmaline | Level 20

>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
Diamond | Level 26
Diamond | Level 26

- 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
Community Manager

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
Tourmaline | Level 20

Thanks Chris

Oligolas
Barite | Level 11

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
Barite | Level 11
ChrisNZ
Tourmaline | Level 20

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

Oligolas
Barite | Level 11

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

JRoman
Obsidian | Level 7

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.