The MONOTONIC function is an undocumented SAS function. It needs no input parameters and if any input parameters are provided, these are ignored. The function counts the number of times it is called and returns an ascending sequence of integers, starting at 1. The function can be used in an expression and is syntactically valid in both a DATA Step and an SQL procedure. However, it may produce unexpected results in some circumstances.
variable = MONOTONIC() ;
The MONOTONIC function is often presented as a means of adding sequence numbers to table rows. Several papers present it as a solution to this programming problem. However, the function is undocumented and has some limitations, including:
These limitations constrain where and when the MONOTONIC function can be used appropriately.
In a DATA step, one can use the automatic variable _N_, which counts the number of iterations of the DATA step. Or one can use a RETAIN statement to declare a counting variable that is incremented programmatically under given conditions.
When using the OPEN function in a DATA step with FETCHOBS, the CUROBS function can return the current observation number.
This article was originally published by Cameron on sasCommunity.org.
How exactly are you proposing to use the CUROBS() function? That is something that you use with OPEN() and FECTHOBS() instead of with SET/MERGE or FROM.
Thanks for pointing this out @Tom. This is another article ported from sasCommunity.org -- I've edited it for clarification.
Here's an sql program that uses monotonic to add memnum to a list of data set names.
*name: make-list-memnames-sql.sas;
PROC sql noprint;
create table list_memnames(label = "&libname") as
select libname,
monotonic() as memnum label='member number',
memname, nobs, nvar as nvars, memlabel
from dictionary.tables
where libname eq "%upcase(&libname)"
and memtype eq 'DATA';
quit;
%put trace make-list-memnames-sql ending;
*name: make-list-memnames-sql-test.sas;
%let libname = sashelp;
%include 'make-list-memnames-sql.sas';
proc print;
run;
here's the listing:
Obs libname memnum memname nobs nvars memlabel
1 SASHELP 1 AACOMP 1544 4
2 SASHELP 2 AARFM 61 4
3 SASHELP 3 ADSMSG 426 6
4 SASHELP 4 AFMSG 1090 6
5 SASHELP 5 AIR 144 2 airline data (monthly: JAN49-DEC60)
6 SASHELP 6 APPLIANC 156 25 Sales time series for 24 appliances by cycle
This is from the paper
Advanced Programming Concepts: History of the List Processing and Cardinality Ratio Memes
published in SESUG.2017
http://www.lexjansen.com/sesug/2017/CC-98.pdf
Ron Fehd macro maven
Dear SAS Development Teams,
Why create a very useful function like "monotonic()", but give it a non-standard name? It's like your development team isn't fluent in SQL.
Standard Oracle, Microsoft, Teradata, MySQL etc. has two functions called "row_number()" and "rank()" that do exactly the same thing. (Row_number, and rank handle conditions where there are ducplicate attributes in a different and useful way.)
Lower the barriers to entry. I think a big advantage of using PROC SQL is that everybody who can read SQL can understand and support the program. The arcane/argo nature of SAS makes it harder than necessary to use. Use standard naming conventions, please?
The main reason that ROW_NUMBER() and RANK() functions have not been added is that they require implementing the whole concept of windowing functions that are NOT part of the SQL standard that PROC SQL implements.
SAS added the MONOTONIC() function BEFORE that definition of windowing functions for the SQL language was even created.
To implement ROW_NUMBER() SAS should create a flavor of SQL using a more recent standard. They could either do it as a new PROC or perhaps as an option in PROC SQL.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.