sqlug3.sas

SAS是功能強大的統計軟體

 /*
 * This is an example of when data is REMERGED. This remerging is
 * useful when you wish to compute "as a fraction of the total"
 * type of expressions. Here we are interested in the contribution
 * of each customer to our total revenue, expressed as a percentage.
 */

   title2 'Invoice information sorted by store';
   select custname, custnum, invnum,
     (100*invqty*invprice)/sum(invqty*invprice) as percent format=9.6
     from invoice
    group by custname
    order by custname, percent desc;


 /*
 * This example combines a number of components including summary
 * functions, a HAVING expression with a subquery, and an in-line
 * view within that subquery.
 */

   title2 'Employee cities with the greatest dollar sales';
   select e.empcity, sum(invqty*invprice) as sales format=dollar.
     from employee e, invoice i
    where e.empnum = i.empnum
    group by empcity
   having sales = ( select max(sales)
                      from ( select e.empcity,
                                    sum(invqty*invprice) as sales
                               from employee e, invoice i
                              where e.empnum = i.empnum
                              group by empcity
                           )
                  )
      order by 1;


 /*==============================================================*/
 /*==                SQL and set operations.                   ==*/
 /*==============================================================*/

 /*
 * This example uses the UNION operator to list products that were
 * sold to stores in Myrtle Beach or sold by salesreps who live in
 * Virginia Beach or both.  Duplicate rows are automatically
 * eliminated from the result table.
 */


   title2 'Product information using UNION';
   select 'Salesrep in Virginia Beach' as who, prodname as product
     from invoice
    where  'Virginia Beach' = ( select empcity
                                  from employee
                                 where empnum = invoice.empnum
                              )

   union

   select 'Store in Myrtle Beach' as who, prodname as product
     from invoice
    where  'Myrtle Beach' = ( select custcity
                                from customer
                               where custname = invoice.custname
                                 and custnum  = invoice.custnum
                            )
   order by 1, 2;


 /*
 * This example uses the EXCEPT operator to list products that were
 * sold by salesreps who live in Virginia Beach but were not sold
 * to stores in Myrtle Beach.
 */

   title2 'Product information using EXCEPT';
   select prodname as product
     from invoice
    where  'Virginia Beach' = ( select empcity
                                  from employee
                                 where empnum = invoice.empnum
                              )

   except

   select prodname as product
     from invoice
    where 'Myrtle Beach' = ( select custcity
                               from customer
                              where custname = invoice.custname
                                and custnum  = invoice.custnum
                           )
   order by 1;


 /*=================================================================*/
 /*==                  SQL and n-way joins.                       ==*/
 /*=================================================================*/


 /*
 * This example shows how a join on four tables (a four-way join)
 * is performed.  The following query shows sales figures of items
 * priced over $150 that were sold by each manager's group
 * (salesreps) in the sample wholesale company.
 */

   title2 'Sales figures';
   select e1.empname as manager,
          count(i.invnum)            as numsales,
          sum(i.invqty * i.invprice) as totsales,
          sum(i.invqty * p.prodcost) as totcost,
          sum(i.invqty * (i.invprice - p.prodcost)) as totmarg,
          sum(i.invqty * (i.invprice - p.prodcost)) /
          sum(i.invqty * i.invprice) as pctmarg
     from invoice i, product p,
          employee e1, employee e2
    where i.prodname = p.prodname
      and p.prodcost > 150
      and i.empnum   = e2.empnum
      and e1.emptitle = 'manager'
      and e1.empnum = e2.empboss
    group by  manager;

 /*
 * These next three examples break down the four-way join
 * into separate(intermediate) two-way joins.
 */

   create table t1 as
   select p.prodcost, i.empnum, i.invnum, i.invqty, i.invprice
     from invoice i, product p
    where i.prodname = p.prodname
      and p.prodcost > 150;

   title2 'INTERMEDIATE RESULT T1';
   select * from t1;

   create table t2 as
   select prodcost, empboss, invnum, invqty, invprice
     from t1, employee e
    where t1.empnum = e.empnum;

   title2 'INTERMEDIATE RESULT T2';
   select * from t2;

   create table t3 as
   select prodcost, empname as manager, invnum, invqty, invprice
     from t2, employee e
    where t2.empboss = e.empnum and e.emptitle = 'manager';

   title2 'INTERMEDIATE RESULT T3';
   select * from t3;

 /*
 * This example is the final breakdown of the four-way join.  The
 * intermediate table T3 now includes only those rows that satisfy
 * the conditions in the original query's WHERE expression.  The
 * query can now be evaluated using the T3 table.
 */

   title2 'FINAL RESULT';
   select manager,
          count(invnum)          as numsales,
          sum(invqty * invprice) as totsales,
          sum(invqty * prodcost) as totcost,
          sum(invqty * (invprice - prodcost)) as totmarg,
          sum(invqty * (invprice - prodcost)) /
              sum(invqty * invprice) as pctmarg
     from t3
    group by manager;




 /*=============================================================*/
 /*==                     SQL and indexes.                    ==*/
 /*=============================================================*/

 /*
 * This example defines INDEXES on two columns in the INVOICE
 * table.  The option STIMER is used to demonstrate the
 * efficiency of indexes.
 */

   reset stimer;

   title2 'Before indexes are defined';
   select e1.empname as manager,
          count(i.invnum)            as numsales,
          sum(i.invqty * i.invprice) as totsales,
          sum(i.invqty * p.prodcost) as totcost,
          sum(i.invqty * (i.invprice - p.prodcost)) as totmarg,
          sum(i.invqty * (i.invprice - p.prodcost)) /
             sum(i.invqty * i.invprice) as pctmarg
     from invoice i, product p,
          employee e1, employee e2
    where i.prodname = p.prodname
      and p.prodcost > 150
      and i.empnum   = e2.empnum
      and e1.emptitle = 'manager'
      and e1.empnum = e2.empboss
    group by  manager;


   create index empnum on invoice(empnum);
   create index prodname on invoice(prodname);

   title2 'After indexes are defined';
   select e1.empname as manager,
          count(i.invnum)            as numsales,
          sum(i.invqty * i.invprice) as totsales,
          sum(i.invqty * p.prodcost) as totcost,
          sum(i.invqty * (i.invprice - p.prodcost)) as totmarg,
          sum(i.invqty * (i.invprice - p.prodcost)) /
             sum(i.invqty * i.invprice) as pctmarg
     from invoice i, product p,
          employee e1, employee e2
    where i.prodname = p.prodname
      and p.prodcost > 150
      and i.empnum   = e2.empnum
      and e1.emptitle = 'manager'
      and e1.empnum = e2.empboss
    group by  manager;


    drop index empnum, prodname from invoice;


 /* This example shows that performance can be enhanced when     *
 * indexes are defined on columns that participate in equijoins *
 * on a frequent basis.                                         */

data twoK;   do i = 1 to 2000; output; end; run;
data fiftyK; do i = 1 to 50000; output; end; run;

proc sql stimer;
   title2 'BEFORE INDEX CREATED';
   select count(*) from twoK t, fiftyK f where t.i=f.i;

   create unique index i on fiftyK(i);

   title2 'AFTER INDEX CREATED';
   select count(*) from twoK t, fiftyK f where t.i=f.i;


 /*=============================================================*/
 /*==                   SQL and outer joins.                  ==*/
 /*=============================================================*/

 /*
 * In this example, a LEFT OUTER JOIN is used to list all the
 * products and sales (if any) of those products to the
 * Beach Land store.
 */

proc sql;
   title2 'Beach Land store sales';
   select p.prodname, i.invqty, i.invprice, p.prodlist,
          i.invqty*i.invprice format=dollar. label='Invoice Amount'
     from product p left join invoice i
     on p.prodname=i.prodname and i.custname='Beach Land';


 /*
 * This next example creates four tables and performs a FULL OUTER
 * JOIN on them.  The COALESCE function returns the first argument
 * whose value is not a SAS missing value.
 */

data j1;
   input x1 j1$ @@;
   cards;
   0 j1_0 1 j1_1
data j2;
   input x2 j2$ @@;
   cards;
   0 j2_0 2 j2_2
data j3;
   input x3 j3$ @@;
   cards;
   0 j3_0 3 j3_3
data j4;
   input x4 j4$ @@;
   cards;
   0 j4_0 4 j4_4
   ;

proc sql;
   title2;
   select coalesce(x1,x2,x3,x4) as x,j1,j2,j3,j4
     from j1 full join j2 on x1=x2
             full join j3 on x1=x3
             full join j4 on x1=x4;

   select coalesce(x1,x2,x3,x4) as x,j1,j2,j3,j4
     from j1 full join j2 on x1=x2
             full join j3 on x1=x3
             full join j4 on x1=x4
   order by 1;


 /*=============================================================*/
 /*==              Equivalent SQL queries.                    ==*/
 /*=============================================================*/


 /*
 * The next three examples demonstrate how different queries can
 * produce the same results.  Each of the following queries lists
 * the customers who have the greatest number of stores.
 *
 * Query one compares a correlated subquery with a subquery that
 * includes an in-line view in the WHERE expression.
 *
 * Query two uses one subquery with an in-line view as the
 * predicate in the HAVING expression.
 *
 * Query Three creates a temporary table and performs a query
 * on it.
 */

   reset stimer;

   title2 'QUERY ONE';
   select distinct custname
     from customer c1
    where ( select count(*)
              from customer c2
              where c2.custname = c1.custname
          )
          =
          ( select max(numstore)
              from ( select count(*) as numstore
                       from customer
                      group by custname
                   )
          )
    order by 1;


   title2 'QUERY TWO';
   select distinct custname, count(*)
     from customer c1
    group by custname
   having count(*) = ( select max(numstore)
                         from ( select count(*) as numstore
                                  from customer
                                 group by custname
                              )
                     )
    order by 1;


   title2 'QUERY THREE';
   create table temp as
   select custname, count(*) as numstore
     from customer
    group by custname;

   select distinct custname, numstore
     from temp
    where numstore = (select max(numstore) from temp)
     order by 1;

   reset nostimer;


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


 /*
 * This example creates a table listing people who are qualified
 * to serve as referees for a review of academic papers.  No more
 * than three people per subject are required in a table.  The SAS
 * macro language is used to check the count of those people
 * qualified to referee a subject before inserting a new person.
 */

proc sql;
   create table referee
     ( name    char(15),
       subject char(15)
     );

 /* This example defines a SAS macro to insert people into the  *
  * table.  The macro has two parameters: the person's name and *
  * the subject he is qualified to referee.                     */

%macro  addref(name,subject);

  %local count;

  /*-----------------------------------------------------------*/
  /*-- Do we have three people who can referee this subject? --*/
  /*-----------------------------------------------------------*/

  reset noprint;
  select count(*)
    into :count
    from referee
   where subject = "&subject";

  /*-----------------------------------------------------------*/
  /*-- Yes, we do. so lets show the user who they are..      --*/
  /*-----------------------------------------------------------*/

  %if &count >= 3 %then %do;
    reset print;
    title2  "ERROR: &name not inserted for subject - &subject..";
    title3 "       There are 3 referees already.";

    select * from referee where subject = "&subject";
    reset noprint;
    %end;

  /*-----------------------------------------------------------*/
  /*-- Nope, so lets add this person as a referee..          --*/
  /*-----------------------------------------------------------*/

  %else %do;
    insert into referee(name,subject)
       values("&name","&subject");
    %put Note: &name has been added for subject - &subject..;
    %end;

  %mend;


 /*
 * we now use our macro to add people to the table.
 */

%addref(Conner,sailing);
%addref(Fay,sailing);
%addref(Einstein,relativity);
%addref(Smythe,sailing);
%addref(Naish,sailing);

quit;

 /*=============================================================*/
 /*==           SQL and SAS/FSP SCL Language.                 ==*/
 /*=============================================================*/


 /*
 * The following example assumes you have built your primary menu
 * screen and are using SCL to write the source portion of your
 * program entry.  The SAS/AF program screen is designed with two
 * fields, name and subject.  The program associated with the screen
 * is as follows:
 */


 /*  Use this code as the SCL source

INIT:
   control always;
   return;

MAIN:
    submit continue sql;
     *-----------------------------------------------------------*
     *-- Do we have three people who can referee this subject? --*
     *-----------------------------------------------------------*
    proc sql;
    reset  noprint;
    select count(*)
    into  :count
    from referee
           where subject = "&subject";
    endsubmit;

     *-----------------------------------------------------------*
     *-- Yes, we do. so lets tell the user.                    --*
     *-----------------------------------------------------------*

    if symget('count') >= 3 then
       _msg_ = 'ERROR: ' || trim(name) || ' not inserted.';

     *-----------------------------------------------------------*
     *-- Nope, so lets add this person as a referee..          --*
     *-----------------------------------------------------------*

    else do;
       submit continue sql;
          insert into referee values("&name","&subject");
       endsubmit;
       _msg_ = "NOTE: ' || trim(name) || ' has beed added for '
               || subject";
    end;

TERM:

End of SCL Source */