notes15 - 1 1 Notes 15: SQL 2 The SQL Procedure The SQL...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 1 Notes 15: SQL 2 The SQL Procedure The SQL procedure enables you to write ANSI standard SQL code within the SAS System and use it to process SAS tables. Join Merge Column Variable Row Observation Table Data set SQL SAS 3 PROC SQL versus the DATA Step: Benefits The SQL procedure enables you to retrieve information without creating SAS data sets or using PROC steps. join tables and produce a report in one step without creating a SAS data set join tables without sorted data use complex matching criteria. By default, PROC SQL returns a report, not a SAS data set. 4 PROC SQL versus DATA Step: Costs In general, the SQL procedure requires more CPU time and memory than a DATA step merge. You cannot create data sets from non- database external sources (e.g. ASCII files, spreadsheets). 5 The SQL Procedure Syntax Overview The PROC SQL statement signals the start of the SQL procedure. PROC SQL; PROC SQL; 6 The SQL Procedure Syntax Overview The QUIT statement ends the SQL step. QUIT; QUIT; 2 7 The SQL Procedure: Syntax Overview Statements within the SQL step (also called queries ) are made of smaller building blocks called clauses : SELECT FROM WHERE GROUP BY ORDER BY HAVING There is one semicolon at the end of each query ; not at the end of each clause. 8 The SELECT clause identifies columns to include in the query result or table. Columns listed in the SELECT clause are separated by commas. There is no comma following the last variable in the list. Adding the DISTINCT keyword eliminates duplicate rows. To select all columns read, use an asterisk in place of the column names. The SELECT Clause SELECT * SELECT * SELECT <DISTINCT> var-1 , var-2 ... SELECT <DISTINCT> var-1 , var-2 ... 9 The FROM Clause The FROM clause identifies the SAS table(s) from which to read. FROM SAS-data-set ... FROM SAS-data-set ... 10 A Simple Query Instead of using PROC PRINT to create a listing report, you can also use a SELECT statement. proc sql; select * from purchases; CUSTNUM ITEM UNITS UNITCOST CUSTNUM ITEM UNITS UNITCOST CUSTNUM ITEM UNITS UNITCOST CUSTNUM ITEM UNITS UNITCOST 1 Chair 1 $179.00 1 Chair 1 $179.00 1 Chair 1 $179.00 1 Chair 1 $179.00 1 Pens 12 $0.89 1 Pens 12 $0.89 1 Pens 12 $0.89 1 Pens 12 $0.89 1 Paper 4 $6.95 1 Paper 4 $6.95 1 Paper 4 $6.95 1 Paper 4 $6.95 1 Stapler 1 $8.95 1 Stapler 1 $8.95 1 Stapler 1 $8.95 1 Stapler 1 $8.95 7 Mouse Pad 1 $11.79 7 Mouse Pad 1 $11.79 7 Mouse Pad 1 $11.79 7 Mouse Pad 1 $11.79 7 Pens 24 $1.59 7 Pens 24 $1.59 7 Pens 24 $1.59 7 Pens 24 $1.59 13 Markers . $0.99 13 Markers . $0.99 13 Markers . $0.99 13 Markers . ....
View Full Document

Page1 / 13

notes15 - 1 1 Notes 15: SQL 2 The SQL Procedure The SQL...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online