max 3 in proc report

[mona4u]

Hi, 

I have this input and I'm trying to output only the max 3 by using proc report 

Top 3 Sites with highest # issues # Open Issues

1350	7
1601	7
1450	6
1451	4
5006	4
1602	3
1200	2
1600	2
1050	1
5008

and this is the aimed results 

Top 3 Sites with highest # issues:	1601	1350	1450
Open Issues:	7	7	6

[kiranv_]

what happens 7 is repeated  4 times

or 7 is repeated 1 time and 6 is repeated 4 times. for example

data abc;
infile datalines dlm = '09'x;
input 	@3 Site_Number @8	numsites;
datalines;
1	1050	1
2	1200	2
3	1350	7
4	1450	7
5	1451	4
6	1600	2
7	1601	6
8	1602	6
9	5006	6
10	5008	1
;

[Reeza]

@kiranv_ brings up a very good point, what if there are repeats. If there are 4 7's what would you want to see and how would you just which three to show? It may be better to use RANK in that approach, but you need to know the number of observations for that approach as well.

[Reeza]

Or create both and you have the option of using rank_multiple or rank as desired. It's worth knowing how to do this anyways:

proc sort data=abc; by descending numsites;

data cat;
set abc;
by descending numsites;
retain rank_multiple 0;

rank = _n_;
rank_multiple = ifn(first.numsites, rank_multiple+1, rank_multiple);
run;

[Reeza · Accepted solution]

• Sort the data
• Use OBS= to keep only top N observations
• Use PROC REPORT/TABULATE/PRINT to display the results, as desired

I'll leave the final formatting to you. 

Here's a demo using SASHELP.CLASS - using this in your demo code will help you get faster answers because a user can just run your code. It's also a good way to learn because you're separating development from implementation so you're less likely to make mistakes. 

%* sort descending;
proc sort data=sashelp.class out=class;
by descending height;
run;


proc tabulate data=class (obs=3); *take top 3;
class name;
var height;
table name*height=''*mean=''; *display somewhat as shown;
run;

[kiranv_]

I like @Reeza solution which is clean way to do what you want to do. Another interesting way could be sql and transpose for your scenario.

/* for any top 3*/
proc sql outobs=3;
create table newtable(drop=val) as 
select site_number, numsites,
(select count(distinct numsites) from abc a
where a.numsites >= b.numsites)as val
from abc b;

/* for top 3 repeated*/
proc sql ;
create table newtable (drop=val) as 
select site_number, numsites,
(select count(distinct numsites)
 from abc awhere a.numsites >= b.numsites)as val
 from abc bwhere calculated val le 3;

proc transpose data =newtable out=finaltable(drop =_name_);
id site_number;
var numsites;
run;