topic serial number for the same rows in New SAS User

serial number for the same rows

Smitha9 — Tue, 08 Dec 2020 22:27:05 GMT

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

Re: serial number for the same rows

Kurt_Bremser — Tue, 08 Dec 2020 22:33:30 GMT

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

Re: serial number for the same rows

ballardw — Tue, 08 Dec 2020 22:36:01 GMT

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;