topic Re: Assigning group ID base on specific key on a sorted dataset in SAS Programming

Assigning group ID base on specific key on a sorted dataset

nasman — Mon, 08 Sep 2014 08:54:00 GMT

Hi,

I am not really sure how to explain this or what term to use so excuse me on the subject 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.

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

Patrick — Mon, 08 Sep 2014 09:19:22 GMT

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;

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

nasman — Mon, 08 Sep 2014 09:43:28 GMT

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