How to Get Row Numbers in SAS Proc SQL (and DO NOT Use the Undocumented MONOTONIC Function)

January 11, 2013
By

This post was kindly contributed by From a Logical Point of View » SAS - go there to comment and to read the full post.

SAS programmers are longing for row number function used in Proc SQL, like ROW_NUMBER() in Oracle SQL and it will act like data step system variable _N_. When you google this question, most likely you will get MONOTONIC() function, which might be one of the most famous undocumented features shipped by SAS. You can of course use it, but at your own risk! In SAS Usage Note 15138, it’s said:

The MONOTONIC() function is not supported in PROC SQL. Using the MONOTONIC() function in PROC SQL can cause missing or non-sequential values to be returned.

Here question is, why stick to the function? Actually there is a long-existing Proc SQL option (at least since SAS 9.1.3 which was my first SAS version), NUMBER to return the row numbers:

proc sql number outobs=5;
    select Species
    from sashelp.iris
    ;
quit;

See a new column "Row" was created in response to this NUMBER option.

This post was kindly contributed by From a Logical Point of View » SAS - go there to comment and to read the full post.

Tags: ,

Welcome!

SAS-X.com offers news and tutorials about the various SAS® software packages, contributed by bloggers. You are welcome to subscribe to e-mail updates, or add your SAS-blog to the site.

Sponsors







Dear readers, proc-x is looking for sponsors who would be willing to support the site in exchange for banner ads in the right sidebar of the site. If you are interested, please e-mail me at: tal.galili@gmail.com
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.