proc sql mode function

Question

I know that in mysql there's no aggregate function to calculate the mode (you have to do many steps). But I think in a proc sql should be possible. Is there a way to do so? The code would be sth like this:

select phones, mode(state_id)as state from
xxx.listings_abr3 
group by phones

the error is:

Function MODE could not be located.

thanks!

Answer 1

MODE is possible in proc sql, with a subquery.

data have;
  call streaminit(7);
  do id = 1 to 100;
    x = rand('Geometric',.2);
    output;
  end;
run;

proc sql;
 select x as mode from (
        select x, count(1) as count from have group by x
        ) 
    having count=max(count);
quit;

This takes advantage of the automatic remerging SAS will do for you; if you want to avoid that, you need to do a bit more work to get that having statement to work.

You still may need to do further work on this, as you might have multiple modes and this doesn't distinguish between them (it returns all modes).