sqlug4.sas

SAS是功能強大的統計軟體

       on left.key=right.key;


 /*
 * This is an example of a THREE WAY JOIN.
 * It is followed by a two phase process breaking the join into
 * a series of pairwise joins with intermediate results.
 */

   select  e.empname, i.custname, i.prodname, i.invqty,
           i.invqty*p.prodcost label='Cost Price' format=dollar.
     from  invoice i, employe e, product p
     where i.prodname = 'windsurfer'
       and i.empnum   = e.empnum
       and i.prodname = p.prodname;

   create table threej as
   select  e.empname, i.custname, i.prodname, i.invqty
     from  invoice i, employe e
     where i.prodname = 'windsurfer'
       and i.empnum = e.empnum;

   title2 'INTERMEDIATE TABLE JOINED WITH THIRD TABLE';
   select  t.*, t.invqty*p.prodcost label='Cost Price' format=dollar.
     from  threej t, product p
     where t.prodname = p.prodname;


 /*=================================================================*/
 /*==                      LIKE predicate                         ==*/
 /*=================================================================*/

   title2 'Customer names that begin with "S"';
   select distinct custname
     from customr
     where custname like "S%";

   title2 'Customer cities that do not end in Beach';
   select distinct custcity
     from customr
     where custcity not like '%Beach';


 /*=================================================================*/
 /*==                      OBJECT-ITEMS                           ==*/
 /*=================================================================*/

   title2 'Product Information';
   select prodname label='Product Name',
          prodlist label='List Price of This Item' format=dollar8.0,
          prodcost label='COST PRICE (DO NOT REVEAL)' format=comma8.0
   from product;


 /*=================================================================*/
 /*==                      Order By clause                        ==*/
 /*=================================================================*/


 /*
 * This examples demonstrates the use of an arithmetic expression
 * in the ORDER BY clause.  It is not necessary to select the column
 * that you are ordering on.
 */

   select *
     from product
    order by prodlist/prodcost desc;


 /*=================================================================*/
 /*==                        Set operators                        ==*/
 /*=================================================================*/

 /*
 * This example demonstrates a UNION of tables that do not have
 * the same number of columns.  Table1 has three columns and Table2
 * has only two columns.
 */

data table1;
   input x y$ z;
   cards;
1 aaa 2
2 bbb 4
;
run;

data table2;
   input x y$;
   cards;
5 ccc
6 ddd
;
run;

proc sql;
   title2 'Union of Table1 and Table2';
   select * from table1
   union
   select * from table2;

 /*
 * The following examples demonstrate various set operations.
 */

data lefty;
   input x y $;
   cards;
   1 one
   2 two
   2 two
   3 three
   run;

proc sql;

   title2 'Lefty Table';
   select * from lefty;


data rightz;
   input x z $;
   cards;
   1 one
   2 two
   4 four
   run;

proc sql;

   title2 'Rightz Table';
   select * from rightz;

 /*
 * This is an example of the OUTER UNION operation.
 */

   title2 'OUTER UNION EXAMPLE';
   select * from lefty
   outer union
   select * from rightz;

   title2 'UNION EXAMPLE';
   select * from lefty
   union
   select * from rightz;

   title2 'UNION ALL OF LEFTY AND RIGHTZ';
   select * from lefty
   union all
   select * from rightz;

   title2 'EXCEPT ALL EXAMPLE';
   select * from lefty
   except all
   select * from rightz;

   title2 'INTERSECTION OF LEFTY AND RIGHTZ';
   select * from lefty
   intersect
   select * from rightz;

   title2 'OUTER UNION CORRESPONDING EXAMPLE';
   select * from lefty
   outer union corresponding
   select * from rightz;

   title2 'UNION CORRESPONDING EXAMPLE';
   select * from lefty
   union corr
   select * from rightz;


 /*=================================================================*/
 /*==                       SET clause                            ==*/
 /*=================================================================*/


 /*
 * This example CREATEs a table and uses the SAS function UPCASE
 * to SET the query results in uppercase.
 */

   create table xyzzy as
   select empnum, empname, empyears
     from employe
     where emptitle='manager';

   title2 'XYZZY Table';
   select * from xyzzy;

   update xyzzy
     set empname  = upcase(empname),
         empyears = 20;

   title2 'Updated XYZZY Table';
   select * from xyzzy;


 /*=================================================================*/
 /*==                    Sounds like predicate                    ==*/
 /*=================================================================*/


data people;
   length fname $8 lname $16;
   input fname lname;
   cards;
   Magnolia Lewis
   Joe Louis
   David Johnson
   Tom Johnsson
   Susan Johnston
   Matthew Johnsen
   Larry Jiles
   Aretha Giles
   John Geil
   Ione Gin
   run;

proc sql;
   title2 'LAST NAMES WHICH SOUND LIKE JOHNSON';
   select * from people where lname =* "Johnson" order by 2;

   title2 'LAST NAMES WHICH SOUND LIKE LEWIS';
   select * from people where lname =* "Lewis";

   title2 'LAST NAMES WHICH SOUND LIKE GIN OR GILES';
   select * from people
      where (lname =* "Gin") or (lname ="Giles");


 /*=================================================================*/
 /*==                     Contains predicate                      ==*/
 /*=================================================================*/

   title2 'CUSTOMER CITIES WHICH CONTAIN BEACH';
   select distinct * from customr
     where custcity contains 'Beach';


 /*=================================================================*/
 /*==                  Subqueries in PROC SQL                     ==*/
 /*=================================================================*/

   title2 'WHO LIVES NEAR HERB?';
   select empname
      from employe
      where empcity = ( select empcity
                          from employe
                         where empname = 'Herb');

   title2 'SALESREPS WHO MADE SALES';
   select e1.empnum, e1.empname, e2.empname as manager
      from  employe as e1,
            employe as e2
      where e1.empboss = e2.empnum  and
            e1.empnum in (select empnum from invoice)
      order by 2;

   title2 'WHO HAS WORKED HER LONGER THAN ANY SINGLE SALESREP?';
   select empname
      from employe
      where empyears > all (select empyears
                              from employe
                             where emptitle = 'salesrep');

   title2 'DO ANY SALESREPS HAVE MORE SERVICE THAN A MANAGER?';
   select empname
      from employe
      where emptitle = 'salesrep'
         and empyears > any (select empyears
                               from employe
                              where emptitle = 'manager');

   title2 'PRODUCTS SOLD TO MYRTLE BEACH STORES';
   select distinct prodname
      from  invoice as i
      where 'Myrtle Beach' in ( select distinct custcity
                                  from customr as c
                                 where c.custname = i.custname
                                   and c.custnum  = i.custnum);


 /*=================================================================*/
 /*==             Summary Statistics and PROC SQL                 ==*/
 /*=================================================================*/

data summary;
   input x y z;
   cards;
   1 2 3
   4 5 6
   7 8 9
   ;
   run;

   title2 'SUMMARY TABLE';
   proc sql;
   select * from summary;

 /*
 * This is an example of the SUM, MIN and MAX functions
 * using a single argument. The statistic is computed down
 * the columns of the dataset.
 */

   title2 'COLUMN-WISE SUMMARY STATISTICS';
   select sum(x) as sum_x, min(y) as min_y, max(z) as max_z
     from summary;

 /*
 * This example demonstrates the SUM function using multiple arguments.
 * The statistic is computed across the arguments to the function.
 */

   title2 'ROW-WISE SUMMARY STATISTICS';
   select *, sum(x,y,z) as rowsum
     from summary;

 /*
 * This is an example of evaluating SUMMARY functions with
 * non-summary expressions.  This type of query may result
 * in having to remerge the original data back with the
 * summary values computed.
 */

   title2 'TOTAL SUMMARY STATISTICS';
   select x, (100*x/sum(x)) as p_total
     from summary;


 /*
 * Systems without this remerge facility would require you to create
 * a view that computed the total, and join that view with the original
 * data in your query.
 */

   create view totalx as
      select sum(x) as totx
         from summary;

   title2 'TOTAL SUMMARY STATISTICS';
   select x, (100*x/totx) as p_total
      from summary, totalx;



 /*
 * This example demonstrates the use of a SUMMARY function
 * with a GROUP BY clause.  The example lists the number of
 * different customers for each product whose outstanding
 * inventory count is more than 30 units.
 */

   title2 'INVENTORIES GREATER THAN 30 UNITS';
   select prodname as products,
          count(distinct custname) label='Number of Customers'
     from invoice as i1
    group by prodname
   having (select sum(invqty)
             from invoice
            where prodname = i1.prodname
          ) > 30

    order by 1,2;

 /*
 * This is another example of a SUMMARY function with a GROUP BY
 * clause.  This example shows the total sales to each customer
 * and the fraction of the total sales for which the customer is
 * responsible. It uses the remerge facility.
 */

   title2 'TOTAL SALES INFORMATION';
   select custname, sales,
          100*sales/sum(sales) as contrib format=6.2
     from (select custname, sum(invqty*invprice) as sales
              from invoice
              group by custname);


 /*=================================================================*/
 /*==                     Table Expression                        ==*/
 /*=================================================================*/


   title2 'Products sold in Myrtle Beach by Virginia Beach employees.';
   select distinct prodname
     from customr as c, invoice as i
    where c.custname = i.custname and
          c.custnum  = i.custnum  and
          c.custcity = 'Myrtle Beach'

   intersect

   select distinct prodname
     from invoice as i, employe as e
    where e.empnum  = i.empnum and e.empcity = 'Virginia Beach';


   title2 'Job Titles';
   select distinct emptitle
      from employe
      order by emptitle;


 /*=================================================================*/
 /*==                        Table-Name                           ==*/
 /*=================================================================*/

 /*
 * This example displays the Product table without listing the product
 * costs.  The SAS data set options DROP= and LABEL= are used.
 */

   title2 'Product Table';
   select *
      from product(drop=prodcost label='List Prices, 1989');

 /*
 * The example below creates and displays data using the table Nosales.
 */

   create table nosales as
      select distinct custname
         from customr
         where custname not in
               (select custname from invoice)
      order by custname;

   title2 'Nosales Table';
   select * from nosales;

 /*
 * This example creates a temporarily stored view that displays the
 * salesreps by the number of sales (number of invoices) they have made.
 */

   create view salenum as
      select name, count(*) as numinv
         from employe(rename=(empname=name)) as e,
              invoice as i
         where e.empnum = i.empnum
         group by name
         order by numinv;

   title2 'Salesnum Table';
   select * from salenum;


 /*=================================================================*/
 /*==                       Values clause                         ==*/
 /*=================================================================*/

 /*
 * This example creates a small table and adds values
 * using the VALUES clause.
 */

   create table valtest ( x numeric, y numeric, z char );

   insert into valtest
          values(1,2,'happy')
          values(3,4,'sad');

   title2 'Valtest Table';
   select * from valtest;


 /*=================================================================*/
 /*==                     WHERE Expression                        ==*/
 /*=================================================================*/

 /*
 * This example lists the dollar amount for sales made where
 * the customer and salesrep lived in the same city.  Notice the
 * use of both comparison and logical operators in the WHERE
 * expression.
 */

   select invnum, custname, custnum, empnum,
          (invqty * invprice) as sales
      from invoice
      where (select distinct empcity
                from employe
                where empnum = invoice.empnum)
            =
            (select distinct custcity
                from customr
                where custname = invoice.custname and
                      custnum = invoice.custnum)
   order by 1;

   quit;