topic Re: How to aggregate columns based on plurality? in SAS Programming

How to aggregate columns based on plurality?

Adam_Black — Thu, 20 Jul 2017 21:11:54 GMT

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

Re: How to aggregate columns based on plurality?

Reeza — Thu, 20 Jul 2017 22:12:01 GMT

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;

Re: How to aggregate columns based on plurality?

Adam_Black — Fri, 28 Jul 2017 02:31:54 GMT

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!

Re: How to aggregate columns based on plurality?

Reeza — Fri, 28 Jul 2017 02:47:54 GMT

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