Selecting top X by group

Reply
Contributor
Posts: 29

Selecting top X by group

Hello SAS community

To make things simple, i have small dataset:

ID Customer Count

1 A 100

2 A 90

3 A 90

4 A 80

5 A 70

6 B 100

7 B 90

8 B 80

9 B 70

10 B 60

I want to create a new table that contains the largets count by group (customer and id)

This is pretty strightforward using first and output. ex.:

DATA top2;

    SET mydata;

    count + 1;

    BY customer;

    IF FIRST.customer THEN count=1;

    IF count<=2 THEN OUTPUT;

RUN;

My problem is, that i have count values that are the same. For example (in the above) for group A ID 2 and 3 have the same value. Computing a top 2 for group A should then output ID 1 2 and 3.

Any ideas for a method?

Best

Kasper

Contributor
Posts: 29

Re: Selecting top X by group

Figured a solution, though this might not be the most clever way todo it:

data top2;

set data; by customer;

counter+1;

if first.customer then count=1;

if lag1(count)=count then counter=counter-1;

if counter > 2 then delete;

run;

Grand Advisor
Posts: 9,576

Re: Selecting top X by group

data have;
input ID Customer $ Count ;
cards;
1 A 100
2 A 90
3 A 90
4 A 80
5 A 70
6 B 100
7 B 90
8 B 80
9 B 70
10 B 60
;
run;


data want;
 set have;
 by   Customer  Count notsorted;
 if first.Customer then n=0;
 n+first.Count;
 if n le 2;
run;



Another simple way is proc univariate

proc univariate data=have nextrobs=2;
by Customer;
var count;
run;

XIa Keshan

Ask a Question
Discussion stats
  • 2 replies
  • 181 views
  • 1 like
  • 2 in conversation