SAS 9.1 SQL Procedure User's Guide

Front Cover\r......Page 0
Title Page\r......Page 2
Copyright\r......Page 3
Contents......Page 4
What Is the SQL Procedure?......Page 6
Views......Page 7
Comparing PROC SQL with the SAS DATA Step......Page 8
Notes about the Example Tables......Page 9
Retrieving Data from a Single Table......Page 16
Overview of the SELECT Statement......Page 17
Selecting All Columns in a Table......Page 19
Selecting Specific Columns in a Table......Page 20
Eliminating Duplicate Rows from the Query Results......Page 21
Determining the Structure of a Table......Page 22
Adding Text to Output......Page 23
Calculating Values......Page 24
Assigning a Column Alias......Page 25
Assigning Values Conditionally......Page 26
Specifying Column Attributes......Page 29
Sorting by Column......Page 30
Sorting by Multiple Columns......Page 31
Sorting by Calculated Column......Page 32
Sorting by Column Position......Page 33
Specifying a Different Sorting Sequence......Page 34
Using a Simple WHERE Clause......Page 35
Retrieving Rows Based on a Comparison......Page 36
Retrieving Rows That Satisfy Multiple Conditions......Page 37
Using Other Conditional Operators......Page 38
Using a WHERE Clause with Missing Values......Page 42
Using Aggregate Functions......Page 44
Displaying Sums......Page 45
Remerging Summary Statistics......Page 46
Using Aggregate Functions with Unique Values......Page 48
Summarizing Data with Missing Values......Page 49
Grouping Data......Page 50
Grouping without Summarizing......Page 51
Grouping by Multiple Columns......Page 52
Grouping with Missing Values......Page 53
Using a Simple HAVING Clause......Page 55
Using HAVING with Aggregate Functions......Page 56
Validating a Query......Page 57
Retrieving Data from Multiple Tables......Page 60
Selecting Data from More Than One Table by Using Joins......Page 61
Inner Joins......Page 62
Outer Joins......Page 70
Specialty Joins......Page 73
Using the Coalesce Function in Joins......Page 75
Comparing DATA Step Match-Merges with PROC SQL Joins......Page 76
Using Subqueries to Select Data......Page 79
Multiple-Value Subqueries......Page 80
Correlated Subqueries......Page 81
Testing for the Existence of a Group of Values......Page 82
Multiple Levels of Subquery Nesting......Page 83
Combining a Join with a Subquery......Page 84
When to Use Joins and Subqueries......Page 85
Working with Two or More Query Results......Page 86
Producing Unique Rows from Both Queries (UNION)......Page 87
Producing Rows That Are in Only the First Query Result (EXCEPT)......Page 88
Producing Rows That Belong to Both Query Results (INTERSECT)......Page 89
Concatenating Query Results (OUTER UNION)......Page 90
Producing Rows from the First Query or the Second Query......Page 91
Creating and Updating Tables and Views......Page 94
Creating Tables from Column Definitions......Page 95
Creating Tables from a Query Result......Page 96
Creating Tables Like an Existing Table......Page 97
Inserting Rows with the SET Clause......Page 98
Inserting Rows with the VALUES Clause......Page 99
Inserting Rows with a Query......Page 100
Updating All Rows in a Column with the Same Expression......Page 101
Updating Rows in a Column with Different Expressions......Page 102
Deleting Rows......Page 103
Adding a Column......Page 104
Modifying a Column......Page 105
Deleting a Column......Page 106
Tips for Creating Indexes......Page 107
Creating and Using Integrity Constraints in a Table......Page 108
Creating and Using PROC SQL Views......Page 110
Describing a View......Page 111
Embedding a Libname in a View......Page 112
Specifying In-Line Views......Page 113
Using SQL Procedure Views in SAS Software......Page 114
Introduction......Page 116
Limiting Iterations with the LOOPS= Option......Page 117
Expanding SELECT * with the FEEDBACK Option......Page 118
Timing PROC SQL with the STIMER Option......Page 119
Using Indexes to Improve Performance......Page 120
Comparing Subqueries with Joins......Page 121
Accessing SAS System Information Using DICTIONARY Tables......Page 122
Using DICTIONARY.COLUMNS......Page 124
Using PROC SQL with the SAS Macro Facility......Page 125
Creating Macro Variables in PROC SQL......Page 126
Concatenating Values in Macro Variables......Page 128
Defining Macros to Create Tables......Page 129
Using the PROC SQL Automatic Macro Variables......Page 131
Formatting PROC SQL Output Using the REPORT Procedure......Page 132
Accessing a DBMS with SAS/ACCESS Software......Page 133
Using Libname Engines......Page 134
Displaying DBMS Data with the PROC SQL Pass-Through Facility......Page 136
Using the Output Delivery System (ODS) with PROC SQL......Page 137
Practical Problem-Solving with PROC SQL......Page 138
Background Information......Page 139
Solution......Page 140
Background Information......Page 141
Solution......Page 142
Background Information......Page 143
How It Works......Page 144
Solution......Page 145
Background Information......Page 146
How It Works......Page 147
Solution......Page 148
Problem......Page 149
How It Works......Page 150
Solution......Page 151
How It Works......Page 152
Background Information......Page 153
How It Works......Page 154
Background Information......Page 155
Solution......Page 156
How It Works......Page 157
Solution......Page 158
Background Information......Page 159
Solution......Page 160
Solution......Page 162
How It Works......Page 164
Recommended Reading......Page 166
Glossary......Page 168
Index......Page 172