/****************************************************************/ /*          S A S   S A M P L E   L I B R A R Y                 */ /*                                                              */ /*    NAME: SQLFUN02                                            */ /*   TITLE: fun/interesting applications of PROC SQL. (fun02)   */ /* PRODUCT: BASE                                                */ /*  SYSTEM: ALL                                                 */ /*    KEYS: SQL DATMAN SELECT GROUP BY MAX SUM HAVING           */ /*   PROCS: SQL                                                 */ /*    DATA:                                                     */ /*                                                              */ /* SUPPORT: pmk                         UPDATE:                 */ /*     REF:                                                     */ /*    MISC: this example shows how one can calcuate             */ /*          nested statistics with PROC SQL                     */ /*                                                              */ /*          ie who does the most total sales? is a query        */ /*          that involves both SUM() and MAX()                  */ /*                                                              */ /****************************************************************/ title1 'SAS SAMPLE LIBRARY, MEMBER(SQLFUN02)';data sales; input company $ cost; cards;a 10a 20b 10b 20b 30run;proc sql; /*  * one way is to figure the max of the total cost in  * a subquery, and use that value in the having clause  * this method involves reading the data twice  */ select company   from sales  group by company having sum(cost) = ( select max(cost)                        from ( select sum(cost) as cost                                 from sales                                group by company                             )                    ); /*  * another way is to figure the max of the total cost and  * use that single row in a join with the original data.  * this method also involves reading the data twice  */  select company    from sales,         ( select max(cost) as maxcost             from ( select sum(cost) as cost                      from sales                     group by company                  )         )  group by company having sum(cost) = maxcost; /*  * a method that uses the sas re-merge features allows  * you to do this with a single pass of the data.  */ select company   from ( select company, sum(cost) as totcost            from sales           group by company        ) having totcost = max(totcost);quit;