topic Determine maximum count of distinct values in SAS Programming

Determine maximum count of distinct values

Batman — Thu, 10 Jun 2021 21:32:19 GMT

Is there was a combine the two sql steps below into one?

/*Sample data*/
data one;
input x $1. y 1.;
datalines;
a1
a2
a1
b1
b1
;
run;

proc sql;
/*determine count of distinct values of y for each value of x*/
create table two as select x, count(distinct y) as cnt_y
from one
group by x;
/*determine maximum value of distinct value count*/
select max(cnt_y)
from two;
quit;

Re: Determine maximum count of distinct values

novinosrin — Thu, 10 Jun 2021 21:43:57 GMT

Hi @Batman You could do in one step that still doesn't mean it's a one pass though. For brevity, you could modify the code to-

proc sql;
 select max(cnt_y) 
 from (select count(distinct y) as cnt_y from one group by x);
quit;

However the above is still 2 passes akin to your solution