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

Solph — Tue, 28 Mar 2017 19:26:04 GMT

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

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

art297 — Tue, 28 Mar 2017 19:38:32 GMT

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

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

Solph — Tue, 28 Mar 2017 20:08:07 GMT

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

topic Assign a meta ID to consecutive rows that share the same ID in SAS Programming

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

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

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