SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

assigning same sequence numbers within each group

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

assigning same sequence numbers within each group

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

 


Accepted Solutions
Solution
‎05-20-2016 01:06 PM
Super User
Posts: 5,516

Re: assigning same sequence numbers within each group

[ Edited ]
Posted in reply to michellel

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;

View solution in original post


All Replies
Super User
Posts: 5,437

Re: assigning same sequence numbers within each group

Posted in reply to michellel
By id count;
If first.count then seq_id+1;
Data never sleeps
Contributor
Posts: 67

Re: assigning same sequence numbers within each group

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.

Solution
‎05-20-2016 01:06 PM
Super User
Posts: 5,516

Re: assigning same sequence numbers within each group

[ Edited ]
Posted in reply to michellel

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;

Contributor
Posts: 67

Re: assigning same sequence numbers within each group

Posted in reply to Astounding

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.

 

Respected Advisor
Posts: 4,931

Re: assigning same sequence numbers within each group

Posted in reply to Astounding

Very close @Astounding Smiley Happy

 

by id count descending;

PG
Super User
Posts: 5,516

Re: assigning same sequence numbers within each group

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.

Contributor
Posts: 67

Re: assigning same sequence numbers within each group

Thanks. It works with
by id descending count;
Contributor
Posts: 67

Re: assigning same sequence numbers within each group

Posted in reply to Astounding
Thanks! It works!
Super User
Posts: 5,437

Re: assigning same sequence numbers within each group

Posted in reply to michellel
I think that my code would work if you add DESCENDING.
Data never sleeps
Contributor
Posts: 67

Re: assigning same sequence numbers within each group

Thanks! It works now.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 518 views
  • 2 likes
  • 4 in conversation