78 users online | 78 Guests and 0 Registered

How can I create a row count variable in DATA step and PROC SQL?

There is an automatic variable generated by the system in the PDV when processing DATA step syntax which will in many circumstances provide the necessary information.  The _n_ is a numeric variable which increments on each iteration of the DATA step during execution.

You would be forgiven however if you had never observed this variable as it is automatically dropped from any dataset generated.  Even if you endeavour to explicitly KEEP the variable, it will still not be included.

The code:

data class (keep = name _n_) ;
  set sashelp.class ;
run ;

generates the following WARNING in the LOG:

WARNING: The variable _n_ in the DROP, KEEP, or RENAME list has never been referenced.

To include this iteration count it must be assigned to another variable, e.g.

data class ;
  set sashelp.class ;
    obs = _n_ ;
run ;

Unfortunately the _n_ variable is not available in PROC SQL, so it is necessary to (ab)use a function to give us a similar result:

proc sql noprint ;
  create table class as
  select  c.*
         ,monotonic() as obs
  from sashelp.class as c
quit ;

The monotonic function here simply recording 'preserved order' however strictly speaking it is not supported in SAS.

Alan D Rudland
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.

Records in this category