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

    Posted on June 18, 2012 by in Functions, Procedures, SAS

    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

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    View all articles by

    Email : [email protected]

    Leave a Reply