1 / 20

Correlated Subqueries in PROC SQL

Correlated Subqueries in PROC SQL. Barry Campbell Reproductive Care Program of Nova Scotia. Overview. Quick review of PROC SQL Subqueries and examples Correlated subqueries and examples. Quick review of PROC SQL. SQL: Structured Query Language Talk to relational databases

gambhiri
Download Presentation

Correlated Subqueries in PROC 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. Correlated Subqueriesin PROC SQL Barry Campbell Reproductive Care Program of Nova Scotia

  2. Overview • Quick review of PROC SQL • Subqueries and examples • Correlated subqueries and examples

  3. Quick review of PROC SQL • SQL: Structured Query Language • Talk to relational databases • PROC SQL is SAS’s implementation SQL • Integrates elements of SAS language: e.g., functions, formats, labels • Alternative to Data Step when you want to think relationally

  4. Terminology

  5. PROC SQL Syntax

  6. What is a subquery? • SELECT statement embedded in a SQL query instead of a column name, table name or expression • Use in SELECT, FROM, WHERE, or HAVING clauses • Scalar or vector depending on context • Can refer to same or different table • AKA “Nested” or “Inner” queries

  7. Subquery in SELECT clause SELECT student_id, (SELECT COUNT(*) FROM courses WHERE student_id = S.student_id) num_courses, (SELECT MAX(grade) FROM grades WHERE student_id = S.student_id) best_grade FROM students S Pull number of courses and best grade from other tables. Could also be done with a JOIN.

  8. Subquery in FROM clause Create inline table C to join with A and B using IDs A (admits) B (patients) C (DISTINCT doctors)

  9. Subquery in WHERE clause Systolic blood pressure

  10. What is a correlated subquery • Results of inner query constrained by outer • Uses a common variable or key to correlate inner and outer • Typically in the WHERE clause

  11. What’s it good for? • Selection depends on aggregate results from the same table • Selection depends on data about same entity in a different table • Combine multiple steps into one • Pass-through queries (execute on DBMS)

  12. What’s it good for? Examples • Improving grades: • Show students whose average grade improved at least 10% over last year’s • Flooding events: • List cities and dates on which rainfall was at least 10x the city’s daily average

  13. Correlated Subquery Example SELECT student_id, name FROM students S WHERE (SELECT MEAN(grade) FROM grades WHERE student_id = S.student_id AND year = 2011) > (SELECT MEAN(grade) FROM grades WHERE student_id = S.student_id AND year = 2010) * 1.1 Who improved their average by at least 10%?

  14. Correlated Subquery Example SELECT city_id, rainfall_mm, event_date FROM rainfall_data R WHERE rainfall_mm > (SELECT MEAN(rainfall_mm) * 10 FROM rainfall_data WHERE city_id = R.city_id) Where and when was the heavy rain? Correlation: inner to outer

  15. Correlated Subquery Example From patient visit registry, list all patients and the maximum amount charged for each one

  16. Correlated Subquery Example Which patients had vital signs taken more than once in the same visit?

  17. Correlated Subquery Example Show me profits on CPUs from top performing suppliers

  18. Correlated Subquery Example Build dataset from X but exclude observations found in Y

  19. Summary • Subqueries and correlated subqueries: compact way to write complex data manipulation. • Combine selection with aggregation • Think relationally rather than procedurally. • Good way to improve understanding of relationships among database tables

  20. Resources • PROC SQL for DATA Step Die-Hards, Christianna S. Williams http://www2.sas.com/proceedings/forum2008/185-2008.pdf Various data manipulation tasks using Data Step and SQL in an increasingly complex series of examples, including sub-queries and correlated sub-queries. • Advanced SQL Processing, Destiny Corporation http://www.nesug.org/proceedings/nesug02/hw/hw007.pdf Advanced topics in PROC SQL including HAVING, FULL JOINs and creation of Views, Indexes, and Data sets. Later examples of sub-queries and correlated sub-queries. • Working With Subquery in the SQL Procedure Lei Zhang, Domain Solutions Corphttp://www.nesug.org/proceedings/nesug98/dbas/p005.pdf - Advanced examples of subqueries and correlatedsubqueries in all clauses of the SQL statement. • An Animated Guide: Knowing SQL Internal Processes makes SQL Easy - Russ Laveryhttp://www.phuse.eu/download.aspx?type=cms&docID=597 A graphical representation of the SQL process and some rules for describing/predicting the SQL process. Detailed examples with good explanations of pros and cons of subqueries. • http://beginner-sql-tutorial.com/sql-subquery.htm

More Related