A summary function of a summary function only makes sense if the grouping
changes. In this case, you want COUNT applied to each V1 x V2 pair, but MAX
applied once at each V1 level. But a SELECT can have only one GROUP BY
So this has to be done in stages. Here is an example. First set up the
create table foo as
select sex as v1, age as v2
Next get the counts:
create table d3 as
select v1, v2, count(*) as pop
group by v1, v2;
Now use MAX to identify the highest counts:
create table d2 as
select * from d3
group by v1
Of course ties are possible. If you want to apply an arbitrary tie-breaking
rule, another layer is needed:
create table d1 as
select * from d2
group by v1
having v2 = min(v2)
order by v1;
All of this can be rolled up into one statement, as Richard demonstrated
earlier in this thread.
On Tue, 3 May 2005 15:36:03 +0300, Arjen Raateland
>Sigurd Hermansen wrote:
>> Unless I am missing something, your program will select the least common
>> combination of v1 with a v2 value.
>> In SQL, the HAVING clause sets constraints on summary values. Try ....
>> proc sql;
>> create table d1 as
>> select distinct v1, v2, count(*) as pop
>> from d
>> group by v1 having pop=max(pop)
>> order by v1
>I tried your code, but SAS 8.2 doesn't think it's acceptable:
>65 proc sql;
>66 create table d1 as
>67 select distinct v1, v2, count(*) as pop
>68 from foo
>69 group by v1 having pop=max(pop)
>70 order by v1
>ERROR: Summary functions nested in this way are not supported.
>NOTE: The SAS System stopped processing this step because of errors.
>It looked like a nice idea, though. Could you get it to work?
>Mr. Arjen Raateland
>Finnish Environment Institute SYKE
>phone +358 9 4030 0350