PROC SQL by Example: Using SQL Within SAS

✍ Scribed by Howard Schreier

• Publisher: SAS Press
• Year: 2008
• Tongue: English
• Leaves: 294
• Category: Library

✦ Synopsis

Using a side-by-side approach, this concise reference guide includes many extensively explained examples showing equivalent DATA step and SQL code, enabling SAS users to take advantage of existing SAS skills and knowledge while learning about SQL.

✦ Table of Contents

Introduction
1.1 More about SQL
Standards and Extensions
Fundamental Differences between SQL and the DATA Step
RDBMS Heritage
1.2 More about This Book
Purpose
Perspective
Presentation
Basic Building Blocks
2.1 The Simplest SELECT Statement
2.2 A More Selective SELECT
2.3 Storing Results
2.4 Column Subsets
2.5 New Columns
2.6 Aggregation
Grand Totals and More
Subtotals and More
Details
2.7 Conditionality
2.8 Filtering
Filtering from the Source
Filtering Aggregated Data
2.9 Reordering Rows
2.10 Elimination of Duplicates
2.11 Summary
More Building Blocks
3.1 Combining Summary Statistics with Original Detail
3.2 Summary Statistics Based on Distinct Values
3.3 Preprocessing the Source with Inline Views
3.4 Summary
Joins
4.1 Avoiding Ambiguity in Column References
4.2 The Simplest Merges and Joins
4.3 Matching with Nonrepeating Keys
Symmetrically Inclusive Joins
Asymmetric Joins
Match MERGE versus INNER JOIN
Summary
4.4 Matching with Repeating Keys
INNER JOIN
OUTER JOIN
4.5 More about Joins and Merges
Three or More Sources
Composite Keys
4.6 More about Joins
Natural Joins
Implicit Joins
Generality of Join Conditions
4.7 Summary
Subqueries
5.1 Contexts That Expect Subqueries
The EXISTS Condition
The IN Condition
The ANY Keyword
The ALL Keyword
Subqueries Outside the WHERE Clause
5.2 General Usage of Subqueries
5.3 Summary
Set Operators
6.1 The Contrast between Joins and Set Operators
6.2 Set Operators: Preview
6.3 Concatenation and Interleaving with OUTER UNION
6.4 Data Type Compatibility
6.5 Overview: UNION, INTERSECT, and EXCEPT
Column Alignment
Leftover Columns
Row Accretion
Duplicate Rows
Conclusion
6.6 UNION
6.7 INTERSECT
6.8 EXCEPT
6.9 Summary
Global Statements, Options, and Session Management
7.1 Global Statements
7.2 PROC SQL Options
7.3 Summary
Using the Macro Facility with PROC SQL
8.1 Generating PROC SQL Code
8.2 Populating Macro Variables
User-Defined Macro Variables
DICTIONARY Tables
Automatic Macro Variables
8.3 Summary
Table Maintenance and Alternate Strategies
9.1 Environment for Examples
9.2 Distinguishing Persistence from Replacement
9.3 Life Cycle of a Table
Creation Using a Query
Creation Using a Model
Creation Using Specifications
Help from the DESCRIBE TABLE Statement
Deleting a Table
9.4 Data Maintenance
Inserting Rows into an Existing Table
Deleting Rows
Selective Corrections
9.5 Metadata Maintenance
9.6 Changing Structure
9.7 Changing Features
Indexes
Integrity Constraints
Audit Trails
Generation Data Sets
9.8 Summary
Views
10.1 Defining Views
10.2 Using Views
10.3 Inspecting Views
10.4 Changing a Table via a View
10.5 Managing Library References
10.6 Summary
PROC SQL as a Report Generator
11.1 Simple Reports
11.2 Complex Reports
11.3 Reports with Long Character Strings
11.4 PROC SQL and the Output Delivery System
11.5 Summary
Mixed Solutions
12.1 Example: Schedule Matrix
12.2 Example: Identifying Spikes in a Series
12.3 Example: Using PROC TRANSPOSE to Normalize
12.4 Summary
Performance Tuning
13.1 Resource Example: The Effect of an Index
13.2 Code Example: The Advantage of Equijoins
13.3 Summary
Documentation Roadmap
14.1 Where to Start?
SAS 9.2 SQL Query Window User's Guide
SAS 9.2 SQL Procedure User's Guide
Base SAS 9.2 Procedures Guide (SQL Chapter)
None of the Above
14.2 Following Cross References
SELECT Statement
sql-expression
query-expression
table-expression
14.3 The Three Expressions Revisited
SELECT Statement
query-expression
table-expression
sql-expression
14.4 Could It Be More Logical?
14.5 Summary
SASHELP.CLASS Data Set
Online Resources
65193_freechapter.pdf
Cody_CodysCollection_frontcover
TOC
chap1_new
Introduction
Task: Converting character values to numeric values
Keywords
Program 1.1: Converting character values to numeric values
Task: Converting character values to numeric values using a macro
Keywords
Program 1.2: Presenting a macro to perform character-to-numeric conversion
Program 1.3: Testing the character-to-numeric conversion macro
Task: Converting a specific value such as 999 to a missing value for all numeric variables in a SAS data set
Keywords
Program 1.4: Converting a specific value such as 999 to a missing value for all numeric variables in a SAS data set
Task: Converting a specific value such as 'NA' to a missing value for all character variables in a SAS data set
Keywords
Program 1.5: Converting a specific value such as ‘NA’ to a missing value for all character variables in a SAS data set
Task: Changing all character values to either uppercase, lowercase, or proper case
Keywords
Program 1.6: Changing case for all character variables in a SAS data set
Task: Reading a numeric value that contains units such as Lbs. or Kgs. in the value
Keywords
Program 1.7: Reading data values that contain units
Task: Solving part of the previous task using a Perl regular expression
Keywords
Program 1.8: Using a Perl regular expression to extract the digit and units part of a character value
Conclusion
AboutThisAuthor_new
CodyCodysCollection_FreeChapAdFINAL
chap7.new.pdf
7.1 Global Statements
7.2 PROC SQL Options
7.3 Summary