|
Hi Florio,
The FIRST. and LAST. processing lets
you count and keep track of where you
are in each group. The RETAIN lets you
keep a value such as the counter across
observations.
data have;
input id rank;
datalines;
1 1
1 2
1 2
1 2
1 5
1 6
2 1
2 1
2 1
2 1
2 5
3 1
3 2
3 3
3 3
4 1
4 2
4 3
5 1
5 2
6 1
6 1
6 3
6 3
;
run;
data result;
set have;
by id rank;
retain counter 0;
if first.id then counter=0; * reset ;
counter+1;
if counter le 3 then output;
run;
Hope this is helpful.
Mark Terjeson
Investment Business Intelligence
Investment Management & Research
Russell Investments
253-439-2367
Russell
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Florio Arguillas
Sent: Wednesday, November 18, 2009 1:07 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Selecting the top 3 records with a twist
Hi Everyone,
I have a dataset that looks like this.
data have;
input id rank;
datalines;
1 1
1 2
1 2
1 2
1 5
1 6
2 1
2 1
2 1
2 1
2 5
3 1
3 2
3 3
3 3
4 1
4 2
4 3
5 1
5 2
6 1
6 1
6 3
6 3
;
run;
ID 1 has 6 records, ID 2 has 5 records, ID 3 has 4 records, ID 4 has 3
records, ID 5 has 2 records, and ID 6 has 4 records.
I only want to get the top 3 records for each ID. For your reference
the
value 1 for rank is considered the top rank.
Clearly there is no problem with IDs 4 and 5.
However, I have problems with IDs 1, 2 , 3, and 6 because of the ties
with
at least the 4th record for each ID.
Here's the problem:
I only want 3 records for each ID and they should be the top 3. In case
of
ties in ranks (like the rank 2's in ID 1, the 1's in ID 2, and the 3's
in
IDs 3 adn 6 , i want to randomly select from among the tied ranks so
that
I can come up with my 3 records for each case. To be clear, I want to
randomly select 2 records in ID 1 with rank 2; 3 records in ID 2 with
rank
1; and 1 record in ID 3 and 6 with rank 3. I also would like to be
able
to get the same results each time I submit the code, maybe a seed should
be
set?
Thanks for you help in advance everyone.
Best regards,
Florio
| 
