Feb 10, 2013

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;
sas monotonic
Proc SQL with the NUMBER option prints the row number on a report but does not store the value in the dataset.
sas monotonic
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;
sas monotonic
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;
sas monotonic

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.