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?
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.