|
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
clause.
So this has to be done in stages. Here is an example. First set up the
input table:
proc sql;
create table foo as
select sex as v1, age as v2
from sashelp.class;
Next get the counts:
create table d3 as
select v1, v2, count(*) as pop
from foo
group by v1, v2;
Now use MAX to identify the highest counts:
create table d2 as
select * from d3
group by v1
having pop=max(pop);
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;
quit;
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
<arjen.raateland@YMPARISTO.FI> wrote:
>Sigurd Hermansen wrote:
>> Rusty:
>> 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
>>
>> quit;
>
>
>Hello Sig,
>
>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
>71 ;
>ERROR: Summary functions nested in this way are not supported.
>72 quit;
>NOTE: The SAS System stopped processing this step because of errors.
>
>It looked like a nice idea, though. Could you get it to work?
>
>cheerio,
>--
>Mr. Arjen Raateland
>Finnish Environment Institute SYKE
>Research Dept.
>SAS Support
>phone +358 9 4030 0350
| 
