Enumerating within by group

[KPCklebspn]

Hi all,

My dataset:

ID1 ID2

1     a

1     a

1     b

2     a

2     a

2     b

2     b

Dataset I want:

ID1 ID2 varwant

1     a         1

1     a         .

1     b         2

2     a         1

2     a         .

2     b         2

2     b         .

How do I do this? 

Thx!

[Astounding]

Here's a possibility that assumes your data set is in the right order:

data want;

set have;

by id1 id2;

if first.id1 then counter=1;

else if first.id2 then counter + 1;

if first.id2 then varwant = counter;

drop counter;

run;

Also, before dropping COUNTER, take a look at it.  Perhaps you prefer that to the VARWANT in your post.

[KPCklebspn]

Thanks so much - this works! I am just having a put of difficulty understanding the "if else" part of this.


>From my understanding, the "if first.id1 then counter=1;" flags the first observation within each id1 group (and if it's not the first observation within the id1 group, then it's a .).

But I'm not clear on what the "else if first.id2 then counter + 1;" does - would you mind explaining? Thank you so much

[Astounding]

By definition, on the first observation for an ID1, both first.id1 and first.id2 are true.  Without "else" therefore, the first COUNTER for an ID1 would get incremented twice, ending up with a value of 2 instead of 1.  "Else" prevents incrementing COUNTER that second time, until the next time that a new ID2 appears.

[Ksharp]

data have;
input ID1 ID2 $;
cards;
1     a
1     a
1     b
2     a
2     a
2     b
2     b
;

data want;
set have;
by id1 id2 notsorted;
if first.id1 then n=0;
n+first.id2;
want=n;
if not first.id2 then call missing(want);
drop n;
run;