Hello SAS Community,
I am not getting desired output from below code while calculating mode with SAS SQL. Can anyone help me to get the problem fixed.
data new;
set sashelp.class;
if name in ('Alfred' 'Barbara' 'Carol') then name='Alice';
If name in ('Janet' 'Jane' 'Jeffrey' 'John' 'Joyce') then name='Judy';
if name in('William' 'Louise' 'Mary' 'Philip' 'Robert') then name='Ronald';
run;
proc sort data=new;
by name sex;
run;
proc sql;
create table new_1 as
select name, sex, age as mode from (
select name, sex, age, count(1) as count from new
group by name, sex, age)
group by name, sex
having count=max(count);
quit;
Desired Output
Name | Sex | Age |
Alice | F | 13 |
Alice | M | 14 |
Henry | M | 14 |
James | M | 12 |
Judy | F | 11 |
Judy | M | 12 |
Ronald | F | 12 |
Ronald | M | 12 |
Thomas | M | 11 |
Why do you have to use proc sql?
I can't fix the sql-code, but you get the expected output by using proc summary twice.
proc summary data=new nway;
class name sex age;
output out=count(drop= _type_ rename=(_freq_=count));
run;
proc summary data=count nway;
class name sex;
id age;
var count;
output out=want1 max=;
run;
Your "problem" is a combination of two things. First the
having count= max(count)
will select multiple records if the count=max(count). You have many ties because of the data you have, so that having does not select single output for each group.
If your data has ties at the maximum count for any combination of categories then SQL will return multiple values.
If you expected a "tie breaker" to report the lowest value of age then that was not programmed anywhere.
Is there some reason you want this for Proc SQL? Proc Univariate or Means/ Summary would typically be the procedures to use to get such.
But even those will not work with Judy because you have nothing but tied counts for Age by Sex.
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.