DATA Step, Macro, Functions and more

adding group number, use retain? or do loop?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

adding group number, use retain? or do loop?

I want to add a group number to the first dataset (see the second one). Does anyone know how?

I cannot use sort because some categories have the same name. I can only determine the group by the existing data order.

Thanks!

dataset 1:

a

a

b

b

b

c

c

d

d

d

b

b

dataset 2:

a 1

a 1

b 2

b 2

b 2

c 3

c 3

d 4

d 4

d 4

b 5

b 5


Accepted Solutions
Solution
‎11-19-2013 05:32 PM
Super User
Posts: 19,770

Re: adding group number, use retain? or do loop?

Use the NOTSORTED option of by groups with a retain statement:

data want;

set have;

by id notsorted;

retain group 0;

if first.id then group=group+1;

run;

View solution in original post


All Replies
Valued Guide
Posts: 2,177

Re: adding group number, use retain? or do loop?

set statement referring to a single dataset allows the by statement to use the NOTSORTED option

Valued Guide
Posts: 2,177

Re: adding group number, use retain? or do loop?

A simple version would be

data ds2 ;

set ds1 ;

by id notsorted ;

group_no + first.id ;

run ;

Super User
Posts: 11,343

Re: adding group number, use retain? or do loop?

If the two datasets have the exact same number of records then this may work:

data new;

     set dataset1;

     set dataset2;

run;

Solution
‎11-19-2013 05:32 PM
Super User
Posts: 19,770

Re: adding group number, use retain? or do loop?

Use the NOTSORTED option of by groups with a retain statement:

data want;

set have;

by id notsorted;

retain group 0;

if first.id then group=group+1;

run;

Frequent Contributor
Posts: 128

Re: adding group number, use retain? or do loop?

I make my own version too, thank you all for the ideas!

data want;

set test;

retain group 0;

if lag( id ) ne  id then group +1;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 319 views
  • 9 likes
  • 4 in conversation