- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
>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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;