PROC SQL: Beyond the Basics Using SAS®, Second Edition

✍ Scribed by Kirk Paul Lafler

• Publisher: SAS Institute
• Year: 2016
• Tongue: English
• Leaves: 468
• Category: Library

✦ Table of Contents

About This Book......Page 16
About The Author......Page 21
Acknowledgments......Page 22
Chapter 1: Designing Database Tables......Page 25
Conceptual View......Page 26
Redundant Information......Page 27
Normalization......Page 28
Normalization Strategies......Page 29
Column Names and Reserved Words......Page 33
Data Integrity......Page 34
Database Tables Used in This Book......Page 35
MANUFACTURERS Table......Page 36
Table Contents......Page 37
The Database Structure......Page 39
Sample Database Tables......Page 40
Summary......Page 46
Chapter 2: Working with Data in PROC SQL......Page 48
Numeric Data......Page 49
Date and Time Column Definitions......Page 53
Missing Values and NULL......Page 54
Arithmetic and Missing Data......Page 55
SQL Keywords......Page 58
SQL Operators and Functions......Page 60
Comparison Operators......Page 61
Logical Operators......Page 62
Arithmetic Operators......Page 64
Character String Operators and Functions......Page 66
Summarizing Data......Page 86
Predicates......Page 90
Dictionary Tables......Page 100
Dictionary Tables and Metadata......Page 101
Displaying Dictionary Table Definitions......Page 103
Dictionary Table Column Names......Page 104
Accessing a Dictionary Table’s Contents......Page 107
Summary......Page 117
Chapter 3: Formatting Output......Page 119
Writing a Blank Line between Each Row......Page 120
Displaying Row Numbers......Page 121
Using the FORMAT= Column Modifier to Format Output......Page 124
Concatenating Character Strings......Page 125
Inserting Text and Constants between Columns......Page 127
Using Scalar Expressions with Selected Columns......Page 129
Ordering Output by Columns......Page 132
Grouping Data with Summary Functions......Page 135
Grouping Data and Sorting......Page 137
Subsetting Groups with the HAVING Clause......Page 138
ODS and Output Formats......Page 141
Sending Output to a SAS Data Set......Page 142
Converting Output to Rich Text Format......Page 143
Exporting Data and Output to Excel......Page 145
Delivering Results to the Web......Page 147
Summary......Page 148
Chapter 4: Coding PROC SQL Logic......Page 150
SQL Code......Page 151
SQL Code......Page 152
CASE Expressions......Page 153
Simple Case Expression......Page 154
Searched CASE Expression......Page 168
Case Logic versus COALESCE Expression......Page 174
Assigning Labels and Grouping Data......Page 176
Logic and Nulls......Page 178
Interfacing PROC SQL with the Macro Language......Page 181
Exploring Macro Variables and Values......Page 182
Creating Multiple Macro Variables......Page 187
Using Automatic Macro Variables to Control Processing......Page 191
Building Macro Tools and Applications......Page 193
Cross-Referencing Columns......Page 194
Determining the Number of Rows in a Table......Page 195
Identifying Duplicate Rows in a Table......Page 196
Summary......Page 197
Chapter 5: Creating, Populating, and Deleting Tables......Page 198
Creating Tables......Page 200
Creating a Table Using Column-Definition Lists......Page 201
Creating a Table Using the LIKE Clause......Page 205
Deriving a Table and Data from an Existing Table......Page 207
Populating Tables......Page 209
Adding Data to a Table with a SET Clause......Page 210
Adding Data to All of the Columns in a Row......Page 213
Adding Data to Some of the Columns in a Row......Page 218
Adding Data with a SELECT Query......Page 222
Bulk Loading Data from Microsoft Excel......Page 223
Types of Integrity Constraints......Page 229
Preventing Null Values with a NOT NULL Constraint......Page 230
Enforcing Unique Values with a UNIQUE Constraint......Page 234
Validating Column Values with a CHECK Constraint......Page 235
Referential Integrity Constraints......Page 237
Establishing a Primary Key......Page 238
Establishing a Foreign Key......Page 239
Displaying Integrity Constraints......Page 244
Deleting a Single Row in a Table......Page 245
Deleting All Rows in a Table......Page 246
Deleting a Single Table......Page 247
Deleting Multiple Tables......Page 248
Deleting Tables That Contain Integrity Constraints......Page 249
Summary......Page 250
Chapter 6: Modifying and Updating Tables and Indexes......Page 252
Adding New Columns......Page 253
Controlling the Position of Columns in a Table......Page 255
Changing a Column’s Length......Page 258
Changing a Column’s Format......Page 262
Renaming a Column......Page 263
Renaming a Table......Page 265
Indexes......Page 266
Designing Indexes......Page 268
Index Selectivity......Page 270
Defining Indexes......Page 272
Creating a Simple Index......Page 273
Creating a Composite Index......Page 274
Modifying Columns Containing Indexes......Page 275
Deleting (Dropping) Indexes......Page 276
Updating Data in a Table......Page 277
Summary......Page 278
Chapter 7: Coding Complex Queries......Page 280
Joins......Page 282
Information Retrieval Based on Relationships......Page 283
DATA Step Merges versus PROC SQL Joins......Page 284
Types of Complex Queries......Page 285
Demystifying Join Algorithms......Page 287
Influencing Joins with a Little Magic......Page 288
Equijoins......Page 292
Non-Equijoins......Page 295
Reflexive or Self Joins......Page 296
Using Table Aliases in Joins......Page 299
Performing Computations in Joins......Page 300
Joins with Three Tables......Page 301
Joins with More Than Three Tables......Page 303
Outer Joins......Page 305
Left Outer Joins......Page 306
Right Outer Joins......Page 310
Full Outer Joins......Page 311
Subqueries......Page 312
Alternate Approaches to Subqueries......Page 313
Passing a Single Value with a Subquery......Page 314
Passing More Than One Row with a Subquery......Page 319
Comparing a Set of Values......Page 320
Correlated Subqueries......Page 322
Rules for Set Operators......Page 325
Set Operators and Precedence......Page 326
Accessing Rows from the Intersection of Two Queries......Page 327
Accessing Rows from the Combination of Two Queries......Page 329
Concatenating Rows from Two Queries......Page 331
Comparing Rows from Two Queries......Page 333
One-to-One, One-to-Many, Many-to-One, and Many-to-Many Relationships......Page 335
Processing First, Last, and Between Rows for BY-and Groups......Page 341
Determining the Number of Rows in an Input Table......Page 347
Identifying Tables with the Most Indexes......Page 348
Summary......Page 350
Chapter 8: Working with Views......Page 351
Views-Windows to Your Data......Page 352
What Views Aren’t......Page 353
Types of Views......Page 354
Creating Views......Page 355
Displaying a View’s Contents......Page 357
Describing View Definitions......Page 358
Creating and Using Views in SAS......Page 359
Views and SAS Procedures......Page 360
Views and DATA Steps......Page 363
Eliminating Redundancy......Page 364
Restricting Data Access-Security......Page 365
Hiding Logic Complexities......Page 367
Nesting Views......Page 368
Updatable Views......Page 371
Inserting New Rows of Data......Page 372
Updating Existing Rows of Data......Page 377
Deleting Rows of Data......Page 380
Deleting Views......Page 381
Summary......Page 382
Chapter 9: Troubleshooting and Debugging......Page 383
The Debugging Process......Page 384
Types of Problems......Page 386
Validating Queries with the VALIDATE Statement......Page 388
Documented PROC SQL Options and Statement......Page 389
Undocumented PROC SQL Options......Page 406
Macro Variables......Page 407
Troubleshooting and Debugging Examples......Page 410
Summary......Page 416
Chapter 10: Tuning for Performance and Efficiency......Page 417
Understanding Performance Tuning......Page 418
User-Specified Sorting (SORTPGM= System Options)......Page 419
Grouping and Performance......Page 420
Indexes and Performance......Page 421
Reviewing CONTENTS Output and System Messages......Page 423
Optimizing WHERE Clause Processing with Indexes......Page 426
Constructing Efficient Logic Conditions......Page 427
Avoiding UNIONs......Page 430
Summary......Page 434
Index......Page 436