We’re smarter together. Learn from this collection of community knowledge and add your expertise.

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

by Respected Advisor on ‎11-18-2015 08:14 AM (19,974 Views)

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;

 

Comments
by Super User
on ‎11-18-2015 08:40 AM - last edited on ‎11-18-2015 08:57 AM by Community Manager

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.

by Community Manager
on ‎11-18-2015 09:04 AM

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.

by Occasional Contributor Rikard
on ‎11-19-2015 06:38 AM

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 :smileyhappy:). 

 

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;

 

by Community Manager
on ‎11-19-2015 07:54 AM

@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.

row.png

by Valued Guide
‎11-24-2015 10:53 AM - edited ‎11-24-2015 10:55 AM

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