Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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?


Accepted Solutions
Solution
‎11-18-2015 07:52 AM
Respected Advisor
Posts: 4,571

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

[ Edited ]
Posted in reply to AnneBouman

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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

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

Posted in reply to AnneBouman

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.

New Contributor
Posts: 3

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

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

Solution
‎11-18-2015 07:52 AM
Respected Advisor
Posts: 4,571

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

[ Edited ]
Posted in reply to AnneBouman

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;

 

Super User
Super User
Posts: 7,860

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

Posted in reply to AnneBouman

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;
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1807 views
  • 0 likes
  • 4 in conversation