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

[niki0209]

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?

[Angel_Larrion · Accepted solution]

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.