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

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
novinosrin
Tourmaline | Level 20
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;

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

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