This tutorial explains various ways to add rows numbers in SAS, along with examples.
In SAS, we can use the automatic variable _N_ to generate row numbers.
data want; rowNo = _N_; set sashelp.class; run;
We have created a new variable called "rowNo" that stores row numbers in the new dataset named "want".
We can use the monotonic() function in PROC SQL to generate row numbers in SAS.
proc sql noprint; create table want as select monotonic() as rowNo, * from sashelp.class; quit;
In Proc SQL, there is an option called NUMBER that creates a column called ROW, which is the row (or observation) number of the data.
ods listing; ods output SQL_Results=want; proc sql number; select * from sashelp.class; quit;
The limitation of this method is that it does not work when you don't want to print the dataset.
Suppose you want to generate row numbers by one or more groups. In this case, we can use the FIRST.variable to initialize the row numbers to 1 for the first observation within each category of a group. For the other observations within the same category, the row number is incremented by 1.
First we need to sort the dataset by the categorical variable using PROC SORT.
/* Step 1: Sort Dataset by Sex (categorical variable)*/
proc sort data=sashelp.class out=class;
by sex;
run;
/* Step 2: Add row number by Sex*/
data want;
set class;
by sex;
if first.sex then rowNo=1;
else rowNo+1;
run;
The output dataset has a new variable called "rowNo" that contains the row number within each category.
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Share Share Tweet