How to aggregate columns based on plurality?

[Adam_Black]

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

[Reeza · Accepted solution]

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;

[Adam_Black]

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!

[Reeza]

---

@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 🙂 You may need to add a BY statement for yours to work properly.