topic Re: selecting and ordering the ID with max rows count in SAS Procedures

selecting and ordering the ID with max rows count

SIgnificatif — Fri, 19 Jul 2019 08:59:33 GMT

Hi all,

I would like to perform a query to select top 3 ID's that have max row counts,

I have found that it's possible to do with this query :

proc sql;
create table want as
select *
from (select id, count(*) as cnt from have group by id_user)
having cnt=max(cnt);
quit;

but I would like to select top 3 ID' s that have the max count of rows of variable Name ( attached image) , the ID 1001 must come first , the 1002 second and 1004 third.

Anyone have an idea to that ?

Thanks.

Re: selecting and ordering the ID with max rows count

PeterClemmensen — Fri, 19 Jul 2019 09:36:03 GMT

Why would ID=1004 and not 1005 be in the data? They both appear once?

Re: selecting and ordering the ID with max rows count

SIgnificatif — Fri, 19 Jul 2019 09:44:48 GMT

yes,

we can add them,

the idea is to get top 3 , I don't need others, but ordering by variables count could also be an option.... so I can srroll trhough them all from top...

Re: selecting and ordering the ID with max rows count

andreas_lds — Fri, 19 Jul 2019 10:07:09 GMT

In https://communities.sas.com/t5/SAS-Programming/Top-N-rows-per-by-group/td-p/172659 @Jagadishkatam suggested using proc sort and proc rank.