BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michellel
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

10 REPLIES 10
LinusH
Tourmaline | Level 20
By id count;
If first.count then seq_id+1;
Data never sleeps
michellel
Calcite | Level 5

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.

Astounding
PROC Star

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;

michellel
Calcite | Level 5

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.

 

PGStats
Opal | Level 21

Very close @Astounding Smiley Happy

 

by id count descending;

PG
Astounding
PROC Star

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.

michellel
Calcite | Level 5
Thanks. It works with
by id descending count;
michellel
Calcite | Level 5
Thanks! It works!
LinusH
Tourmaline | Level 20
I think that my code would work if you add DESCENDING.
Data never sleeps
michellel
Calcite | Level 5
Thanks! It works now.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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