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

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.