BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## Ranking Output and then grouping the remaining

I used order by to order my randomly arranged data so now I have the following data :

data have;
input name \$4. count ;
datalines;
john 100
Harr 90
Rick 80
Gini 70
kell 70
Rini 67
Holl 65
Radd 65
lola 60
;

What I want as the output is the top 5 names with the corresponding counts and then for the 6th row I want everything left to be grouped together labelled as 'Othr' and their counts summed.

john 100

Harr 90
Rick 80
Gini 70
kell 70

Othr 257

How can I achieve this?

Thanks in advance 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Ranking Output and then grouping the remaining

``````data have;
input name \$4. count ;
datalines;
john 100
Harr 90
Rick 80
Gini 70
kell 70
Rini 67
Holl 65
Radd 65
lola 60
;
data want;
set have end=lr;
if _n_>5 then s+count;
if lr then do;
count=s;
name='other';
end;
if lr or _n_<=5 then output;
drop s;
run;``````
2 REPLIES 2
Tourmaline | Level 20

## Re: Ranking Output and then grouping the remaining

``````data have;
input name \$4. count ;
datalines;
john 100
Harr 90
Rick 80
Gini 70
kell 70
Rini 67
Holl 65
Radd 65
lola 60
;
data want;
set have end=lr;
if _n_>5 then s+count;
if lr then do;
count=s;
name='other';
end;
if lr or _n_<=5 then output;
drop s;
run;``````
Obsidian | Level 7

## Re: Ranking Output and then grouping the remaining

Thanks a lot @novinosrin!!

Discussion stats
• 2 replies
• 505 views
• 1 like
• 2 in conversation