sqlfun02.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          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 10
a 20
b 10
b 20
b 30
run;


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;