DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

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: 7,363

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

 

View solution in original post


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

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: 101

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.

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

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