DATA Step, Macro, Functions and more

How to aggregate columns based on plurality?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How to aggregate columns based on plurality?

I would like to aggreate the columns of a dataset based on the plurality of non-missing values for each column.

 

Suppose my dataset was

Name     Color     Food

Jane       Red       Sushi

Jane       Blue       

Jane       Red       

John       Green    Yogurt

John       Green    Sushi

John       Green    Yogurt

John       Red

 

I would like to summarize my dataset using something like this:

 

proc sql; 
select Name, plurality(Color) as Color, plurality(Food) as Food
from raw_data
group by Name;

The result would be 

 

Name     Color     Food

Jane       Red        Sushi

John       Green     Yogurt

 

 

The plurality function would return the value that occurs most often after missing values are removed. Ties could be handled using alphabetical order.

 

What is the best way to accomplish this data transformation in SAS (version 9.3 or 9.4)?

(Is it possible to combine a user defined function with proc sql to accomplish this?)


Accepted Solutions
Solution
‎07-27-2017 10:28 PM
Super User
Posts: 19,789

Re: How to aggregate columns based on plurality?

Posted in reply to Adam_Black

I've never heard the term plurality...in statistics we call it the mode. 

Run a PROC FREQ - sort it descending, there's an option for that. 

Then use BY group processing to take the top observations per 'group'.

untested code.

 

proc freq data=have;
table name*color*food/out=want;
run;

proc sort data=have; by name count;
run;

data want;
set have;
by name;
if last.name;
run;

 

View solution in original post


All Replies
Solution
‎07-27-2017 10:28 PM
Super User
Posts: 19,789

Re: How to aggregate columns based on plurality?

Posted in reply to Adam_Black

I've never heard the term plurality...in statistics we call it the mode. 

Run a PROC FREQ - sort it descending, there's an option for that. 

Then use BY group processing to take the top observations per 'group'.

untested code.

 

proc freq data=have;
table name*color*food/out=want;
run;

proc sort data=have; by name count;
run;

data want;
set have;
by name;
if last.name;
run;

 

Occasional Contributor
Posts: 13

Re: How to aggregate columns based on plurality?

Yes, the mode is what I am after. So I guess I have to do this aggregation one column at a time and then merge the results since there is no MODE aggregate function in PROC SQL. Thanks for the help!
Super User
Posts: 19,789

Re: How to aggregate columns based on plurality?

Posted in reply to Adam_Black

Adam_Black wrote:
So I guess I have to do this aggregation one column at a time and then merge the results since there is no MODE aggregate function in PROC SQL. Thanks for the help!

 

You can do them all at once, sort it, take top record and then transpose. Probably easier to code and will be fully dynamic in case anything changes in the future. 

 

Here's an example of how to do all at once Smiley Happy You may need to add a BY statement for yours to work properly. 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 233 views
  • 1 like
  • 2 in conversation