- This is a work in progress. Please feel free to contribute to this article.
The MONOTONIC function is an undocumented SAS function. It needs no input parameters and if any input parameters are provided, these are ingnored. 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 expressions and is syntatically 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:
- If the MONOTONIC function is used in an SQL procedure that aggregates data then the function may return non-sequential or missing results.
- If two different statements use the MONOTONIC function then a separate number sequence is returned for each statement.
These limitations constrain where and when the MONOTONIC function can be used appropriately.
Alternatives to the MONOTONIC function
There are alternative solutions that can be used instead of the MONOTONIC function. These alternative solutions depend on whether these need to be used in a DATA step or a SQL procedure.
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 programatically under given conditions. The CUROBS function may also provide the number of the current observation of a dataset.
In a SQL procedure
The CUROBS function may provide the number of the current observation of a dataset. Depending on what type of numbering is required the dataset may need to be pre- or post-processed to add record or observation numbers.
Tips using the MONOTONIC function
- Paper 040-29: Helpful Undocumented Features in SAS® Wei Cheng, ISIS Pharmaceuticals, Inc., Carlsbad, CA
- Paper 257-2013: Top 10 Most Powerful Functions for PROC SQL by Chao Huang and Yu Fu, Oklahoma State University
- Adding Serial Numbers to SQL Data by Howard Schreier