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?)
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;
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 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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.