SAS_ADV_01SQL高级SQL.doc

Topic PROC SQL Introduction Generate reports using PROC SQL and the appropriate options Construct subqueries within a PROC SQL step Access Dictionary Tables using the SQL procedure Combining Tables vertically using Set Operations in PROC SQL Joining Tables horizontally Creating and Managing Tables using PROC SQL Creating Index using PROC SQL Creating Macro Integrity Constraints 1. Introduction SQL, an acronym for Structured Query Language, is an ANSI (American National Standards Institute) standard database programming/query language. SQL is a standardized, widely used language that retrieves and updates data in tables and views based on those tables. PROC SQL can often be used as an alternative to other SAS procedures or the DATA step. You can use PROC SQL to retrieve data from and manipulate SAS tables add or modify data values in a table add, modify, or drop columns in a table create tables and views join multiple tables (whether or not they contain columns with the same name) generate reports. 1.1 What is SQL table? A PROC SQL table is synonymous with a SAS data file and has a member type of DATA. You can use PROC SQL tables as input into DATA steps and procedures. A row in a table is the same as an observation in a SAS data file. A column is the same as a variable. 1.2 Table-Comparison of SQL terms, Base SAS, and Data processing terminology: SQL Base SAS Data Processing _______________________________________________________________________ table SAS data file file row observation record column variable field _______________________________________________________________________ 1.3 Why use SQL or learn it? Powerful - can do many things Flexible - just say what you want Compatible - you can use the SQL you learn later in other applications Required - for SAS advanced programmer 1.4 SAS Data Step and SQL SAS SQL Create dataset Create tables Update values Update values Delete records Delete records Append new