Assigning group ID base on specific key on a sorted dataset

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Assigning group ID base on specific key on a sorted dataset

Hi,

I am not really sure how to explain this or what term to use so excuse me on the subject Smiley Happy so i have this sample dataset below that i need to assign an incremental field (GROUP_ID), for every time it sees a 'B' status GROUP_ID ill have a +1 please see expected result for more info.

**sample data

ROW_NUM     STATUS    

1                    A

2                    A

3                    B

4                    A

5                    A

6                    A

7                    B

8                    A

9                    A

10                  B

**expected result

ROW_NUM     STATUS     GROUP_ID

1                    A               1

2                    A               1

3                    B               1

4                    A               2

5                    A               2

6                    A               2

7                    B               2

8                    A               3

9                    A               3

10                  B               3

cheers guys.


Accepted Solutions
Solution
‎09-08-2014 05:19 AM
Respected Advisor
Posts: 4,173

Re: Assigning group ID base on specific key on a sorted dataset

No need for excuses. You give us sample data, an explanation and and example of how the desired result should look like. That's all that's normally required to understand.

data have;

input ROW_NUM STATUS $;

datalines;

1 A

2 A

3 B

4 A

5 A

6 A

7 B

8 A

9 A

10 B

;

run;

data want;

  set have;

  by STATUS notsorted;

  if _n_=1 then group_id=1;

  else if first.status and status='A' then group_id+1;

run;

View solution in original post


All Replies
Solution
‎09-08-2014 05:19 AM
Respected Advisor
Posts: 4,173

Re: Assigning group ID base on specific key on a sorted dataset

No need for excuses. You give us sample data, an explanation and and example of how the desired result should look like. That's all that's normally required to understand.

data have;

input ROW_NUM STATUS $;

datalines;

1 A

2 A

3 B

4 A

5 A

6 A

7 B

8 A

9 A

10 B

;

run;

data want;

  set have;

  by STATUS notsorted;

  if _n_=1 then group_id=1;

  else if first.status and status='A' then group_id+1;

run;

New Contributor
Posts: 2

Re: Assigning group ID base on specific key on a sorted dataset

That was fast, thank you so much mate! it worked :smileygrin:

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 150 views
  • 0 likes
  • 2 in conversation