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.
[update 2014/03/19] A reader asked how to capture such row number in a data set since the NUMBER option only affects in output. Well, if it shows up in output, we can always use ODS to retrieve it. Submit the follow codes to check which output table will be generated (which is SQL_Results):
ods trace on;
proc sql number outobs=5;
select Species
from sashelp.iris
;
quit;ods trace off;
Then use the standard ODS OUTPUT statement to get it:
ods output SQL_Results=rownumber;
proc sql number outobs=5;
select Species
from sashelp.iris
;
quit;