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;