How do I add a row number to a table in SAS code?

9 Likes

Question

I want to add a row number to a table. In some versions of SQL you can use PARTITION BY to add a row number and track that row counter over several BY group variables. In SAS, how can I add a row counter using more than 1 variable?

Answer

If it's simply about the row number then SAS has an automatic variable _N_ which gives you exactly that. If you want to store this number permanently in a new variable then it's as simple as:

data want;
  set sashelp.class;
  rownum=_n_;
run;

If you're more after creating a group identifier then something like below should work:

data want;
  set have;
  by var1 var2;  /* data set have must be sorted by var1 var2 */
  if first.var2 then group_id+1;
run;

SAS syntax "group_id+1" as used in above code retains the variable so you don't need an additional RETAIN statement.

Or if you're after a counter within a group:

data want;
  set have;
  by var1 var2;  /* data set have must be sorted by var1 var2 */
  if first.var2 then 
    do;
      group_id+1;
      counter_in_group=1;
    end;
  else counter_in_group+1;
run;

RW9 · ‎11-18-2015

Good post, it's a question which often comes up. I would also like to add a couple of points on here. The first is another method to get row number onto a dataset. I would point out that this was not my code, @Ksharp has to get credit for it:

ods output sql_results=want;
proc sql number;
  select * from sashelp.class;
quit;

The second is an SQL function - MONOTONIC(). With this function though it's necessary to point out that on a parallel process, you may not get the results expected, so be careful:

proc sql;
  select *,monotonic() as N from SASHELP.CARS;
quit;

There are specific implementations for SQL in other databases such as rownumber() over (); but as there is nothing in base SQL these aren't available to us. Would be good to have an expanded SQL library available.

ChrisHemedinger · ‎11-18-2015

Thanks for these great comments, @RW9. monotonic() is not a documented SAS function, though it has been included in Base SAS for years. As a Base SAS function you can use it in PROC SQL (as you can with any SAS function), but it's not designed for SQL. As you point out, you can get unpredictable results with it. It appears in many SAS conference papers as a trick you can use, but users should be careful.

PROC SQL NUMBER is a good approach. Here's the documentation for this option.

NUMBER | NONUMBER

specifies whether the SELECT statement includes a column called ROW, which is the row (or observation) number of the data as the rows are retrieved.

Rikard · ‎11-19-2015

The NUMBER option in PROC SQL seems to work only when printing the result but not when you want to add a row number column to a table (as the article title state ).

The code below will not add a ROW column to the table.

proc sql number;
  create table work.class as
  select * from sashelp.class;
quit;

ChrisHemedinger · ‎11-19-2015

@Rikard - great point! Fortunately, we can use ODS to output this table for us with the number intact.

ods listing;
ods output SQL_Results=outclass;
proc sql number;
 select * from sashelp.class;
quit;

This creates a table WORK.OUTCLASS with the row field as the first column.

MikeZdeb · ‎11-24-2015

Hi. Here's another idea. Using SASHELP.CLASS, add two counters: within age groups, within gender groups within age ...

proc sort data=sashelp.class out=have;
by age sex;
run;

data want;
set have;
by age sex;
group_id + first.age;
counter_in_group + 1 - (counter_in_group)*first.sex;
run;

Take a look ... http://www.sascommunity.org/wiki/Tips:Between_and_Within_Group_Counters

Mariusz__P · ‎09-19-2018

The MONOTONIC function is an undocumented SAS function. It needs no input parameters and if any input parameters are provided, these are ingnored. The function counts the number of times it is called and returns an ascending sequence of integers, starting at 1. The function can be used in an expressions and is syntatically valid in both a DATA Step and an SQL procedure. However, it may produce unexpected results in some circumstances.

http://www.sascommunity.org/wiki/MONOTONIC_function#Alternatives_to_the_MONOTONIC_function

https://communities.sas.com/t5/SAS-Communities-Library/MONOTONIC-function-in-PROC-SQL/ta-p/475752