sqlug2.sas

SAS是功能強大的統計軟體

   select empnum, empname
     from employee
    where empnum between 301 and 401;


 /*
 * In this example a WHERE expression is used to test for missing
 * values.  This query displays employees who have no supervisor,
 * that is, the EMPBOSS column has a missing value instead of a
 * supervisor's employee number.
 */

   title2 'The Boss';
   proc sql;
   select empname, empboss
     from employee
    where empboss is null;


 /*
 * This example demonstrates a HAVING expression with a GROUP BY
 * clause.  This query lists customers and store numbers for
 * customers with two stores.
 *
 * The HAVING clause can be considered a "where clause" that is
 * applied to the groups formed by the GROUP BY clause.
 */

   title2 'Customers that have two stores';
   proc sql;
   select custname, custnum, custcity
     from customer
    group by custname
   having count(*)= 2
    order by custname, 2, 3;


 /*
 * This is an example of querying multiple tables. The following
 * query shows the cost, invoice price, and list price for each
 * product sold to Beach Land stores.
 * It also demonstrates the use of qualifying column names when
 * joining tables with columns names in common.
 */

   title2 'Product information for Beach Land stores';
   proc sql;
   select invnum, product.prodname, prodcost, invprice,
          prodlist, custname, custnum
     from product, invoice
    where product.prodname = invoice.prodname
      and custname = 'Beach Land';


 /*
 * This is an example of joining tables by more than one column.
 * The query below lists sales made to the Ocean City stores.
 */

   title2 'Sales made to ocean city stores';
   select invoice.custname, invoice.custnum, custcity, invnum,
          prodname
     from invoice as i, customer as c
     where invoice.custname = c.custname
       and invoice.custnum  = c.custnum
       and custcity   = 'Ocean City';


 /*
 * This example shows how table aliases can be used.
 * Aliases are shorthand "nicknames" for tables.
 */

   title2 'Sales made to ocean city stores';
   proc sql;
   select i.custname, i.custnum, custcity, invnum, prodname
     from invoice as i, customer as c
     where i.custname = c.custname
       and i.custnum  = c.custnum
       and custcity   = 'Ocean City';


 /*
 * This example shows what happens when you join tables that have
 * duplicate values in the matching columns.
 * The two tables, PET and CITY, are created and displayed.
 */

   proc sql;
   create table pet
     (person char,
      pettype char (12));

   insert into pet
     values('Jack','collie')
     values('Jack','parakeet')
     values('Sue','horse')
     values('Sue','goldfish');

   title2 'Pet table';
   select * from pet;

   create table city
     (person char,
      cityname char (12));

   insert into city
     values('Jack','Atlanta')
     values('Jack','Washington')
     values('Sue','Cary')
     values('Sue','Raleigh');

   title2 'City table';
   select * from city;


 /*
 * The query below joins the Pet and City tables by matching
 * on the PERSON column.
 */

   title2 'Joining PET and CITY tables using PROC SQL';
   select pet.person, pettype, city.person, cityname
     from pet, city
    where pet.person = city.person
    order by 1,2,3,4;

 /*
 * This example demonstrates the difference between a SAS MERGE
 * and the SQL procedure. The data step treats multiple
 * occurences in the BY group in a different fashion than the
 * SQL join operator.
 */

   title2 'Mergeing PET and CITY tables with the data step';
   data;
      merge pet city;
      by person;
      run;

   proc print;
      run;


 /*
 * This example demonstrates joining a table with itself.  This
 * kind of join is also called a reflexive join.  The query below
 * joins the City table with itself.
 */
   proc sql;
   select c1.person, c1.cityname, c2.person, c2.cityname
      from city as c1, city as c2
      order by 1,2,3,4;


 /*
 * This example demonstrates a REFLEXIVE JOIN with a WHERE
 * expression.  This query displays the supervisor name for each
 * employee who has a supervisor.
 * (A reflexive join implies joining a table with itself.)
 *
 * Notice the usage of alias names mgr and emp for the two
 * instances of the employee table.
 */

   title2 'Supervisor names for each employee who has a supervisor';
   select emp.empnum, emp.empname, emp.emptitle,
          mgr.empnum as mgrnum, mgr.empname as mgrname,
          mgr.emptitle as mgrtitle
     from employee emp, employee mgr
    where emp.empboss = mgr.empnum
    order by 1;


 /*
 * This example demonstrates a three-way join.  The query below
 * lists sales made by Sam to customer stores located in
 * Charleston.
 */

   title2 'Sales made by Sam to customer stores in Charleston';
   proc sql;
   select c.custname, c.custnum, i.prodname,
          i.invnum, e.empname, c.custcity
     from invoice i, employee e, customer c
    where i.empnum    =  e.empnum
      and i.custname  =  c.custname
      and i.custnum   =  c.custnum
      and e.empname   =  'Sam'
      and c.custcity  =  'Charleston'
    order by 1, 2, 3;


 /*
 * This example demonstrates a SUBQUERY.  The query below uses
 * a subquery to  connect the names Fred and Marvin in the
 * Employee table with their employee numbers in the Invoice table;
 * the employee numbers appear in both tables and therefore link
 * the tables.
 */

   title2 'Employee information for Fred and Marvin';
   proc sql;
   select empnum, custname, custnum, prodname, invnum
     from invoice
    where empnum in ( select empnum
                        from employee
                       where empname in ('Fred','Marvin')
                    )
      order by 1,2,3,4;


 /*
 * The following query breaks down the above query, replacing
 * the subquery with values.
 */

   select empnum, custname, custnum, prodname, invnum
      from invoice
      where empnum in (216,314)
      order by 1,2,3,4;


 /*
 * This example uses the WHERE expression with NOT IN.  The
 * query lists products that did not sell.
 */

   title2 'Products that did not sell';
   select prodname
     from product
    where prodname not in (select prodname from invoice)
    order by 1;


 /*
 * This example demonstrates a CORRELATED SUBQUERY.  This query
 * displays employee information about salesreps who sold
 * surfboards.
 */

   title2 'Which Salesreps Sell Surfboards?';
   proc sql;
   select empnum, empname, empcity
     from employee as e
    where 'surfboard' in (select prodname
                            from invoice as i
                           where i.empnum = e.empnum
                         )
    order by 1;


 /*
 * This is an example of a SUBQUERY using the EXISTS condition.
 * This query lists the stores who did not buy any products from
 * our sample wholesale company.
 */

   title2 'Stores who did not buy any products';
   proc sql;
   select distinct custname, custnum
     from customer c
    where not exists ( select *
                         from invoice i
                        where i.custname = c.custname
                          and i.custnum  = c.custnum
                     )
      order by 1, 2;


 /*
 * In this example, multiple levels of SUBQUERY NESTING are used.
 * here we display salesreps who made sales to stores in Ocean
 * City.
 */

   title2 'Salesreps who made sales to stores in Ocean City';
   proc sql;
   select empnum, empname
     from employee
    where empnum in (select empnum
                       from invoice i
                      where 'Ocean City' in (select custcity
                                               from customer c
                                      where c.custname = i.custname
                                         and c.custnum  = i.custnum
                                            )
                    )
     order by 1;


 /*
 * This example demonstrates the VALIDATE statement.  VALIDATE
 * is used to check the correctness of a SELECT statement's syntax
 * without actually executing the query.
 */

   proc sql;
   validate
      select empnum, empname
        from employee
        where empnum > 200 and empnum < 400;
quit;