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 |
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;
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
;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.