serial number for the same rows

[Smitha9]

Hi,

I have a dataset:

ID start

1   10

1   20

1  30

2   40

2  20

2   60

3   10

3   12

3    15

I want to create a variable as below:

ID SNO start

1   1_1    10

1   1_2    20

1   1_3   30

2   2_1   40

2   2_2   20

2   2_3   60

3   3_1   10

3   3_2   12

3   3_3   15

thank you in advance

[Kurt_Bremser · Accepted solution]

data want;
set have;
by id;
if first.id
then counter = 1;
else counter + 1;
sno = catx('_',id,counter);
drop counter;
run;

[ballardw]

You are creating additional, likely unneeded complexity by including the underscore since that forces this to be a CHARACTER variable. With that construction your SNO will not sort properly if there are more than 9 rows for each Id value. 1_10 will sort before 1_2 in general. So not a nice serial number.

data want;
   set have;
   by id;
   retain counter;
   if first.id then counter=1;
   else counter+1;
   length sno $ 15;
   sno = catx('_',id,counter);
   drop counter;
run;