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: 66
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,069

Re: assigning same sequence numbers within each group

[ Edited ]

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,254

Re: assigning same sequence numbers within each group

By id count;
If first.count then seq_id+1;
Data never sleeps
Contributor
Posts: 66

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,069

Re: assigning same sequence numbers within each group

[ Edited ]

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: 66

Re: assigning same sequence numbers within each group

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,640

Re: assigning same sequence numbers within each group

Very close @Astounding Smiley Happy

 

by id count descending;

PG
Super User
Posts: 5,069

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: 66

Re: assigning same sequence numbers within each group

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

Re: assigning same sequence numbers within each group

Thanks! It works!
Super User
Posts: 5,254

Re: assigning same sequence numbers within each group

I think that my code would work if you add DESCENDING.
Data never sleeps
Contributor
Posts: 66

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
  • 508 views
  • 2 likes
  • 4 in conversation