topic Re: Enumerating within by group in SAS Data Management

Enumerating within by group

KPCklebspn — Tue, 05 Mar 2019 21:14:14 GMT

Hi all,

My dataset:

ID1 ID2

1 a

1 b

2 a

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!

Re: Enumerating within by group

Astounding — Tue, 05 Mar 2019 21:31:02 GMT

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.

Re: Enumerating within by group

KPCklebspn — Wed, 06 Mar 2019 01:30:00 GMT

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

Re: Enumerating within by group

Astounding — Wed, 06 Mar 2019 03:19:16 GMT

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.

Re: Enumerating within by group

Ksharp — Wed, 06 Mar 2019 11:33:35 GMT

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;