/****************************************************************/ /*          S A S   S A M P L E   L I B R A R Y                 */ /*                                                              */ /*    NAME: SQLUG2                                              */ /*   TITLE: EXAMPLES FROM CHAPTER TWO OF SQL USER'S GUIDE       */ /* PRODUCT: BASE                                                */ /*  SYSTEM: ALL                                                 */ /*    KEYS: SQL DATMAN SQLV61 MACRO FEEDBACK PRINT SUM GROUP in */ /*          ORDER BY WHERE LIKE BETWEEN NULL HAVING VALIDATE    */ /*   PROCS: SQL                                                 */ /*    DATA:                                                     */ /*                                                              */ /* SUPPORT: KMS, PMK                    UPDATE:                 */ /*     REF:                                                     */ /*    MISC:                                                     */ /*                                                              */ /****************************************************************/  title1 '*** sqlug2: SQL User Guide chapter two examples ***'; /*--------------------------------------------------------------*/ /*-- The following data steps create the permanent datasets   --*/ /*-- used in the SQL User's Guide examples.                   --*/ /*--------------------------------------------------------------*/data invoice;   input invnum custname $ 6-15 custnum empnum prodname $ 28-37      invqty invprice;   format invprice dollar.;   cards;280  Beach Land   16  215  snorkel      20    14290  Beach Land   16  216  flippers     15    19300  Beach Land   16  216  raft         20     7310  Coast Shop    3  318  windsurfer    2  1305320  Coast Shop    3  318  raft         30     6330  Coast Shop    5  318  snorkel       5    15340  Coast Shop    5  318  flippers     15    19350  Coast Shop    5  318  raft         40     6360  Coast Shop    5  318  snorkel      10    15370  Coast Shop   12  213  raft         10     7380  Coast Shop   14  417  windsurfer    1  1325390  Del Mar       3  417  flippers     30    18400  Del Mar       3  417  kayak         3   230410  Del Mar       8  417  raft         40     6420  Del Mar      11  417  raft         15     7430  Del Mar      11  417  snorkel      10    15440  Del Mar      11  417  flippers     20    19450  New Waves     3  215  flippers      5    20460  New Waves     3  215  flippers     10    20470  New Waves     6  213  snorkel      15    15480  New Waves     6  213  surfboard     4   735490  New Waves     6  213  snorkel      10    15500  Surf Mart   101  417  snorkel      20    14510  Surf Mart   101  417  surfboard     2   740520  Surf Mart   101  417  snorkel      12    15530  Surf Mart   118  318  flippers     15    19540  Surf Mart   118  318  raft         30     6550  Surf Mart   118  318  snorkel      10    15560  Surf Mart   127  314  flippers     25    19570  Surf Mart   127  314  surfboard     3   740run;data product;   input prodname $ 1-10 prodcost prodlist;   format prodcost prodlist dollar.;   cards;flippers      16    20jet ski     2150  2675kayak        190   240raft           5     7snorkel       12    15surfboard    615   750windsurfer  1090  1325run;data customer;   input custname $ 1-10 custnum custcity $ 22-36;   cards;Beach Land       16  Ocean CityCoast Shop        3  Myrtle BeachCoast Shop        5  Myrtle BeachCoast Shop       12  Virginia BeachCoast Shop       14  CharlestonDel Mar           3  Folly BeachDel Mar           8  CharlestonDel Mar          11  CharlestonNew Waves         3  Ocean CityNew Waves         6  Virginia BeachSea Sports        8  CharlestonSea Sports       20  Virginia BeachSurf Mart       101  CharlestonSurf Mart       118  SurfsideSurf Mart       127  Ocean IsleSurf Mart       133  Charlestonrun;data employee;   input empnum empname $ empyears empcity $ 20-34   emptitle $ 36-45 empboss;   cards;101   Herb     28  Ocean City      president     .201   Betty     8  Ocean City      manager     101213   Joe       2  Virginia Beach  salesrep    201214   Jeff      1  Virginia Beach  salesrep    201215   Wanda    10  Ocean City      salesrep    201216   Fred      6  Ocean City      salesrep    201301   Sally     9  Wilmington      manager     101314   Marvin    5  Wilmington      salesrep    301318   Nick      1  Myrtle Beach    salesrep    301401   Chuck    12  Charleston      manager     101417   Sam       7  Charleston      salesrep    401run; /*--------------------------------------------------------------*/ /*-- EXAMPLES FROM CHAPTER TWO OF SQL USER'S GUIDE BEGIN HERE --*/ /*--------------------------------------------------------------*/ /* * This example selects and displays the list price and product * name from the product table using proc print and proc sql. */proc print data=product noobs;run;proc sql;   title2 'List Prices and Product Names';   select prodlist, prodname     from product; /* * In this example an asterick (*) is used in place of column names * to select all the columns from the product table.  The order of * the columns displayed matches the order of the columns in the * table.  The FEEDBACK option writes the expanded form of the * SELECT on the SAS log. */   proc sql feedback;   select *      from product; /* * This example demonstrates the SUM function and the use of a * column alias.  This SELECT statement selects salesreps and * displays the salesrep city and the total number of service * years by city, sorted by the total number of service years. */   proc sql;   title2 'Salesrep information, sorted by years of service';   select empcity, sum(empyears) as totyears     from employee    where emptitle = 'salesrep'    group by empcity    order by totyears; /* * This example demonstrates that arithmetic expressions in the * SELECT clause can perform computations on numeric columns.  The * query below displays the percentage profit margin for each * product sold at fulllist price. */   title2 'Percentage of profit margin for each product';   proc sql;   select prodname, prodlist, prodcost,          (prodlist - prodcost) / prodlist     from product; /* * The following query selects all the columns and rows * from the Customer table and displays the resulting table. */   title2 'Customer Table';   proc sql;   select * from customer; /* * This example demonstrates GROUP BY.  In this query, the results * of the summary function are grouped by the city name, that is, * the number of EMPYEAR for each city are added using the SUM * function. */   title2 'Total years of service, by city';   select empcity, sum(empyears) as totyears     from employee    where emptitle = 'salesrep'    group by empcity; /* * This example demonstrates ORDER BY.  The query below displays * the Product table sorted by ascending list price. */   title2 'Product table sorted by list price';   proc sql;   select prodname, prodlist, prodcost     from product    order by prodlist; /* * This example demonstrates ORDER BY.  The query computes the * percentage profit margin for each product sold at full list * price.  The results are sorted in descending order by the * fourth column in the SELECT statement and in ascending order * by prodcost when the values of the fourth column have the same * value. */   title2 'Profit Margins';   proc sql;   select prodname, prodlist, prodcost,          (prodlist - prodcost) / prodlist     from product    order by 4 desc, prodcost asc; /* * This is an example of a WHERE expression using a comparison * operator.  This query on the Employee table displays only * those employees who have ten or more years of service. */   title2 'Employess with 10 or more years of service';   proc sql;   select empname, emptitle     from employee    where empyears >= 10    order by empname; /* * This is an example of a COMPOUND predicate.  The query * below displays employees who neither live in Ocean City * nor function as salesreps but who have more than ten * service years. */   title2 'EMPLOYEES WHO DO NOT LIVE IN OCEAN CITY NOR FUNCTION';   title3 'AS A SALESREP BUT HAVE MORE THAN TEN YEARS OF SERVICE';   proc sql;   select empname, emptitle, empcity, empyears     from employee    where (not (empcity = 'Ocean City' or emptitle = 'salesrep'))      and (empyears > 10); /* * These examples demonstrate the like operator used for pattern * matching.  This query displays each employee whose name begins * with the uppercase letter "S." */   title2 'Employees whose name begins with "S"';   proc sql;   select empname     from employee    where empname like 'S%'; /* * The query below selects each employee whose name ends with a * lowercase "k" and consists of exactly four letters. */   title2 'Employees who have a four letter name ending in "k"';   select empname     from employee    where empname like '___k'; /* * This example demonstrates a WHERE expression which tests for a * membership in a set of values.  This query displays employees * who have worked for one, five, or ten years. */   title2 'Employees who have worked for one, five, or ten years';   proc sql;   select empname, empyears     from employee    where empyears in (1,5,10); /* * This example demonstrates a WHERE expression testing for a * range of values. * In the query the BETWEEN range selects and displays all the rows * whose employee numbers fall between the employee numbers 301 and * 401, including these numbers. */   title2 'Employee numbers between 301 AND 401';   proc sql;