topic Re: sql max of count in New SAS User

sql max of count

ArseneW — Tue, 18 Dec 2018 09:25:59 GMT

Hello,

Is there a possibility to write one sql querry in sas that will show max of count?

Let's say I have below data, I'm looking for user (USER_ID) with max number of rows and I would like to know this count number

RW9 — Tue, 18 Dec 2018 09:36:14 GMT

Post test data in the form of a datastep:

As such not tested:

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

Note that if there are more than one with the total number of records these will all come out.

FreelanceReinh — Tue, 18 Dec 2018 09:52:16 GMT

Hello @ArseneW and welcome to the SAS Support Communities!

Use an inline view:

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