1 / 47

What Is SQL?

What Is SQL?. http://support.sas.com/91doc/getDoc/proc.hlp/a000086336.htm. Structured Query Language (SQL) is a standardized, widely used language that retrieves and updates data in relational tables and databases.

millermelvin
Télécharger la présentation

What Is SQL?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What Is SQL? http://support.sas.com/91doc/getDoc/proc.hlp/a000086336.htm • Structured Query Language (SQL) is a standardized, widely used language that retrieves and updates data in relational tables and databases. • A relationis a mathematical concept. Relations are represented physically as two-dimensional tables that are arranged in rows and columns. • The Structured Query Language is now in the public domain and is part of many vendors’ products.

  2. PROC SQL • generate reports • generate summary statistics • retrieve data from tables or views • combine data from tables or views • create tables, views, and indexes • update the data values in PROC SQL tables • update and retrieve data from database management system (DBMS) tables • modify a PROC SQL table by adding, modifying, or dropping columns.

  3. Table, Query and View • Tables • QueriesQueries retrieve data from a table, view, or DBMS. A query returns a query result,which consists of rows and columns from a table. With PROC SQL, you use a SELECT statement and its subordinate clauses to form a query. • ViewsPROC SQL views do not actually contain data as tables do. Rather, a PROC SQL view contains a stored SELECT statement or query. The query executes when you use the view in a SAS procedure or DATA step.

  4. PROC SQL <option(s)>; ALTER TABLEtable-name <ADD <CONSTRAINT> constraint-clause<, ... constraint-clause>> <ADD column-definition<, ... column-definition>> <DROP CONSTRAINTconstraint-name <, ... constraint-name>> <DROPcolumn<, ... column>> <DROP FOREIGN KEYconstraint-name> <DROP PRIMARY KEY> <MODIFY column-definition<, ... column-definition>> ; CREATE <UNIQUE> INDEXindex-nameONtable-name ( column <, ... column>); CREATE TABLEtable-name (column-specification<, ...column-specification | constraint-specification>) ; CREATE TABLE table-nameLIKEtable-name2; CREATE TABLEtable-nameAS query-expression <ORDER BYorder-by-item<, ... order-by-item>>; CREATE VIEWproc-sql-viewAS query-expression <ORDER BYorder-by-item<, ... order-by-item>> <USINGlibname-clause<, ... libname-clause>> ; DELETE FROMtable-name|proc-sql-view |sas/access-view <AS alias> <WHERE sql-expression>; DESCRIBE TABLEtable-name <, ... table-name>; DESCRIBE VIEWproc-sql-view <, ... proc-sql-view>; DESCRIBE TABLE CONSTRAINTStable-name <, ... table-name>; DROP INDEX index-name <, ... index-name> FROMtable-name; DROP TABLEtable-name <, ... table-name>; DROP VIEWview-name <, ... view-name>; INSERT INTOtable-name|sas/access-view|proc-sql-view <(column<, ... column>)> SETcolumn=sql-expression <, ... column=sql-expression> <SETcolumn=sql-expression <, ... column=sql-expression>>; INSERT INTOtable-name|sas/access-view|proc-sql-view <(column<, ... column>)> VALUES (value <, ... value>) <... VALUES (value <, ... value>)>; INSERT INTOtable-name|sas/access-view|proc-sql-view <(column<, ...column>)> query-expression; RESET <option(s)>; SELECT <DISTINCT> object-item <, ...object-item> <INTOmacro-variable-specification <, ... macro-variable-pecification>> FROMfrom-list <WHERE sql-expression> <GROUP BYgroup-by-item <, ... group-by-item>> <HAVING sql-expression> <ORDER BYorder-by-item <, ... order-by-item>>; UPDATEtable-name|sas/access-view|proc-sql-view <ASalias> SETcolumn=sql-expression <, ... column=sql-expression> <SETcolumn=sql-expression <, ... column=sql-expression>> <WHERE sql-expression>; VALIDATE query-expression;

  5. Overview of the SELECT Statement • retrieve data from a single table by using the SELECT statement validate the correctness of a SELECT statement by using the VALIDATE statement. • With the SELECT statement, you can retrieve data from tables or data that is described by SAS data views. • The SELECT statement is the primary tool of PROC SQL. • You use it to identify, retrieve, and manipulate columns of data from a table. • You can also use several optional clauses within the SELECT statement to place restrictions on a query.

  6. SELECT and FROM Clauses • The following simple SELECT statement is sufficient to produce a useful result: select Name from data; • The SELECT statement must contain a SELECT clause and a FROM clause, both of which are required in a PROC SQL query. • This SELECT statement contains a SELECT clause that lists the Name column a FROM clause that lists the table in which the Name column resides.

  7. WHERE Clause • The WHERE clause enables you to restrict the data that you retrieve by specifying a condition that each row of the table must satisfy. • PROC SQL output includes only those rows that satisfy the condition. • The following SELECT statement contains a WHERE clause that restricts the query output to only those countries that have a population that is greater than 5,000,000 people: select Name from sql.countries where Population gt 5000000;

  8. ORDER BY Clause • The ORDER BY clause enables you to sort the output from a table by one or more columns; that is, you can put character values in either ascending or descending alphabetical order, and you can put numerical values in either ascending or descending numerical order. • The default order is ascending. • For example, you can modify the previous example to list the data by descending population: select Name from sql.countries where Population gt 5000000 order by Population desc;

  9. GROUP BY Clause • The GROUP BY clause enables you to break query results into subsets of rows. • When you use the GROUP BY clause, you use an aggregate function in the SELECT clause or a HAVING clause to instruct PROC SQL how to group the data. • For details about aggregate functions, see Summarizing Data. • PROC SQL calculates the aggregate function separately for each group. • When you do not use an aggregate function, PROC SQL treats the GROUP BY clause as if it were an ORDER BY clause, and any aggregate functions are applied to the entire table. • The following query uses the SUM function to list the total population of each continent. The GROUP BY clause groups the countries by continent, and the ORDER BY clause puts the continents in alphabetical order: select Continent, sum(Population) from sql.countries group by Continent order by Continent;

  10. HAVING Clause • The HAVING clause works with the GROUP BY clause to restrict the groups in a query's results based on a given condition. • PROC SQL applies the HAVING condition after grouping the data and applying aggregate functions. • For example, the following query restricts the groups to include only the continents of Asia and Europe: select Continent, sum(Population) from sql.countries group by Continent having Continent in ('Asia', 'Europe') order by Continent;

  11. Ordering the SELECT Statement • When you construct a SELECT statement, you must specify the clauses in the following order: • SELECT • FROM • WHERE • GROUP BY • HAVING • ORDER BY • Note:   Only the SELECT and FROM clauses are required

  12. A relational database Downloaded at http://www.buffalo.edu/~cxma/STA511/Northwind.zip

  13. EXAMPLES /* Create a empty dataset (0 record) */ PROCSQL; CREATETABLE PURCHASES (CUSTNUM CHAR(4), ITEM CHAR(10), UNITS NUM, UNITCOST NUM(8,2)); QUIT;

  14. /* Create a empty dataset using data step (1 record)*/ DATA PURCHASES; LENGTH CUSTNUM $4. ITEM $10. UNITS 3. UNITCOST 4.; LABEL CUSTNUM = 'Customer Number' ITEM = 'Item Purchased' UNITS = '# Units Purchased' UNITCOST = 'Unit Cost'; FORMAT UNITCOST DOLLAR12.2; RUN; PROCCONTENTSDATA=PURCHASES; RUN;

  15. DATA FOR EXAMPLES /*******************/ /* CUSTOMERS TABLE */ /*******************/ DATA CUSTOMERS; INFILE CARDS MISSOVER; INPUT @1 CUSTNUM 4. @7 CUSTNAME $25. @36 CUSTCITY $20.; CARDS; 101 La Mesa Computer Land La Mesa 201 Vista Tech Center Vista 301 Coronado Internet Zone Coronado 401 La Jolla Computing La Jolla 501 Alpine Technical Center Alpine 601 Oceanside Computer Land Oceanside 701 San Diego Byte Store San Diego 801 Jamul Hardware & Software Jamul 901 Del Mar Tech Center Del Mar 1001 Lakeside Software Center Lakeside 1101 Bonsall Network Store Bonsall 1201 Rancho Santa Fe Tech Rancho Santa Fe 1301 Spring Valley Byte Center Spring Valley 1401 Poway Central Poway 1501 Valley Center Tech Center Valley Center 1601 Fairbanks Tech USA Fairbanks Ranch 1701 Blossom Valley Tech Blossom Valley 1801 Chula Vista Networks ; RUN; /********************/ /* CUSTOMERS2 TABLE */ /********************/ DATA CUSTOMERS2; INFILE CARDS MISSOVER; INPUT @1 CUSTNUM 2. @5 CUSTNAME $10. @17 CUSTCITY $20.; CARDS; 1 Smith San Diego 7 Lafler Spring Valley 11 Jones Carmel 13 Thompson Miami 7 Loffler Spring Valley 1 Smithe San Diego 7 Laughler Spring Valley 7 Laffler Spring Valley ; RUN; /*******************/ /* INVENTORY TABLE */ /*******************/ DATA INVENTORY; INFILE CARDS MISSOVER; INPUT @1 PRODNUM 4. @8 INVENQTY 2. @13 ORDDATE MMDDYY10. @27 INVENCST COMMA10.2 @39 MANUNUM 3.; FORMAT INVENCST DOLLAR10.2 ORDDATE MMDDYY10.; CARDS; 1110 20 09/01/2000 45,000.00 111 1700 10 08/15/2000 28,000.00 170 5001 5 08/15/2000 1,000.00 500 5002 3 08/15/2000 900.00 500 5003 10 08/15/2000 2,000.00 500 5004 20 09/01/2000 1,400.00 500 5001 2 09/01/2000 1,200.00 600 ; RUN; /*****************/ /* INVOICE TABLE */ /*****************/ DATA INVOICE; INFILE CARDS MISSOVER; INPUT @1 INVNUM 4. @7 MANUNUM 3. @13 CUSTNUM 4. @20 INVQTY 2. @25 INVPRICE COMMA10.2 @37 PRODNUM 4.; FORMAT INVPRICE DOLLAR12.2; CARDS; 1001 500 201 5 1,495.00 5001 1002 600 1301 2 1,598.00 6001 1003 210 101 7 245.00 2101 1004 111 501 3 9,600.00 1110 1005 500 801 2 798.00 5002 1006 500 901 4 396.00 6000 1007 500 401 7 23,100.00 1200 ; RUN; /***********************/ /* MANUFACTURERS TABLE */ /***********************/ DATA MANUFACTURERS; INFILE CARDS MISSOVER; INPUT @1 MANUNUM 3. @6 MANUNAME $22. @29 MANUCITY $12. @41 MANUSTAT $2.; CARDS; 111 Cupid Computer Houston TX 210 Global Comm Corp San Diego CA 600 World Internet Corp Miami FL 120 Storage Devices Inc San Mateo CA 500 KPL Enterprises San Diego CA 700 San Diego PC Planet San Diego CA ; RUN; /******************/ /* PRODUCTS TABLE */ /******************/ DATA PRODUCTS; INFILE CARDS MISSOVER; INPUT @1 PRODNUM 4. @7 PRODNAME $25. @33 MANUNUM 3. @38 PRODTYPE $15. @53 PRODCOST COMMA10.2; FORMAT PRODCOST DOLLAR9.2; CARDS; 1110 Dream Machine 111 Workstation 3,200.00 1200 Business Machine 120 Workstation 3,300.00 1700 Travel Laptop 170 Laptop 3,400.00 2101 Analog Cell Phone 210 Phone 35.00 2102 Digital Cell Phone 210 Phone 175.00 2200 Office Phone 220 Phone 130.00 5001 Spreadsheet Software 500 Software 299.00 5002 Database Software 500 Software 399.00 5003 Wordprocessor Software 500 Software 299.00 5004 Graphics Software 500 Software 299.00 ; RUN; /*******************/ /* PURCHASES TABLE */ /*******************/ DATA PURCHASES; INFILE CARDS MISSOVER; INPUT @1 CUSTNUM 2. @5 ITEM $10. @18 UNITS 2. @21 UNITCOST COMMA12.2; FORMAT UNITCOST DOLLAR12.2; CARDS; 1 Chair 1 179.00 1 Pens 12 0.89 1 Paper 4 6.95 1 Stapler 1 8.95 7 Mouse Pad 1 11.79 7 Pens 24 1.59 13 Markers . 0.99 ; RUN;

  16. /* Example: Creating a new data column */ PROCSQL; SELECT CUSTNUM, ITEM, UNITS, UNITCOST, UNITS * UNITCOST AS TOTAL FROM PURCHASES ORDERBY TOTAL; QUIT; /* Example: Creating Column Aliases */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST * 0.80AS Discount_Price FROM PRODUCTS ORDERBY3; QUIT; DATA _TMP_; SET PURCHASES; TOTAL= UNITS * UNITCOST KEEP CUSTNUM ITEM UNITS UNITCOST TOTAL; RUN; PROC SORT; BY TOTAL; RUN; PROC PRINT; RUN;

  17. /* Example: Eliminates Duplicate Rows*/ PROCSQL; SELECTDISTINCT MANUNUM FROM INVENTORY; QUIT; /* Example: Finding Unique Values, the same as above */ PROCSQL; SELECT UNIQUE MANUNUM FROM INVENTORY; QUIT; /* Example: Comparison Operators, Where Clause */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST FROM PRODUCTS WHERE PRODCOST > 300; QUIT;

  18. /* Example: AND Logical Operator */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST FROM PRODUCTS WHERE PRODTYPE = 'Software'AND PRODCOST > 300; QUIT; /* Example: OR Logical Operator */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST FROM PRODUCTS WHERE PRODTYPE = 'Software' OR PRODCOST > 300; QUIT; /* Example: NOT Logical Operator */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST FROM PRODUCTS WHERE NOT PRODTYPE = 'Software'AND NOT PRODCOST > 300; QUIT;

  19. /* Example: CALCULATED Keyword */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST * 0.80AS DISCOUNT_PRICE FORMAT=DOLLAR9.2, PRODCOST - CALCULATED DISCOUNT_PRICE AS LOSS FORMAT=DOLLAR7.2 FROM PRODUCTS ORDERBY3; QUIT; /* Example: Concatenating Strings Together */ PROCSQL; SELECT MANUCITY || "," || MANUSTAT FROM MANUFACTURERS; QUIT; /* Example: Finding the Length of a String */ PROCSQL; SELECT PRODNUM, PRODNAME, LENGTH(PRODNAME) AS Length FROM PRODUCTS; QUIT;

  20. /* Example: Combining Functions*/ PROCSQL; UPDATE PRODUCTS SET PRODNAME = SCAN(PRODNAME,1) || TRIM(PRODTYPE) WHERE PRODTYPE IN ('Phone'); QUIT; /* Example: LEFT Aligning Characters */ PROCSQL; SELECT LEFT(TRIM(MANUCITY) || ", " || MANUSTAT) FROM MANUFACTURERS; QUIT; /* Example: RIGHT Aligning Characters */ PROCSQL; SELECT RIGHT(MANUCITY) FROM MANUFACTURERS; QUIT;

  21. /* Finding the Occurrence of a Pattern with INDEX */ PROCSQL; SELECT PRODNUM, PRODNAME, PRODTYPE FROM PRODUCTS WHERE INDEX(PRODNAME, 'phone') > 0; QUIT; /* Changing the Case in a String - UPCASE */ PROCSQL; SELECT PRODNUM, PRODNAME, PRODTYPE FROM PRODUCTS WHERE INDEX(UPCASE(PRODNAME), 'PHONE') > 0; QUIT; /* Changing the Case in a String - LOWCASE */ PROCSQL; SELECT PRODNUM, PRODNAME, PRODTYPE FROM PRODUCTS WHERE INDEX(LOWCASE(PRODNAME), ' phone') > 0; QUIT;

  22. /* Extracting Information from a String */ PROCSQL; SELECT PRODNUM, PRODNAME, PRODTYPE, SUBSTR(PRODTYPE,1,4) FROM PRODUCTS WHERE PRODCOST > 100.00; QUIT; /*Phonetic Matching*/ PROCSQL; SELECT CUSTNUM, CUSTNAME, CUSTCITY FROM CUSTOMERS2 WHERE CUSTNAME =* 'Lafler'; QUIT; /* Phonetic Matching*/ PROCSQL; SELECT CUSTNUM, CUSTNAME, CUSTCITY FROM CUSTOMERS2 WHERE CUSTNAME =* 'Lafler' OR CUSTNAME =* 'Laughler' OR CUSTNAME =* 'Lasler'; QUIT;

  23. /*Producing a Row Number with the MONOTONIC() Function */ PROCSQL; SELECT MONOTONIC() AS Row_Number FORMAT=COMMA6., ITEM, UNITS, UNITCOST FROM PURCHASES; QUIT; /*Producing a Row Number with the NUMBER Option */ PROCSQLNUMBER; SELECT ITEM, UNITS, UNITCOST FROM PURCHASES; QUIT;

  24. Summarizing Data: aggregate functions /* Summarizing Data with the COUNT(*) Function */ PROCSQL; SELECT COUNT(*) AS Row_Count FROM PURCHASES; QUIT; /* Summarizing Data with the COUNT Function */ PROCSQL; SELECT COUNT(UNITS) AS Non_Missing_Row_Count FROM PURCHASES; QUIT; /* Summarizing Data with the MIN Function */ PROCSQL; SELECT MIN(prodcost) AS Cheapest Format=dollar9.2Label='Least Expensive' FROM PRODUCTS; QUIT;

  25. /* Summarizing Data with the SUM Function */ PROCSQL; SELECT SUM((UNITS) * (UNITCOST)) AS Total_Purchases FORMAT=DOLLAR6.2 FROM PURCHASES WHERE UPCASE(ITEM)='PENS' OR UPCASE(ITEM)='MARKERS'; QUIT; /* Summarizing Data Down Rows */ PROCSQL; SELECT AVG(PRODCOST) AS Average_Product_Cost FROM PRODUCTS WHERE UPCASE(PRODTYPE) IN ("SOFTWARE"); QUIT;

  26. /* Selecting a Range of Values */ PROCSQL; SELECT PRODNUM, INVENQTY, ORDDATE FROM INVENTORY WHERE (YEAR(ORDDATE) BETWEEN 1999AND2000) OR INVENQTY > 15; QUIT; /*Selecting Non-consecutive Values */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST FROM PRODUCTS WHERE UPCASE(PRODTYPE) IN ('PHONE', 'SOFTWARE'); QUIT; /* Testing for NULL or Missing Values */ PROCSQL; SELECT PRODNUM, INVENQTY, INVENCST FROM INVENTORY WHERE INVENQTY IS NULL; QUIT;

  27. /* Finding Patterns in a String */ PROCSQL; SELECT PRODNAME FROM PRODUCTS WHERE PRODNAME LIKE 'A%'; QUIT; /* Finding Patterns in a String */ PROCSQL; SELECT PRODNAME, PRODTYPE, PRODCOST FROM PRODUCTS WHERE PRODTYPE LIKE '%Soft%'; QUIT; /* Finding Patterns in a String */ PROCSQL; SELECT PRODNAME FROM PRODUCTS WHERE PRODNAME LIKE '% '; QUIT;

  28. /* Finding Patterns in a String */ PROCSQL; SELECT PRODNAME FROM PRODUCTS WHERE PRODNAME LIKE '___a%'; QUIT; /* Testing for the Existence of a Value (sub-query)*/ PROCSQL; SELECT CUSTNUM, CUSTNAME, CUSTCITY FROM CUSTOMERS2 C WHEREEXISTS (SELECT * FROM PURCHASES P WHERE C.CUSTNUM = P.CUSTNUM); QUIT; /* Displaying Dictionary Table Definitions */ PROCSQL; DESCRIBETABLE DICTIONARY.OPTIONS; QUIT;

  29. Using Dictionary /* Dictionary.COLUMNS */ PROCSQL; SELECT * FROM DICTIONARY.COLUMNS WHERE UPCASE(LIBNAME)="WORK"AND UPCASE(NAME)="CUSTNUM"; QUIT; /* Dictionary.MACROS */ PROCSQL; SELECT * FROM DICTIONARY.MACROS WHERE UPCASE(SCOPE)="GLOBAL"; QUIT; /* Dictionary.MEMBERS */ PROCSQL; SELECT * FROM DICTIONARY.MEMBERS WHERE UPCASE(LIBNAME)="WORK"; QUIT;

  30. Grouping /* Grouping Data and Sorting */ PROCSQL; SELECT prodtype, MIN(prodcost) AS Cheapest Format=dollar9.2Label='Least Expensive' FROM PRODUCTS GROUPBY prodtype ORDERBY cheapest; QUIT; /* Subsetting Groups with the HAVING Clause */ PROCSQL; SELECT prodtype, AVG(prodcost) FORMAT=DOLLAR9.2LABEL='Average Product Cost' FROM PRODUCTS GROUPBY prodtype HAVING AVG(prodcost) <= 200.00; QUIT;

  31. Create Data and File /* Sending Output to a SAS Data Set */ ODSLISTINGCLOSE; ODSOUTPUT SQL_Results = SQL_DATA; PROCSQL; TITLE1'Delivering Output to a Data Set'; SELECT prodname, prodtype, prodcost, prodnum FROM PRODUCTS ORDERBY prodtype; QUIT; ODSOUTPUTCLOSE; ODSLISTING; /* Converting Output to Rich Text Format */ ODSLISTINGCLOSE; ODSRTFFILE='c:\SQL_Results.rtf'; PROCSQL; TITLE1'Delivering Output to Rich Text Format'; SELECT prodname, prodtype, prodcost, prodnum FROM PRODUCTS ORDERBY prodtype; QUIT; ODSRTFCLOSE; ODSLISTING;

  32. /* Delivering Results to the Web */ ODSLISTINGCLOSE; ODSHTMLBODY='c:\Products-body.html' CONTENTS='c:\Products-contents.html' PAGE='c:\Products-page.html' FRAME='c:\Products-frame.html'; PROCSQL; TITLE1'Products List'; SELECT prodname, prodtype, prodcost, prodnum FROM PRODUCTS ORDERBY prodtype; QUIT; ODSHTMLCLOSE; ODSLISTING;

  33. /* CASE Expressions */ PROCSQL; SELECT MANUNAME, MANUSTAT, CASE WHEN MANUSTAT = 'CA'THEN'West' WHEN MANUSTAT = 'FL'THEN'East' WHEN MANUSTAT = 'TX'THEN'Central' ELSE'Unknown' ENDAS Region FROM MANUFACTURERS; QUIT; /* CASE Expressions #2 */ PROCSQL; SELECT PRODNAME, CASE PRODTYPE WHEN'Laptop'THEN'Hardware' WHEN'Phone'THEN'Hardware' WHEN'Software'THEN'Software' WHEN'Workstation'THEN'Hardware' ELSE'Unknown' ENDAS Product_Classification FROM PRODUCTS; QUIT; Case Expressions

  34. /* Creating a Macro Variable from a Table Row Column #1 */ PROCSQLNOPRINT; SELECT PRODNAME, PRODCOST INTO :PRODNAME, :PRODCOST FROM PRODUCTS; QUIT; %PUT &PRODNAME &PRODCOST; /* Creating a Macro Variable from a Table Row Column #2 */ PROCSQLNOPRINT; SELECT PRODNAME, PRODCOST INTO :PRODNAME, :PRODCOST FROM PRODUCTS WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'); QUIT; %PUT &PRODNAME &PRODCOST; Create Macro Variables

  35. /* Creating a Macro Variable with Aggregate Functions */ PROCSQLNOPRINT; SELECT MIN(PRODCOST) FORMAT=DOLLAR10.2 INTO :MIN_PRODCOST FROM PRODUCTS; QUIT; %PUT &MIN_PRODCOST; /* Creating Multiple Macro Variables */ PROCSQLNOPRINT; SELECT PRODNAME, PRODCOST INTO :PRODUCT1 - :PRODUCT3, :COST1 - :COST3 FROM PRODUCTS ORDERBY PRODCOST; QUIT; %PUT &PRODUCT1 &COST1; %PUT &PRODUCT2 &COST2; %PUT &PRODUCT3 &COST3;

  36. /* Creating a List of Values in a Macro Variable #1 */ PROCSQLNOPRINT; SELECT MANUNAME INTO :MANUNAME SEPARATED BY' ' FROM MANUFACTURERS WHERE UPCASE(MANUCITY)='SAN DIEGO'; QUIT; %PUT &MANUNAME; /* Creating a List of Values in a Macro Variable #2 */ PROCSQLNOPRINT; SELECT MANUNAME INTO :MANUNAME SEPARATED BY', ' FROM MANUFACTURERS WHERE UPCASE(MANUCITY)='SAN DIEGO'; QUIT; %PUT &MANUNAME;

  37. /* Cross-Referencing Columns */ %MACRO COLUMNS(LIB, COLNAME); PROC SQL; SELECT LIBNAME, MEMNAME, NAME, TYPE, LENGTH FROM DICTIONARY.COLUMNS WHERE LIBNAME="&LIB" AND UPCASE(NAME)="&COLNAME" AND MEMTYPE="DATA"; QUIT; %MEND COLUMNS; %COLUMNS(WORK,CUSTNUM); /* Determining the Number of Rows in a Table */ %MACRO NOBS(LIB, TABLE); PROC SQL; SELECT LIBNAME, MEMNAME, NOBS FROM DICTIONARY.TABLES WHERE UPCASE(LIBNAME)="&LIB" AND UPCASE(MEMNAME)="&TABLE" AND UPCASE(MEMTYPE)="DATA"; QUIT; %MEND NOBS; %NOBS(WORK,PRODUCTS);

  38. /* Identifying Duplicate Rows in a Table */ %MACRO DUPS(LIB, TABLE, GROUPBY); PROC SQL; SELECT &GROUPBY, COUNT(*) AS Duplicate_Rows FROM &LIB..&TABLE GROUP BY &GROUPBY HAVING COUNT(*) > 1; QUIT; %MEND DUPS; %DUPS(WORK,PRODUCTS,PRODTYPE); /* Deriving a Table and Data from an Existing Table*/ PROCSQL; CREATETABLE HOT_PRODUCTS AS SELECT * FROM PRODUCTS WHERE UPCASE(PRODTYPE) IN ("SOFTWARE", "PHONE"); QUIT; /* Adding Data to All the Columns in a Row*/ PROCSQL; INSERTINTO CUSTOMERS (CUSTNUM, CUSTNAME, CUSTCITY) VALUES (702, 'Mission Valley Computing', 'San Diego'); QUIT; Adding DATA

  39. /* Adding Data to All the Columns in a Row #2 */ PROCSQL; INSERTINTO CUSTOMERS (CUSTNUM, CUSTNAME, CUSTCITY) VALUES (402, 'La Jolla Tech Center', 'La Jolla') VALUES (502, 'Alpine Byte Center', 'Alpine') VALUES (1702,'Rancho San Diego Tech','Rancho San Diego'); SELECT * FROM CUSTOMERS ORDERBY CUSTNUM; QUIT; /* Adding Data with a SELECT Query */ PROCSQL; INSERTINTO PRODUCTS (PRODNUM, PRODNAME, PRODTYPE, PRODCOST) SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST FROM SOFTWARE_PRODUCTS WHERE PRODTYPE IN ('Software'); QUIT; /* 5.5.1 Code Example: Deleting a Single Row in a Table */ PROCSQL; DELETEFROM CUSTOMERS WHERE UPCASE(CUSTNAME) = "LAUGHLER"; QUIT;

  40. Change Column /* Changing a Column's Length #2 */ PROCSQL; CREATETABLE MANUFACTURERS_MODIFIED SELECT MANUNUM, MANUNAME, MANUCITY LENGTH=15, MANUSTAT FROM MANUFACTURERS; QUIT; /* Changing a Column's Format */ PROCSQL; ALTERTABLE PRODUCTS MODIFY PRODCOST FORMAT=DOLLAR12.2; QUIT; /* Changing a Column's Label */ PROCSQL; ALTERTABLE PRODUCTS MODIFY PRODCOST LABEL="Retail Product Cost"; QUIT;

  41. Joint Tables /* Cartesian Product Joins */ PROCSQL; SELECT prodname, prodcost, manufacturers.manunum, manuname FROM PRODUCTS, MANUFACTURERS; QUIT; /* Using Table Aliases in Joins */ PROCSQL; SELECT prodnum, prodname, prodtype, M.manunum FROM PRODUCTS P, MANUFACTURERS M WHERE P.manunum = M.manunum AND M.manuname = 'Global Software'; QUIT; /* Left Outer Joins */ PROCSQL; SELECT manuname, manucity, manufacturers.manunum, products.prodtype, products.prodcost FROM MANUFACTURERS LEFT JOIN PRODUCTS ON manufacturers.manunum = products.manunum; QUIT; The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table. The result is NULL from the right side, if there is no match.

  42. /* Specifying a WHERE Clause */ PROCSQL; SELECT manuname, manucity, manufacturers.manunum, products.prodtype, products.prodcost FROM MANUFACTURERS LEFT JOIN PRODUCTS ON manufacturers.manunum = products.manunum WHERE prodcost < 300AND prodcost NE .; QUIT; /* Specifying Aggregate Functions */ PROCSQL; SELECT manuname, SUM(invoice.invprice) AS Total_Invoice_Amt FORMAT=DOLLAR10.2 FROM MANUFACTURERS LEFT JOIN INVOICE ON manufacturers.manunum = invoice.manunum GROUPBY MANUNAME; QUIT;

  43. /* Right Outer Joins */ PROCSQL; SELECT prodname, prodtype, products.manunum, manuname FROM PRODUCTS RIGHT JOIN MANUFACTURERS ON products.manunum = manufacturers.manunum; QUIT; /* Full Outer Joins */ PROCSQL; SELECT prodname, prodtype, products.manunum, manuname FROM PRODUCTS FULL JOIN MANUFACTURERS ON products.manunum = manufacturers.manunum; QUIT; The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

  44. /* Passing a Single Value with a Subquery #1 */ PROCSQL; SELECT * FROM INVOICE WHERE manunum = (SELECT manunum FROM MANUFACTURERS WHERE manuname = 'Global Comm Corp'); QUIT; /* Passing a Single Value with a Subquery #4 */ PROCSQL; SELECT prodnum, invnum, invqty, invprice FROM INVOICE WHERE invqty < (SELECT AVG(invqty) FROM INVOICE); QUIT; /* Passing More Than One Row with a Subquery */ PROCSQL; SELECT * FROM INVOICE WHERE manunum IN (SELECT manunum FROM MANUFACTURERS WHERE UPCASE(manucity) LIKE 'SAN DIEGO%'); QUIT; Sub-Query

  45. /* Accessing Rows from the Combination of Two Queries */ PROCSQL; SELECT * FROM PRODUCTS WHERE prodcost < 300.00 UNION SELECT * FROM PRODUCTS WHERE prodtype = 'Workstation'; QUIT; /* Concatenating Rows from Two Queries */ PROCSQL; SELECT prodnum, prodname, prodtype, prodcost FROM PRODUCTS OUTERUNION SELECT prodnum, prodname, prodtype, prodcost FROM PRODUCTS; QUIT;

  46. /* SQLOBS Macro Variable */ PROCSQL; SELECT * FROM PRODUCTS WHERE PRODTYPE='Software'; %PUT SQLOBS = &SQLOBS; QUIT; /* SQLOBS Macro Variable #2 */ PROCSQL; INSERTINTO PRODUCTS (PRODNUM, PRODNAME, PRODTYPE, PRODCOST) VALUES(6002,'Security Software','Software',375.00) VALUES(1701,'Travel Laptop SE', 'Laptop', 4200.00); %PUT SQLOBS = &SQLOBS; QUIT;

  47. SQL • Create table • Query tables • Summarizing Data: aggregate functions • Insert records • Update records • Joint Tables • Select into : Macro variables

More Related