topic Re: max 3 in proc report in ODS and Base Reporting

max 3 in proc report

mona4u — Wed, 21 Feb 2018 20:41:32 GMT

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

Re: max 3 in proc report

kiranv_ — Wed, 21 Feb 2018 20:53:02 GMT

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
;

Re: max 3 in proc report

Reeza — Wed, 21 Feb 2018 20:52:49 GMT

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;

Re: max 3 in proc report

Reeza — Wed, 21 Feb 2018 21:09:01 GMT

@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.

Re: max 3 in proc report

kiranv_ — Wed, 21 Feb 2018 21:55:12 GMT

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;

Re: max 3 in proc report

Reeza — Wed, 21 Feb 2018 21:18:56 GMT

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;