How do I make a proper proc freq to find the most common value for each person in a dataset

niki0209 — Mon, 07 Sep 2020 09:17:26 GMT

Hello fellow SAS users,

I have a data set that looks somewhat like this:

Variable 1 Variable 2 Variable 3 Variable 4 Variable 5

Person 1 a a c a b

Person 2 a b a b c

Person 3 b c b a a

What I want is to make SAS find the most common variable value for each person. So in this case I would need a table like this:

a b c

Person 1 3 1 1

Person 2 2 2 1

Person 3 2 2 1

Can somebody help me in the right direction?

Re: How do I make a proper proc freq to find the most common value for each person in a dataset

Angel_Larrion — Mon, 07 Sep 2020 14:33:03 GMT

This should work:

data have ; input id $ var1 $ var2 $ var3 $ var4 $ var5 $;
datalines;
Person1 a a c a b
Person2 a b a b c
Person3 b c b a a
;
run;

proc transpose data=have out=b;
by id;
var var1-var5;
run;

proc sql;
create table count as
select distinct id, col1, count(*) as count
from b
group by id ,col1;
quit;

proc sort data=count;
by id col1;
run;

proc transpose data=count out=want(drop= col1 _name_);
by id ;
id col1;
run;

proc sql;
drop table count;
drop table b;
quit;

if there is only 3 values in your data set (a,b and c) you can do it with an array and a do cycle (in a data step), that would be simpler.

topic How do I make a proper proc freq to find the most common value for each person in a dataset in SAS Programming

How do I make a proper proc freq to find the most common value for each person in a dataset

Re: How do I make a proper proc freq to find the most common value for each person in a dataset