book

PROC SQL: Beyond the Basics Using SAS, Second Edition

Name: PROC SQL: Beyond the Basics Using SAS, Second Edition
Author: Kirk Paul Lafler
ISBN: 9781612907345

by Kirk Paul Lafler

October 2013

Intermediate to advanced

400 pages

8h 1m

English

SAS Institute

Read now

Unlock full access

About This Book
About The Author
Acknowledgments
Chapter 1: Designing Database Tables
IntroductionDatabase DesignConceptual ViewTable DefinitionsRedundant InformationNormalizationNormalization StrategiesColumn Names and Reserved WordsANSI SQL Reserved WordsSQL CodeData IntegrityReferential IntegrityDatabase Tables Used in This BookCUSTOMERS TableINVENTORY TableINVOICE TableMANUFACTURERS TablePRODUCTS TablePURCHASES TableTable ContentsThe Database StructureSample Database TablesSummary
Chapter 2: Working with Data in PROC SQL
IntroductionOverview of Data TypesNumeric DataDate and Time Column DefinitionsCharacter DataMissing Values and NULLArithmetic and Missing DataSQL KeywordsSQL Operators and FunctionsComparison OperatorsLogical OperatorsArithmetic OperatorsCharacter String Operators and FunctionsSummarizing DataPredicatesDictionary TablesDictionary Tables and MetadataDisplaying Dictionary Table DefinitionsDictionary Table Column NamesAccessing a Dictionary Table’s ContentsSummary
Chapter 3: Formatting Output
IntroductionFormatting OutputWriting a Blank Line between Each RowDisplaying Row NumbersUsing the FORMAT= Column Modifier to Format OutputConcatenating Character StringsInserting Text and Constants between ColumnsUsing Scalar Expressions with Selected ColumnsOrdering Output by ColumnsGrouping Data with Summary FunctionsGrouping Data and SortingSubsetting Groups with the HAVING ClauseFormatting Output with the Output Delivery SystemODS and Output FormatsSending Output to a SAS Data SetConverting Output to Rich Text FormatExporting Data and Output to ExcelDelivering Results to the WebSummary
Chapter 4: Coding PROC SQL Logic
IntroductionConditional LogicSQL CodeSQL CodeSQL CodeSQL CodeCASE ExpressionsSimple Case ExpressionSearched CASE ExpressionCase Logic versus COALESCE ExpressionAssigning Labels and Grouping DataLogic and NullsInterfacing PROC SQL with the Macro LanguageExploring Macro Variables and ValuesCreating Multiple Macro VariablesUsing Automatic Macro Variables to Control ProcessingBuilding Macro Tools and ApplicationsCreating Simple Macro ToolsCross-Referencing ColumnsDetermining the Number of Rows in a TableIdentifying Duplicate Rows in a TableSummary
Chapter 5: Creating, Populating, and Deleting Tables
IntroductionCreating TablesCreating a Table Using Column-Definition ListsCreating a Table Using the LIKE ClauseDeriving a Table and Data from an Existing TablePopulating TablesAdding Data to a Table with a SET ClauseAdding Data to All of the Columns in a RowAdding Data to Some of the Columns in a RowAdding Data with a SELECT QueryBulk Loading Data from Microsoft ExcelIntegrity ConstraintsDefining Integrity ConstraintsTypes of Integrity ConstraintsPreventing Null Values with a NOT NULL ConstraintEnforcing Unique Values with a UNIQUE ConstraintValidating Column Values with a CHECK ConstraintReferential Integrity ConstraintsEstablishing a Primary KeyEstablishing a Foreign KeyDisplaying Integrity ConstraintsDeleting Rows in a TableDeleting a Single Row in a TableDeleting More Than One Row in a TableDeleting All Rows in a TableDeleting TablesDeleting a Single TableDeleting Multiple TablesDeleting Tables That Contain Integrity ConstraintsSummary
Chapter 6: Modifying and Updating Tables and Indexes
IntroductionModifying TablesAdding New ColumnsControlling the Position of Columns in a TableChanging a Column’s LengthChanging a Column’s FormatChanging a Column’s LabelRenaming a ColumnRenaming a TableIndexesDesigning IndexesCardinalityIndex SelectivityDefining IndexesCreating a Simple IndexCreating a Composite IndexPreventing Duplicate Values in an IndexModifying Columns Containing IndexesDeleting (Dropping) IndexesUpdating Data in a TableSummary
Chapter 7: Coding Complex Queries
IntroductionIntroducing Complex QueriesJoinsWhy Joins Are ImportantInformation Retrieval Based on RelationshipsDATA Step Merges versus PROC SQL JoinsTypes of Complex QueriesDemystifying Join AlgorithmsInfluencing Joins with a Little MagicCartesian Product JoinsInner JoinsEquijoinsNon-EquijoinsReflexive or Self JoinsUsing Table Aliases in JoinsPerforming Computations in JoinsJoins with Three TablesJoins with More Than Three TablesOuter JoinsLeft Outer JoinsRight Outer JoinsFull Outer JoinsSubqueriesAlternate Approaches to SubqueriesPassing a Single Value with a SubqueryPassing More Than One Row with a SubqueryComparing a Set of ValuesCorrelated SubqueriesSet OperationsRules for Set OperatorsSet Operators and PrecedenceAccessing Rows from the Intersection of Two QueriesAccessing Rows from the Combination of Two QueriesConcatenating Rows from Two QueriesComparing Rows from Two QueriesComplex Query ApplicationsOne-to-One, One-to-Many, Many-to-One, and Many-to-Many RelationshipsProcessing First, Last, and Between Rows for BY-and GroupsDetermining the Number of Rows in an Input TableIdentifying Tables with the Most IndexesSummary

Chapter 8: Working with Views
IntroductionViews-Windows to Your DataWhat Views Aren’tTypes of ViewsCreating ViewsDisplaying a View’s ContentsDescribing View DefinitionsCreating and Using Views in SASViews and SAS ProceduresViews and DATA StepsEliminating RedundancyRestricting Data Access-SecurityHiding Logic ComplexitiesNesting ViewsUpdatable ViewsInserting New Rows of DataUpdating Existing Rows of DataDeleting Rows of DataDeleting ViewsSummary
Chapter 9: Troubleshooting and Debugging
IntroductionThe World of BugsThe Debugging ProcessTypes of ProblemsTroubleshooting and Debugging TechniquesValidating Queries with the VALIDATE StatementDocumented PROC SQL Options and StatementUndocumented PROC SQL OptionsMacro VariablesTroubleshooting and Debugging ExamplesSummary
Chapter 10: Tuning for Performance and Efficiency
IntroductionUnderstanding Performance TuningSorting and PerformanceUser-Specified Sorting (SORTPGM= System Options)Automatic SortingGrouping and PerformanceSplitting TablesIndexes and PerformanceReviewing CONTENTS Output and System MessagesOptimizing WHERE Clause Processing with IndexesConstructing Efficient Logic ConditionsAvoiding UNIONsSummary
Index

Content preview from PROC SQL: Beyond the Basics Using SAS, Second Edition

Chapter 9: Troubleshooting and Debugging

Introduction

The World of Bugs

The Debugging Process

Types of Problems

Troubleshooting and Debugging Techniques

Validating Queries with the VALIDATE Statement

Documented PROC SQL Options and Statement

Undocumented PROC SQL Options

Macro Variables

Troubleshooting and Debugging Examples

Summary

Introduction

When it comes to tracking down the source of SQL coding problems, users can be very resourceful. To find all those pesky errors, warnings, notes, and unexpected results, you need to carefully inspect the SAS log and telltale clues from code reviews and sample runs. Using reliable troubleshooting and debugging techniques will help get to the root of any syntax, data, system related, and logic errors ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781612900278

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

PROC SQL: Beyond the Basics Using SAS, Second Edition

by Kirk Paul Lafler

Introduction

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.