MONOTONIC function in PROC SQL
- Article History
- RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
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.
Further reading
SAS Papers
- 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.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thanks for pointing this out @Tom. This is another article ported from sasCommunity.org -- I've edited it for clarification.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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?
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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.