MONOTONIC function in PROC SQL

1 Like

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.

Syntax

variable = MONOTONIC() ;

Usage

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.

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.

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
Usage Note 39458: Adding row numbers to list tables in SAS® Web Report Studio

This article was originally published by Cameron on sasCommunity.org.

Tom · ‎07-05-2018

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.

ChrisHemedinger · ‎07-05-2018

Thanks for pointing this out @Tom. This is another article ported from sasCommunity.org -- I've edited it for clarification.

Ron_MacroMaven · ‎07-23-2018

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

cal4gORl3ndU · ‎04-10-2019

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?

Tom · ‎04-10-2019

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.