topic Re: proc sql, mode within groups in SAS Procedures

proc sql, mode within groups

A_S — Tue, 21 Jun 2016 19:15:17 GMT

I need to find the most frequent level of character variables by group, and the sum of several numeric variables by the same group. If the most frequent level is not unique, I would like the last level with in the group (the data is sorted). Here is an example of what I want to acheive:

Original data:

id	city	color	numeric_variable
123	New York	blue	4
123	LA	blue	3
123	Seattle	green	2
456	New York	yellow	3
456	LA	green	2
456	LA	red	3
456	Seattle	blue	5
789	Seattle	yellow	2
789	New York	green	3
789	Seattle	yellow	5
789	Seattle	yellow	2
789	Seattle	green	4

Desired outcome:

id	city	color	sum
123	Seattle	blue	9
456	LA	blue	13
789	Seattle	yellow	16

For the sum, using group by id and SUM() within PROC SQL works well. Is there a MODE function within PROC SQL?

I am using Enterprise Guide version 5.1 (5.100.0.12019) (32-bit).

Thank you!

Re: proc sql, mode within groups

LinusH — Tue, 21 Jun 2016 19:37:42 GMT

I'm not aware of any mode function (unfortunately).

But you can calculate the mode by using the proc univariate.

Not sure though if can calculate all your measures in one go.

Re: proc sql, mode within groups

PGStats — Wed, 22 Jun 2016 03:27:29 GMT

Well it can be done in SQL but it ain't straightforward

data a;
input id city &:$20. color :$8.	num;
datalines;
123  New York  blue  4
123  LA  blue  3
123  Seattle  green  2
456  New York  yellow  3
456  LA  green  2
456  LA  red  3
456  Seattle  blue  5
789  Seattle  yellow  2
789  New York  green  3
789  Seattle  yellow  5
789  Seattle  yellow  2
789  Seattle  green  4
;

data T;
set a;
order = _n_;
run;

proc sql;
create table cityModes as
select id, city
from
    (select id, city, last
    from
        (select id, city, count(*) as n, max(order) as last
        from T
        group by id, city)
    group by id
    having n = max(n))
group by id
having last=max(last); 

create table colorModes as
select id, color
from
    (select id, color, last
    from
        (select id, color, count(*) as n, max(order) as last
        from T
        group by id, color)
    group by id
    having n = max(n))
group by id
having last=max(last); 

create table want as
select T.id, a.city, b.color, sum(T.num) as sum
from 
    T inner join 
    cityModes as a on T.id=a.id inner join 
    colorModes as b on a.id=b.id
group by T.id, a.city, b.color;
select * from want;
quit;

Re: proc sql, mode within groups

A_S — Wed, 15 Mar 2017 14:13:29 GMT

Thank you!