BookmarkSubscribeRSS Feed
A_S
Fluorite | Level 6 A_S
Fluorite | Level 6

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:

idcitycolornumeric_variable
123New Yorkblue4
123LAblue3
123Seattlegreen2
456New Yorkyellow3
456LAgreen2
456LAred3
456Seattleblue5
789Seattleyellow2
789New Yorkgreen3
789Seattleyellow5
789Seattleyellow2
789Seattlegreen4

 

Desired outcome:

idcitycolorsum
123Seattleblue9
456LAblue13
789Seattleyellow16

 

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!

3 REPLIES 3
LinusH
Tourmaline | Level 20

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.

Data never sleeps
PGStats
Opal | Level 21

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;
PG
A_S
Fluorite | Level 6 A_S
Fluorite | Level 6

Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 6373 views
  • 1 like
  • 3 in conversation