topic Re: How to add a rownumber to a table in SAS code? in SAS Enterprise Guide

How to add a rownumber to a table in SAS code?

AnneBouman — Thu, 12 Nov 2015 09:28:09 GMT

I want to add a rownumber to a table. In sql you can do a partition by to add a rownumber and start counting the rownumber on several BY variables.

I have found this code:

Data myTable;

Set myTable;

By Var1;

if first. Var1 then n=1;

else n+1;

run;

How can I add a rownumber using more than 1 variable?

Re: How to add a rownumber to a table in SAS code?

RW9 — Thu, 12 Nov 2015 09:38:43 GMT

Your code doesn't seem to be correct. There is a space after the period in first., which shouldn't work, and you have no retain statement. Can you also post example test data (as a datastep, just a couple of lines of data) and required output, so we can see clearly what you want. At a guess:

data mytable;
  set mytable;
  by var1 var2;
  retain n;
  if first.var2 then n=1;
  else n=n+1;
run;

This will create an n variable as a count within var2. Note needs to be sorted first.

Re: How to add a rownumber to a table in SAS code?

Patrick — Wed, 18 Nov 2015 12:53:34 GMT

Not sure that I understand what you mean by "using more than 1 variable".

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;

Re: How to add a rownumber to a table in SAS code?

Tom — Thu, 12 Nov 2015 21:26:17 GMT

SAS will generate FIRST. and LAST. flag variables for every variable in your BY statement.

If it takes more than one variable to uniquely identify a group then you would just want to use flags for the last of them.

For example this program will generate a unique id that increments for each new by group and also an sequence number that starts over at one within each group.

data want ; 
  set have ;
  by state city ;
  city_id + first.city ;
  if first.city then seqno=0;
  seqno+1;
run;

Re: How to add a rownumber to a table in SAS code?

AnneBouman — Wed, 18 Nov 2015 10:19:44 GMT

Thanks for your answer. This is exactly what I needed.