topic Re: Top 10 list in SAS Procedures

Top 10 list

don21 — Mon, 18 Sep 2017 07:37:55 GMT

Dear all,

My data set has 1 char and 1 Num var, the char var has duplicates but the numeriv value for the particular char value will be unque as in given example, I am currently struggling to get the Top 10 count in num(sorted order ) as shown below pls help!!

Have dataset

name score

------- --------

John 2500

john 2000

mell 5000

shawm 4850

Mell 9000

Mike 2500

Mike 1650

Mike 6570

ness 2330

Want data set - Top 3 names

Name

Mell	14000
Mike	10720
Shawn	4850

Please help!!

Re: Top 10 list

BenbowL — Mon, 18 Sep 2017 07:51:05 GMT

Hi,

Something like the following will get you the score totals for each name:

proc sql;
create table want as 
select distinct name, sum(score) as total 
from have
group by name
order by total desc ;
quit;

You should then be able to write a step to keep only the top 3.

Good luck,

Lee

Re: Top 10 list

ChrisBrooks — Mon, 18 Sep 2017 08:35:08 GMT

Proc Rank could do it in one step e.g.

proc rank data=sashelp.class out=want(where=(rank<=3)) descending;
var weight;
ranks rank;
run;

Re: Top 10 list

ShiroAmada — Mon, 18 Sep 2017 08:44:46 GMT

Try this....

proc sql;
  create table WANT as
select
upcase(name) as name,
sum(score) as total_score
from HAVE
group by calculated name
 order by calculated SCORE desc;
quit;

data WANT;
  set WANT(obs=10);
run;

Hope this helps...