The SAS Output Delivery System and reporting techniques

max 3 in proc report

Accepted Solution Solved
Reply
Regular Contributor
Posts: 158
Accepted Solution

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

13507
16017
14506
14514
50064
16023
12002
16002
10501
5008

 

and this is the aimed results 

Top 3 Sites with highest # issues:160113501450
Open Issues:776

 


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;

 

 

View solution in original post


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 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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 301 views
  • 5 likes
  • 3 in conversation