BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AnneBouman
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

AnneBouman
Calcite | Level 5

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

Patrick
Opal | Level 21

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;

 

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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