Sometimes you need a column in your data set that indicates the row number. This might be useful as an artificial identifier, a rank, or something else. In this article, we explain two ways how to add row numbers in SAS.
Adding Row Numbers with _N_ in a Data Step
The first option to create a column with row numbers is using the automatic _N_ variable. SAS processes input data row by row and the automatic _N_variable stores the current row number. This variable is particularly useful for processing the first or last row. However, since _N_ is a variable you can also use it to add a column with row numbers.
Adding Row Number with monotonic() in PROC SQL
The automatic _N_ variable can only be used in data steps. However, you can use the monotonic() function in PROC SQL. The monotonic function is undocumented and generates sequential numbers.
Example: Adding Row Numbers
In the following example code, we demonstrate how to apply both options of how to add row numbers. First, we create a data set and then we add the row numbers. The three images at the end show the original data set and the two data set with the new column.
/* CREATE A DATA SET */ data work.examscores; input name $ score; datalines; Jim 80 Maria 85 Bob 50 Cris 70 John 95 Kate 95 George 80 Susan 75 Joe 55 Jennifer 75 Lu 60 Antonio 85 ; run; /* ADD ROW NUMBER (I) */ data work.examscores_I; set work.examscores; rownumber_I = _N_; run; /* ADD ROW NUMBER (II) */ proc sql; create table work.examscores_II as select *, monotonic() as rownumber_II from work.examscores; quit;
On this page you find other useful How To’s discussed on this website.