book

SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition

Name: SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition
Author: SAS Institute
ISBN: 9781607649250

by SAS Institute

July 2011

Intermediate to advanced

1040 pages

23h 21m

English

SAS Institute

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
About This Book
Part 1: SQL Processing with SAS
Chapter 1: Performing Queries Using PROC SQL
OverviewPROC SQL BasicsWriting a PROC SQL StepSelecting ColumnsSpecifying the TableSpecifying Subsetting CriteriaOrdering RowsQuerying Multiple TablesSummarizing Groups of DataCreating Output TablesAdditional FeaturesSummaryQuiz
Chapter 2: Performing Advanced Queries Using PROC SQL
OverviewViewing SELECT Statement SyntaxDisplaying All ColumnsLimiting the Number of Rows DisplayedEliminating Duplicate Rows from OutputSubsetting Rows by Using Conditional OperatorsSubsetting Rows by Using Calculated ValuesEnhancing Query OutputSummarizing and Grouping DataSubsetting Data by Using SubqueriesSubsetting Data by Using Noncorrelated SubqueriesSubsetting Data by Using Correlated SubqueriesValidating Query SyntaxAdditional FeaturesSummaryQuiz
Chapter 3: Combining Tables Horizontally Using PROC SQL
OverviewUnderstanding JoinsGenerating a Cartesian ProductUsing Inner JoinsUsing Outer JoinsCreating an Inner Join with Outer Join-Style SyntaxComparing SQL Joins and DATA Step Match-MergesUsing In-Line ViewsJoining Multiple Tables and ViewsSummaryQuiz
Chapter 4: Combining Tables Vertically Using PROC SQL
OverviewUnderstanding Set OperationsUsing the EXCEPT Set OperatorUsing the INTERSECT Set OperatorUsing the UNION Set OperatorUsing the OUTER UNION Set OperatorComparing Outer Unions and Other SAS TechniquesSummaryQuiz
Chapter 5: Creating and Managing Tables Using PROC SQL
OverviewUnderstanding Methods of Creating TablesCreating an Empty Table by Defining ColumnsDisplaying the Structure of a TableCreating an Empty Table That Is like Another TableCreating a Table from a Query ResultInserting Rows of Data into a TableCreating a Table That Has Integrity ConstraintsHandling Errors in Row InsertionsDisplaying Integrity Constraints for a TableUpdating Values in Existing Table RowsDeleting Rows in a TableAltering Columns in a TableDropping TablesSummaryQuiz

Chapter 6: Creating and Managing Indexes Using PROC SQL
OverviewUnderstanding IndexesDeciding Whether to Create an IndexCreating an IndexDisplaying Index SpecificationsManaging Index UsageDropping IndexesSummaryQuiz
Chapter 7: Creating and Managing Views Using PROC SQL
OverviewCreating and Using PROC SQL ViewsDisplaying the Definition for a PROC SQL ViewManaging PROC SQL ViewsUpdating PROC SQL ViewsDropping PROC SQL ViewsSummaryQuiz
Chapter 8: Managing Processing Using PROC SQL
OverviewSpecifying SQL OptionsControlling ExecutionControlling OutputTesting and Evaluating PerformanceResetting OptionsUsing Dictionary TablesAdditional FeaturesSummaryQuiz
Part 2: SAS Macro Language
Chapter 9: Introducing Macro Variables
OverviewBasic ConceptsUsing Automatic Macro VariablesUsing User-Defined Macro VariablesProcessing Macro VariablesDisplaying Macro Variable Values in the SAS LogUsing Macro Functions to Mask Special CharactersUsing Macro Functions to Manipulate Character StringsUsing SAS Functions with Macro VariablesCombining Macro Variable References with TextSummaryQuiz
Chapter 10: Processing Macro Variables at Execution Time
OverviewCreating a Macro Variable During DATA Step ExecutionCreating Multiple Macro Variables During DATA Step ExecutionReferencing Macro Variables IndirectlyObtaining Macro Variable Values During DATA Step ExecutionCreating Macro Variables During PROC SQL Step ExecutionWorking with PROC SQL ViewsUsing Macro Variables in SCL ProgramsSummaryQuiz
Chapter 11: Creating and Using Macro Programs
OverviewBasic ConceptsDeveloping and Debugging MacrosUsing Macro ParametersUnderstanding Symbol TablesProcessing Statements ConditionallyProcessing Statements IterativelyUsing Arithmetic and Logical ExpressionsSummaryQuiz
Chapter 12: Storing Macro Programs
OverviewUnderstanding Session-Compiled MacrosStoring Macro Definitions in External FilesStoring Macro Definitions in Catalog SOURCE EntriesUsing the Autocall FacilityUsing Stored Compiled MacrosSummaryQuiz
Part 3: Advanced SAS Programming Techniques
Chapter 13: Creating Samples and Indexes
OverviewCreating a Systematic Sample from a Known Number of ObservationsCreating a Systematic Sample from an Unknown Number of ObservationsCreating a Random Sample with ReplacementCreating a Random Sample without ReplacementUsing IndexesCreating Indexes in the DATA StepManaging Indexes with PROC DATASETSManaging Indexes with PROC SQLDocumenting and Maintaining IndexesSummaryQuiz
Chapter 14: Combining Data Vertically
OverviewUsing a FILENAME StatementUsing an INFILE StatementAppending SAS Data SetsAdditional FeaturesSummaryQuiz
Chapter 15: Combining Data Horizontally
OverviewReviewing TerminologyWorking with Lookup Values Outside of SAS Data SetsCombining Data with the DATA Step Match-MergeUsing PROC SQL to Join DataComparing DATA Step Match-Merges and PROC SQL JoinsCombining Summary Data and Detail DataUsing an Index to Combine DataUsing a Transactional Data SetSummaryQuiz
Chapter 16: Using Lookup Tables to Match Data
OverviewUsing Multidimensional ArraysUsing Stored Array ValuesUsing PROC TRANSPOSEMerging the Transposed Data SetUsing Hash Objects as Lookup TablesSummaryQuiz
Chapter 17: Formatting Data
OverviewCreating Custom Formats Using the VALUE StatementCreating Custom Formats Using the PICTURE StatementManaging Custom FormatsUsing Custom FormatsCreating Formats from SAS Data SetsCreating SAS Data Sets from Custom FormatsSummaryQuiz
Chapter 18: Modifying SAS Data Sets and Tracking Changes
OverviewUsing the MODIFY StatementModifying All Observations in a SAS Data SetModifying Observations Using a Transaction Data SetModifying Observations Located by an IndexControlling the Update ProcessUnderstanding Integrity ConstraintsPlacing Integrity Constraints on a Data SetDocumenting Integrity ConstraintsRemoving Integrity ConstraintsUnderstanding Audit TrailsInitiating and Reading Audit TrailsControlling Data in the Audit TrailControlling the Audit TrailUnderstanding Generation Data SetsInitiating Generation Data SetsProcessing Generation Data SetsSummaryQuiz
Part 4: Optimizing SAS Programs
Chapter 19: Introduction to Efficient SAS Programming
OverviewOverview of Computing ResourcesAssessing Efficiency Needs at Your SiteUnderstanding Efficiency Trade-offsUsing SAS System Options to Track ResourcesUsing Benchmarks to Compare TechniquesSummary
Chapter 20: Controlling Memory Usage
OverviewControlling Page Size and the Number of BuffersUsing the SASFILE StatementAdditional FeaturesSummaryQuiz
Chapter 21: Controlling Data Storage Space
OverviewReducing Data Storage Space for Character VariablesReducing Data Storage Space for Numeric VariablesCompressing Data FilesUsing SAS DATA Step Views to Conserve Data Storage SpaceSummaryQuiz
Chapter 22: Using Best Practices
OverviewExecuting Only Necessary StatementsEliminating Unnecessary Passes through the DataReading and Writing Only Essential DataStoring Data in SAS Data SetsAvoiding Unnecessary Procedure InvocationSummaryQuiz
Chapter 23: Selecting Efficient Sorting Strategies
OverviewAvoiding Unnecessary SortsUsing a Threaded SortCalculating and Allocating Sort ResourcesHandling Large Data SetsRemoving Duplicate Observations EfficientlyAdditional FeaturesSummaryQuiz
Chapter 24: Querying Data Efficiently
OverviewUsing an Index for Efficient WHERE ProcessingIdentifying Available IndexesIdentifying Conditions That Can Be OptimizedEstimating the Number of ObservationsComparing Probable Resource UsageDeciding Whether to Create an IndexComparing Procedures That Produce Detail ReportsComparing Tools for Summarizing DataSummaryQuiz
Part 5: Quiz Answer Keys
Appendix 1: Quiz Answer Keys
Chapter 1: Performing Queries Using PROC SQLChapter 2: Performing Advanced Queries Using PROC SQLChapter 3: Combining Tables Horizontally Using PROC SQLChapter 4: Combining Tables Vertically Using PROC SQLChapter 5: Creating and Managing Tables Using PROC SQLChapter 6: Creating and Managing Indexes Using PROC SQLChapter 7: Creating and Managing Views Using PROC SQLChapter 8: Managing Processing Using PROC SQLChapter 9: Introducing Macro VariablesChapter 10: Processing Macro Variables at Execution TimeChapter 11: Creating and Using Macro ProgramsChapter 12: Storing Macro ProgramsChapter 13: Creating Samples and IndexesChapter 14: Combining Data VerticallyChapter 15: Combining Data HorizontallyChapter 16: Using Lookup Tables to Match DataChapter 17: Formatting DataChapter 18: Modifying SAS Data Sets and Tracking ChangesChapter 19: Introduction to Efficient SAS ProgrammingChapter 20: Controlling Memory UsageChapter 21: Controlling Data Storage SpaceChapter 22: Using Best PracticesChapter 23: Selecting Efficient Sorting StrategiesChapter 24: Querying Data Efficiently
Index
Footnote

Content preview from SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition

Chapter 5Creating and Managing Tables Using PROC SQL

Understanding Methods of Creating Tables

Creating an Empty Table by Defining Columns

Overview

Example

Specifying Data Types

Specifying Column Widths

Specifying Column Modifiers

Example

Displaying the Structure of a Table

Overview

Example

Creating an Empty Table That Is like Another Table

Overview

Example

Specifying a Subset of Columns from a Table

Example

Creating a Table from a Query Result

Inserting Rows of Data into a Table

Overview

Inserting Rows by Using the SET Clause

Example

Inserting Rows by Using the VALUES Clause

Example

Inserting Rows from a Query Result

Example

Creating a Table That Has Integrity ...

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

SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition

Publisher Resources

ISBN: 9781607649250Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition

by SAS Institute

Chapter 5Creating and Managing Tables Using PROC SQL

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.