## max 3 in proc report

Solved
Regular Contributor
Posts: 158

# max 3 in proc report

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

Accepted Solutions
Solution
‎02-21-2018 04:06 PM
Super User
Posts: 23,681

## Re: max 3 in proc report

• 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;``````

All Replies
Highlighted
PROC Star
Posts: 509

## Re: max 3 in proc report

[ Edited ]

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
;``````
Super User
Posts: 23,681

## Re: max 3 in proc report

@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.
Super User
Posts: 23,681

## Re: max 3 in proc report

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;``````
Solution
‎02-21-2018 04:06 PM
Super User
Posts: 23,681

## Re: max 3 in proc report

• 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;``````

PROC Star
Posts: 509

## Re: max 3 in proc report

[ Edited ]

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 awhere a.numsites >= b.numsites)as valfrom 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;`
☑ This topic is solved.