How This Book Is Organized
This book is organized so you can read it cover to cover in a linear fashion. Or, if a current project requires learning a particular subject, then you can use the index to find the help you need or simply skip around and read the chapters or sections of interest to you.
Starting with Chapter 1, “Designing Database Tables,” readers are presented with database design strategies, database normalization principles, data redundancy techniques, and techniques for achieving optimal designs using first (1NF), second (2NF), and third (3NF) normal forms.
Chapter 2, “Working with Data in PROC SQL,” presents a variety of operators, functions, and predicates that can be used while working with numeric and character data. Dictionary tables and the wonderful information they provide are also presented.
Chapter 3, “Formatting Output,” presents techniques to improve output produced by queries. ANSI and non-ANSI techniques are presented to show how to insert text and constants between selected columns, how to display column headers for derived fields using mathematical expressions, how to display values in a certain order with the ORDER BY, GROUP BY, and HAVING clauses, and how the Output Delivery System (ODS) can be used to extend SQL’s capabilities to work with output.
Chapter 4, “Coding PROC SQL Logic,” examines how PROC SQL handles conditional logic operations including CASE and WHEN/THEN-ELSE. The availability of these programming constructs provides important features that typically are found only in host languages or external environments.
Chapter 5, “Creating, Populating, and Deleting Tables,” explores topics such as creating tables using column definition lists and from other tables, implementing column and table integrity constraints as well as referential integrity, populating table rows, and deleting one or more tables.
Chapter 6, “Modifying and Updating Tables and Indexes,” shows how to work with sets of data, modify database structures, rename tables, and define, use, and delete indexes.
Chapter 7, “Coding Complex Queries,” presents the differences between merging and joining tables, techniques on combining two or more tables to form cross-referenced data, and using queries within queries. Joins, subqueries, and unions enable programmers to link one piece of information with another piece of information. Examples illustrate the different types of joins and set operators in PROC SQL.
Chapter 8, “Working with Views,” examines the power of views and how “virtual” tables can eliminate or reduce data redundancy, shield users from making logic and/or data errors, hide sensitive information from users for security reasons, update data, and provide greater change control.
Chapter 9, “Troubleshooting and Debugging,” presents a variety of debugging techniques including the use of various PROC SQL options, table options, and macro variables that can be used to troubleshoot SQL queries.
Chapter 10, “Tuning for Performance and Efficiency,” presents techniques for improving a query’s performance. A variety of techniques are presented, including the optimization of query expressions, avoiding unnecessary sorting, using care when applying indexes, creating effective joins, constructing efficient views, and eliminating second passes over data.