Need to calculate the mode through SQL query.

[vishalrajpoot3]

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

[andreas_lds]

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;

[ballardw]

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.