topic Re: assigning same sequence numbers within each group in SAS Data Management

assigning same sequence numbers within each group

michellel — Thu, 19 May 2016 18:15:38 GMT

Hi, I have a question about assigning same sequence numbers within each group of 2 variables. Thanks much for your help! My data looks like below.

ID	count
1	15
1	13
1	13
1	8
2	20
2	20
2	20
2	14
2	14
2	6
2	6

What I want is to create seq_ID as below.

ID	count	seq_id
1	15	1
1	13	2
1	13	2
1	8	3
2	20	1
2	20	1
2	20	1
2	14	2
2	14	2
2	6	3
2	6	3

Re: assigning same sequence numbers within each group

LinusH — Thu, 19 May 2016 18:19:45 GMT

By id count;
If first.count then seq_id+1;

Re: assigning same sequence numbers within each group

michellel — Thu, 19 May 2016 18:23:18 GMT

Thanks, but this is not working.

1, The count variable was sorted descending

2, I want to assign SAME seq number within each group of same id and same count.

Re: assigning same sequence numbers within each group

Astounding — Thu, 19 May 2016 19:52:01 GMT

Very close. Try:

data want;

set have;

by id count nosorted;

if first.id then seq_id=1;

else if first.count then seq_id+1;

run;

Re: assigning same sequence numbers within each group

michellel — Thu, 19 May 2016 19:00:18 GMT

Thanks Astounding, but it does not work at all. The error message shows

BY variables are not properly sorted on data set. I think that because count variable was sorted descending. I need keep the biggest number of count by the end.

Re: assigning same sequence numbers within each group

PGStats — Thu, 19 May 2016 19:02:16 GMT

Very close @Astounding

by id count descending;

Re: assigning same sequence numbers within each group

LinusH — Thu, 19 May 2016 19:05:38 GMT

I think that my code would work if you add DESCENDING.

Re: assigning same sequence numbers within each group

Astounding — Thu, 19 May 2016 19:34:26 GMT

Good catch. I might even go a step further:

by id count notsorted;

We really don't have enough insight into the pattern of COUNT and whether it can go both up and down.

Re: assigning same sequence numbers within each group

michellel — Fri, 20 May 2016 17:06:35 GMT

Thanks! It works!

Re: assigning same sequence numbers within each group

michellel — Fri, 20 May 2016 17:07:19 GMT

Thanks! It works now.

Re: assigning same sequence numbers within each group

michellel — Fri, 20 May 2016 17:08:09 GMT

Thanks. It works with
by id descending count;