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,130

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

[ Edited ]

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: 7,668

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

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,130

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

[ Edited ]

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: 6,842

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

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
  • 696 views
  • 0 likes
  • 4 in conversation