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
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
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
Thanks a lot. Such a clever way, and so simple and straightforward.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: