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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.