The Monotonic() Function

This is one of our archive tips that we’ve kept as its still popular. Some of the information may be out of date. Get in touch if you need more help.

The DATA step environment has an internal counter that takes the form of an automatic variable named _N_. This is because the DATA Step executes statements reading one record at a time and looping through the statements. This variable can be particularly useful when you need to perform special processing for the first/last record found, or to simply add a counter to your table.

Within the SQL environment, the concept of internal iterations does not exist as data gets read and analysed in pages rather than one at a time. But there is an undocumented function that can reproduce the behaviour of the _N_ DATA step variable. This is the monotonic() function, which can be used to generate sequential data and also to evaluate it.

proc sql;
  create table results as
  select monotonic() as sequence,
         *,
         case
          when (monotonic()=1) then 'First'
          else 'Not First'
         end as text
  from sashelp.class;
quit;

In this example the monotonic() function gets used to create a sequential number and to evaluate the first observation on the data set. Each time the monotonic() function gets called, a new sequential number will be generated.

sequence    Name       Sex    Age    Height    Weight    text


    1       Alfred      M      14     69.0      112.5    First
    2       Alice       F      13     56.5       84.0    Not First
    3       Barbara     F      13     65.3       98.0    Not First
    4       Carol       F      14     62.8      102.5    Not First
    5       Henry       M      14     63.5      102.5    Not First
    6       James       M      12     57.3       83.0    Not First
    7       Jane        F      12     59.8       84.5    Not First
    8       Janet       F      15     62.5      112.5    Not First
    9       Jeffrey     M      13     62.5       84.0    Not First
   10       John        M      12     59.0       99.5    Not First
   11       Joyce       F      11     51.3       50.5    Not First
   12       Judy        F      14     64.3       90.0    Not First
   13       Louise      F      12     56.3       77.0    Not First
   14       Mary        F      15     66.5      112.0    Not First
   15       Philip      M      16     72.0      150.0    Not First
   16       Robert      M      12     64.8      128.0    Not First
   17       Ronald      M      15     67.0      133.0    Not First
   18       Thomas      M      11     57.5       85.0    Not First
   19       William     M      15     66.5      112.0    Not First

The monotonic() function can be extremely useful when having to create or work with sequential numbers within SQL code. As it is an undocumented function it is recommended to use within simple queries.