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.
Additional Resources
The following tutorials explain how to perform other common tasks in SAS:
SAS: How to Use the WHERE Operator in PROC SQL
SAS: How to Use the IN Operator in PROC SQL
SAS: How to Use UPDATE Within PROC SQL
SAS: How to Use CONTAINS in PROC SQL