PROC SQL

Beyond the Basics Using SAS, Third Edition

Kirk Paul Lafler

• Publisher: SAS Institute
• Year: 2019
• ISBN:
  9781635266818
• Edition: 3
• Topic: Informatique
• Subtopic: Sciences générales de l'informatique

PROC SQL: Beyond the Basics Using SAS ®, Third Edition, is a step-by-step, example-driven guide that helps readers master the language of PROC SQL. Packed with analysis and examples illustrating an assortment of PROC SQL options, statements, and clauses, this book not only covers all the basics, but it also offers extensive guidance on complex topics such as set operators and correlated subqueries. Programmers at all levels will appreciate Kirk Lafler's easy-to-follow examples, clear explanations, and handy tips to extend their knowledge of PROC SQL.

This third edition explores new and powerful features in SAS® 9.4, including topics such as:

• IFC and IFN functions
• nearest neighbor processing
• the HAVING clause
• indexes

It also features two completely new chapters on fuzzy matching and data-driven programming. Delving into the workings of PROC SQL with greater analysis and discussion, PROC SQL: Beyond the Basics Using SAS ®, Third Edition, explores this powerful database language using discussion and numerous real-world examples.

Chapter 1: Designing Database Tables

Introduction. 1

Database Design. 1

Conceptual View.. 2

Table Definitions. 2

Redundant Information. 3

Normalization. 3

Normalization Strategies. 4

Column Names and Reserved Words. 7

ANSI SQL Reserved Words. 7

SQL Code. 7

Data Integrity. 8

Referential Integrity. 8

Database Tables Used in This Book. 8

CUSTOMERS Table. 8

INVENTORY Table. 9

INVOICE Table. 9

MANUFACTURERS Table. 9

PRODUCTS Table. 10

PURCHASES Table. 10

Table Contents. 11

The Database Structure. 13

Sample Database Tables. 14

Summary. 20

Introduction

The area of database design is very important in relational processes. Much has been written on this subject, including entire textbooks and thousands of technical papers. No pretenses are made about the thoroughness of this very important subject in these pages. Rather, an attempt is made to provide a quick-start introduction for those readers who are unfamiliar with the issues and techniques of basic design principles. Readers needing more information are referred to the references listed in the back of this book. As you read this chapter, the following points should be kept in mind.

Database Design

Activities related to good database design require the identification of end-user requirements and involve defining the structure of data values on a physical level. Database design begins with a conceptual view of what is needed. The next step, called logical design, consists of developing a formal description of database entities and relationships to satisfy user requirements. Seldom does a database consist of a single table. Consequently, tables of interrelated information are created to enable more complex and powerful operations on data. The final step, referred to as physical design, represents the process of achieving optimal performance and storage requirements of the logical database.

Conceptual View

The health and well-being of a database depends on its database design. A database must be in balance with all of its components (or optimized) to avoid performance and operation bottlenecks. Database design doesn’t just happen and is not a process that occurs by chance. It involves planning, modeling, creating, monitoring, and adjusting to satisfy the endless assortment of user requirements without impeding resource requirements. Of central importance to database design is the process of planning. Planning is a valuable component that, when absent, causes a database to fall prey to a host of problems including poor performance and difficulty in operation. Database design consists of three distinct phases, as illustrated in Figure 1.1.

Figure 1.1: Three Distinct Phases of Database Design

Table Definitions

PROC SQL uses a model of data that is conceptually stored as multisets rather than as physical files. A physical file consists of one or more records ordered sequentially or some other way. Programming languages such as COBOL and FORTRAN evolved to process files of this type by performing operations one record at a time. These languages were generally designed and used to mimic the way people process paper forms.

PROC SQL was designed to work with multisets of data. Multisets have no order, and members of a multiset are of the same type using a data structure known as a table. For classification purposes, a table is a base table consisting of zero or more rows and one or more columns, or a table is a virtual table (called a view), which can be used the same way that a table can be used (see Chapter 8, “Working with Views”).

Redundant Information

One of the rules of good database design requires that data not be redundant or duplicated in the same database. The rationale for this conclusion originates from the belief that if data appears more than once in a database, then there is reason to believe that one of the pieces of data is likely to be in error. Furthermore, redundancy often leads to the following:

● Inconsistencies, because errors are more likely to result when facts are repeated.

● Update anomalies where the insertion, modification, or deletion of data may result in inconsistencies.

Another thing to watch for is the appearance of too many columns containing NULL values. When this occurs, the database is pro...

Table of contents

• Cover
• Copyright Page
• Contents
• About This Book
• Chapter 1: Designing Database Tables
• Chapter 2: Working with Data in PROC SQL
• Chapter 3: Formatting Output
• Chapter 4: Coding PROC SQL Logic
• Chapter 5: Creating, Populating, and Deleting Tables
• Chapter 6: Modifying and Updating Tables and Indexes
• Chapter 7: Coding Complex Queries
• Chapter 8: Working with Views
• Chapter 9:  Fuzzy Matching Programming
• Chapter 10: Data-driven Programming
• Chapter 11: Troubleshooting and Debugging
• Chapter 12: Tuning for Performance and Efficiency
• References