CODEWITHSUNDEEP
sas
GabyLP
GabyLP

Reputation: 3781

proc sql mode function

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!

Upvotes: 4

Views: 9258

Answers (2)

catquas
catquas

Reputation: 720

Using the method Joe gives in his answer, here is how you would calculate modes by group:

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;

Upvotes: 4

Joe
Joe

Reputation: 63424

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).

Upvotes: 5

Related Questions