Solved: Re: sql max of count

ArseneW · Posted 12-18-2018 04:25 AM

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

FreelanceReinh · Posted 12-18-2018 04:52 AM

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;

RW9 · Posted 12-18-2018 04:36 AM

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 · Posted 12-18-2018 04:52 AM

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;

sql max of count