Assign a meta ID to consecutive rows that share the same ID

Solved
Frequent Contributor
Posts: 109

Assign a meta ID to consecutive rows that share the same ID

Hi,

I have a datset with individual ID and group ID. A person can move from one group to the other, sometimes back to the same group. I'd like to assign another group ID to identify those with the same Group in consecutive order. Say for ID=3, the person moved from group 25 then 27, 27 and 25...., I'd like to assign GroupID2 to the first four rows as 1 (for group 25), 2 for the next two rows where group id is both 27), and 3 for the 4th row when the person return to the same Group I 25, etc.. How do I do it? Thanks in advance.

data have;

input id groupID Amount;

cards;

1 11 13

1 12 45

2 21 30

2 21 20

2 22 15

3 25 34

3 27 25

3 27 15

3 25 14

3 25 30

3 25 24

;

The data for Group 2 (3rd column) should look like this:

ID, GroupID, GroupID2, Amount:

1 11 1 13

1 12 2 45

2 21 1 30

2 21 1 20

2 22 2 15

3 25 1 34

3 27 2 25

3 27 2 15

3 25 3 14

3 25 3 30

3 25 3 24

Accepted Solutions
Solution
‎03-28-2017 04:08 PM
PROC Star
Posts: 8,163

Re: Assign a meta ID to consecutive rows that share the same ID

```data want;
set have;
by id groupID notsorted;
if first.id then groupID2=0;
if first.groupID then groupID2+1;
run;
```

Art, CEO, AnalystFinder.com

All Replies
Solution
‎03-28-2017 04:08 PM
PROC Star
Posts: 8,163

Re: Assign a meta ID to consecutive rows that share the same ID

```data want;
set have;
by id groupID notsorted;
if first.id then groupID2=0;
if first.groupID then groupID2+1;
run;
```

Art, CEO, AnalystFinder.com

Frequent Contributor
Posts: 109

Re: Assign a meta ID to consecutive rows that share the same ID

Thanks a lot. Such a clever way, and so simple and straightforward.

☑ This topic is solved.