Proc sql mode function

Question

I know that in mysql there is no aggregated function to calculate the mode (you need to take many steps). But I think that in proc sql should be possible. Is there any way to do this? The code will look like this:

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

error:

The MODE function cannot be located.

thank!

Answer 1

MODEpossible in proc sqlwith 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 allows you to use automatic SAS reuse for you; if you want to avoid this, you need to work a bit to have instructions for work.

You may still need further work on this, as you may have several modes, and this does not distinguish them (it returns all modes).

Answer 2

, , :

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

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