sqlug4.sas

SAS是功能強大的統計軟體

 * This is an example of an UPDATE statement using a CASE expression.
 * The case expression returns the multiplier that we would like
 * to modify the prodlist column by.
 */

   create table updateex as
   select *, prodlist as oldlist
     from product;

   update updateex
      set prodlist = prodlist * case when prodcost < 1000 then 1.1
                                    else 1.2
                                    end
          where prodname not= 'kayak';

   title2 'UPDATEEX TABLE';
   select * from updateex;



 /*
 * This is an example of the previous example of an UPDATE statement
 * without a CASE expression.  When you do not use a CASE expression,
 * you must use two UPDATE statements to modify the Updateex data.
 */

   create table updateex as
   select *, prodlist as oldlist
     from product;

   update updateex
      set prodlist = prodlist * 1.1
          where prodname not= 'kayak' and prodcost < 1000;

   update updateex
      set prodlist = prodlist * 1.2
          where prodname not= 'kayak' and prodcost >= 1000;

 /*
 * This is an example of an UPDATE statement not using two queries
 * and without a CASE expression.  To use the updated value of A in
 * the SET clause B=B+A, two queries must be written or a CASE
 * expression used.
 */

   create table numbers (a int, b int);
   insert into numbers values(1,10) values (2,20);

   title2 'Numbers Table';
   select * from numbers;

   update numbers
      set a=a+5, b=b+a;

   title2 'Updated Numbers Table';
   select * from numbers;


 /*=================================================================*/
 /*==                     VALIDATE statement                      ==*/
 /*=================================================================*/


 /*
 * This example checks the query for syntactic accuracy and writes a
 * message on the SAS log.
 */

   validate
   select custname, custcity
      from customr
      where custcity contains 'Beach';


 /*=================================================================*/
 /*==                    MACRO VARIABLES set                      ==*/
 /*==                   by PROC SQL statements                    ==*/
 /*=================================================================*/

 /*
 * This example retrieves the data in the Employee table but does not
 * display them in SAS output because of the NOPRINT option on the
 * PROC SQL statement.  %PUT macro language statement is used to request
 * and display the &SQL variable values.
 */

   proc sql noprint;
   select * from employe;
   %put sqlobs=**&sqlobs** sqloops=**&sqloops** sqlrc=**&sqlrc**;


 /*=================================================================*/
 /*==                      BETWEEN predicate                      ==*/
 /*=================================================================*/


 /*
 * This is an example using the BETWEEN clause.  The example
 * lists employees who have worked at the wholesale company
 * for at least 11 years but no more than 50 years.
 */

proc sql;
   title2 'Old timers';
   select *
     from employe
    where empyears between 11 and 50;


 /*=================================================================*/
 /*==                       CASE expression                       ==*/
 /*=================================================================*/


 /*
 * This example demonstrates the use of a CASE expression without a
 * case-operand.  The form without a case-operand is necessary when
 * the when-condition does not involve equality tests.
 */

   title2 'Product information';
   select prodname,
          case
            when prodcost < 50   then 'cheap'
            when prodcost < 500  then 'not-so-cheap'
            when prodcost < 1000 then 'expensive'
            else 'outrageous'
            end  as category
     from product;


 /*=================================================================*/
 /*==                     Column Attributes                       ==*/
 /*=================================================================*/

 /*
 * This example demonstrates how PROC SQL maps datatypes into those
 * supported by the SAS System.
 */

   create table coldefex
     ( x int,
       y char(20),
       z dec(5,2)  format=comma5.2  label='Z Value'
     );

proc contents data=coldefex;
   title2;
   run;

 /*
 * This example demonstrates the use of INFORMAT and FORMAT options
 * when defining a column.
 */

proc sql;
   create table fish
     (name     char(12),     /* fish by its common name */
      qty      num  informat=comma5.,
                             /* number of fish received */
      weight   num  informat=comma5.2,
                             /*  weight in pounds       */
      datein   num  informat=date10. format=date7.,
                             /* date when fish received */
      sellby   num  informat=date10. format=date7.
                             /* date fish must be sold by */
      );

   insert into fish
     values('flounder',100,50,'2jun1989'd,'7 jun89'd)
     values('red snapper',20,14,'1 jun 89'd,'4jun89'd)
     values('shrimp',.,98,'3jun1989'd,'6jun 89'd)
     values('scallops',.,70,'2-jun-89'd,'6jun89'd)
     ;

 /*
 * This example demonstrates the use of column modifiers in a select
 * expression, to modify a columns attributes temporarily.
 */

   title2 'Fish table';
   select name, qty as quantity, weight format=comma5.2,
          datein label='Date Rec''d',
          sellby label='Last Fresh Day'
     from fish;


 /*=================================================================*/
 /*==                      COLUMN NAMES                           ==*/
 /*=================================================================*/

 /*
 * This example demonstrates the use of qualifying a column. If the
 * same column name exists in more than one table in the query, each
 *  column must be qualified with its table name or a table alias.
 */

   select employe.empnum, empname, prodname, invqty, invprice
      from employe, invoice
      where employe.empnum = invoice.empnum;

 /*
 * This example shows how to calculate and list the difference between
 * the cost of a product and the list price it is sold for.
 */

   title2 'Product Information';
   select prodname as product, prodcost, prodlist,
          (prodlist - prodcost) as costdiff format=dollar.
     from product
     order by costdiff;

 /*
 * In this example, a table is joined with itself to get a report of
 * salesreps and their managers, together with the cities in which
 * they live.  The table aliases REP and MGR are used to qualify the
 * columns in the SELECT list.
 */

   title2 'Sales Representatives';
   select rep.empname label='Sales Representative',
          rep.empcity,
          mgr.empname as manager,
          mgr.empcity as mgrcity
      from employe rep, employe mgr
      where rep.empboss = mgr.empnum and rep.emptitle = 'salesrep';


 /*=================================================================*/
 /*==                      Exists predicate                       ==*/
 /*=================================================================*/

 /*
 * This is an example of a CORRELATED SUBQUERY using an
 * EXISTS condition.
 */

   title2 'Poor salesman report';
   select empname, 'is a poor Salesman'
     from employe
    where not exists ( select *
                         from invoice
                        where empnum = employe.empnum
                     )
      and emptitle = 'salesrep';


 /*=================================================================*/
 /*==                        In-line Views                        ==*/
 /*=================================================================*/

 /*
 * This example demonstrates the use of table aliases.
 */

   create table sales as
      select e.empnum, e.empname, prodname, invqty, invprice
         from employe as e, invoice as i
         where e.empnum = i.empnum
         order by e.empname;

   title2 'Employee numbers under 300';
   select *
      from sales
      where empnum < 300
      order by empname;


 /*
 * This example demonstrates the CREATE TABLE with nested INLINE-VIEWS.
 * The table expressions for table1 and table2 are in-line views.
 */

   create table nosales as
   select distinct prodname
     from invoice
    where prodname not in
           (select table1.prodname
              from (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') as table1,
                   (select distinct prodname
                      from invoice as i, employe as e
                     where e.empnum  = i.empnum and
                           e.empcity = 'Virginia Beach') as table2
              where table1.prodname = table2.prodname)
     order by 1;


   title2 'Nosales table';
   select * from nosales;


 /*=================================================================*/
 /*==                      GROUP BY clause                        ==*/
 /*==                      HAVING clause                          ==*/
 /*=================================================================*/

 /*
 * This example demonstrates the GROUP BY clause.  A GROUP BY clause
 * is used in queries that include one or more summary functions.
 */

   title2 'Average number of years of service';
   select emptitle as title,
          avg(empyears) label='Average Years' format=6.0,
          freq(empnum)  label='Head Count'
     from employe
     group by title
     order by title;


 /*
 * In this example, the COUNT function is used to count the stores.
 */

   title2 'Customers who have four stores';
   select custname, custnum, custcity
      from customr
      where custname in
            (select custname
                from customr
                group by custname
                having count(*) = 4)
      order by 1, 2;


 /*
 * The having clause is a "where clause" for each group defined
 * by the group by clause.
 */

   title2 'Customers with one store';
   select custname as name, count(*)
     from customr
    group by name
   having count(*) = 1;

   title2 'Old timers';
   select empname, emptitle, empyears
     from employe
     group by emptitle
     having empyears > avg(empyears);



 /*=================================================================*/
 /*==              SQL and SAS macro language                     ==*/
 /*=================================================================*/

 /*
 * This example retrieves a value from the database, and places
 * it in a SAS macro variable. It is then used in a title statement.
 */

   reset  noprint;
   select max(invqty*invprice)
      into :maxsale
      from invoice;

   reset print;

   title2 "The best sale totalled &maxsale";
   select * from invoice;



 /*=================================================================*/
 /*==                      IN predicate                           ==*/
 /*=================================================================*/

   title2 'WHO LIVES IN WILMINGTON OR CHARLESTON?';
   select empname
     from employe
    where empcity in('Wilmington', 'Charleston');


 /*
 * This example demonstrates the use of an IN condition on
 * the SELECT clause. The IN predicate returns 1 or 0 depending
 * on whether it is true for the current set of variables or not.
 */

   title2 'EMPLOYEE CITY INFORMATION';
   select distinct empcity,
          empcity in('Wilmington', 'Charleston')
   from employe;


   title2 'WHICH CITIES HAVE NO MANAGER?';
   select distinct empcity
      from employe
      where empcity not in (select empcity
                               from employe
                               where emptitle = 'manager');


 /*=================================================================*/
 /*==                  IS MISSING predicate                       ==*/
 /*=================================================================*/

   title2 'WHO HAS NO BOSS?';
   select *
      from employe
      where empboss is null;


 /*=================================================================*/
 /*==                        Joining Tables                       ==*/
 /*=================================================================*/

 /*
 * These examples demonstrate how to JOIN tables.
 */

data left;
   length lname $12 lcity $12;
   input key lname lcity;
   cards;
   1 Lewis Durham
   2 Cummings Raleigh
   2 Kent Cary
   3 Eaton Durham
   ;

proc sql;

   title2 'Left Table';
   select * from left;

data right;
   length rname $12 rcity $12;
   input key rname rcity;
   cards;
   1 Johnston Durham
   2 Dean Cary
   2 Corrigan Raleigh
   4 Gomez Cary
   ;

proc sql;

   title2 'Right Table';
   select * from right;


 /*
 * This is an example of an EQUIJOIN. It includes only those rows that
 * have a match in the other dataset, as specfied by the where clause.
 * Traditionally inner joins use the "," and put the join conditions
 * in the where clause.
 */

   title2 'INNER JOIN';
   select *
     from left inner join right
       on left.key=right.key;

   select *
      from left, right
      where left.key=right.key;

 /*
 * This is an example of a LEFT OUTER JOIN.
 * Any rows in the left-hand table that do not have a match will
 * still be in the resulting output.
 */

   title2 'LEFT OUTER JOIN';
   select *
     from left left join right
       on left.key=right.key;

 /*
 * This is an example of a RIGHT OUTER JOIN.
 * Any rows in the right-hand table that do not have a match will
 * still be in the resulting output.
 */

   title2 'RIGHT OUTER JOIN';
   select *
     from left right join right
       on left.key=right.key;


 /*
 * This is an example of a FULL OUTER JOIN.
 * Any rows in either table that do not have a match will
 * still be in the resulting output.
 */


   title2 'FULL OUTER JOIN';
   select *
     from left full join right