SAS Monotonic() function to create observation number in Proc SQL...

In SQL, there is no concept of an observation number in tables, as there is when using Data step (i.e. _N_ or a SUM statement count+1) with a SAS dataset. In a dataset, we can select data using an observation number by using firstobs= obs= or _N_.

data class;

set sashelp.class;

count+1;

run;

Proc SQL with the NUMBER option prints the row number on a report but does not store the value in the dataset.

In SAS 9 and later, the MONOTONIC() function will return the equivalent observation number that can be stored in the dataset…

proc sql;

create table class1 as

select monotonic() as rowno, *

from sashelp.class;

quit;

You can also the monotonic() to subset the dataset in the where clause to limit the number of records read / outputted to the final dataset…

proc sql;

create table class2 as

select monotonic() as rowno, *

from sashelp.class

where 10 le monotonic() le 20;

quit;