{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

notes15 - The SQL Procedure The SQL procedure enables you...

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

View Full Document Right Arrow Icon
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;
Image of page 1

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

View Full Document Right Arrow Icon
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 UNITS ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 1 Chair 1 $179.00 Chair 1 Pens 12 $0.89 Pens 1 Paper 4 $6.95 Paper 1 Stapler 1 $8.95 Stapler 7 Mouse Pad 1 $11.79 Mouse Pad $11.79 7 Pens 24 $1.59 Pens 13 Markers . $0.99 Markers Result 11 A Simple Query You can also use PROC SQL to get the number of rows in a table, select count(*) from purchases; ƒƒƒƒƒƒƒƒƒ 7 Result Other examples of summary functions you can use are AVG or MEAN, SUM, MIN, MAX, N or FREQ. select count(units) from purchases; ƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒ 6 or the number of non-missing values in a column.
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern