Table of Contents
The MONOTONIC function in SAS can be used to create a monotonically increasing or decreasing sequence of numbers within a data set. It takes two arguments, a start and end value, and produces a series of numbers between them in increments of one. The MONOTONIC function can be used to fill down sequences of data, such as dates, when working with large data sets. It can also be used to assign unique numbers to each observation in a data set.
You can use the MONOTONIC() function in SAS to generate row numbers for a dataset.
Here are two common ways to use this function in practice:
Method 1: Use MONOTONIC() to Create Column of Row Numbers
/*create column called row_ID that contains row numbers*/
proc sql;
select team, monotonic() as row_ID
from my_data;
quit;
Method 2: Use MONOTONIC() to Filter Dataset by Row Numbers
/*filter where row number is less than 5*/
proc sql;
select *
from my_data
where monotonic() < 5;
quit;
The following examples show how to use each method in practice with the following dataset in SAS:
/*create dataset*/
data my_data;
input team $ points assists;
datalines;
Cavs 12 5
Cavs 14 7
Warriors 15 9
Hawks 18 9
Mavs 31 7
Mavs 32 5
Mavs 35 3
Celtics 36 9
Celtics 40 7
;
run;
/*view dataset*/
proc print data=my_data;
Example 1: Use MONOTONIC() to Create Column of Row Numbers
The following code shows how to use the MONOTONIC() function to create a new column called row_ID that contains the row number (starting from 1) for each observation in the dataset:
/*create column called row_ID that contains row numbers*/
proc sql;
select team, monotonic() as row_ID
from my_data;
quit;
Notice that a new column has been created called row_ID that contains the row number for each observation in the dataset, ranging from 1 to 9.
Example 2: Use MONOTONIC() to Filter Dataset by Row Numbers
The following code shows how to use the MONOTONIC() function to filter a dataset where the row number is less than 5:
/*filter where row number is less than 5*/
proc sql;
select *
from my_data
where monotonic() < 5;
quit;
Notice that only the first four rows from the dataset are shown since we used the MONOTONIC() function to specify that the row number must be less than 5.
The following tutorials explain how to perform other common tasks in SAS: