book

SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

by SAS Institute

December 2015

Beginner

482 pages

13h 27m

English

SAS Institute

Read now

Unlock full access

Syntax Conventions for the SAS LanguageOverview of Syntax Conventions for the SAS LanguageSyntax Components
Special Characters
OverviewAdditional SAS SQL System Option and PROC SQL Statement OptionsSupport Added for Linguistic Collation
Replaced BUFFERSIZE PROC SQL Statement OptionSupport Added for the SQLXOPENERRS Macro Variable
What Is SQL?What Is the SQL Procedure?
Tables
ViewsNull ValuesComparing PROC SQL with the SAS DATA Step

How to Use the SELECT StatementSELECT and FROM ClausesWHERE ClauseORDER BY ClauseGROUP BY ClauseHAVING Clause
Selecting Columns in a TableSelecting All Columns in a TableSelecting Specific Columns in a TableEliminating Duplicate Rows from the Query Results
Creating New Columns (1/2)Adding Text to OutputCalculating ValuesAssigning a Column AliasReferring to a Calculated Column by Alias
Assigning Values ConditionallyReplacing Missing ValuesSpecifying Column Attributes
Overview of Sorting DataSorting by ColumnSorting by Multiple ColumnsSpecifying a Sort OrderSorting by Calculated ColumnSorting by Column Position
Sorting by Columns That Are Not SelectedSpecifying a Different Sorting SequenceSorting Columns That Contain Missing Values
Using a Simple WHERE ClauseRetrieving Rows Based on a ComparisonRetrieving Rows That Satisfy Multiple Conditions
Using Other Conditional OperatorsUsing Truncated String Comparison Operators
Using a WHERE Clause with Missing Values
Overview of Summarizing DataUsing Aggregate FunctionsSummarizing Data with a WHERE ClauseDisplaying SumsCombining Data from Multiple Rows into a Single RowRemerging Summary Statistics
Using Aggregate Functions with Unique ValuesSummarizing Data with Missing Values
Grouping by One ColumnGrouping without SummarizingGrouping by Multiple ColumnsGrouping and Sorting DataGrouping with Missing Values
Overview of Filtering Grouped DataUsing a Simple HAVING ClauseChoosing between HAVING and WHEREUsing HAVING with Aggregate Functions
Introduction
Overview of Selecting Data from More Than One Table By Using JoinsInner Joins
Outer JoinsSpecialty Joins
Using the Coalesce Function in JoinsComparing DATA Step Match-Merges with PROC SQL Joins
Single-Value SubqueriesMultiple-Value SubqueriesCorrelated SubqueriesTesting for the Existence of a Group of ValuesMultiple Levels of Subquery Nesting
Combining a Join with a Subquery
Working with Two or More Query ResultsProducing Unique Rows from Both Queries (UNION)Producing Rows That Are in Only the First Query Result (EXCEPT)Producing Rows That Belong to Both Query Results (INTERSECT)Concatenating Query Results (OUTER UNION)
Producing Rows from the First Query or the Second Query
Creating TablesCreating Tables from Column DefinitionsCreating Tables from a Query ResultCreating Tables like an Existing TableCopying an Existing TableUsing Data Set Options
Inserting Rows with the SET ClauseInserting Rows with the VALUES ClauseInserting Rows with a Query
Updating All Rows in a Column with the Same ExpressionUpdating Rows in a Column with Different ExpressionsHandling Update Errors
Altering ColumnsAdding a ColumnModifying a ColumnDeleting a Column
Using PROC SQL to Create IndexesTips for Creating Indexes
Deleting a TableUsing SQL Procedure Tables in SAS Software
Overview of Creating and Using PROC SQL ViewsCreating ViewsDescribing a ViewUpdating a ViewEmbedding a LIBNAME in a ViewDeleting a ViewSpecifying In-Line ViewsTips for Using SQL Procedure Views
Using SQL Procedure Views in SAS Software
Using PROC SQL Options to Create and Debug QueriesOverview of Using PROC SQL Options to Create and Debug QueriesRestricting Row Processing with the INOBS= and OUTOBS= OptionsLimiting Iterations with the LOOPS= OptionChecking Syntax with the NOEXEC Option and the VALIDATE StatementExpanding SELECT * with the FEEDBACK OptionTiming PROC SQL with the STIMER OptionResetting PROC SQL Options with the RESET Statement
Overview of Improving Query PerformanceUsing Indexes to Improve PerformanceUsing the Keyword ALL in Set OperationsOmitting the ORDER BY Clause When Creating Tables and ViewsUsing In-Line Views versus Temporary TablesComparing Subqueries with JoinsUsing WHERE Expressions with JoinsOptimizing the PUT Function
Disabling the Remerging of Data When Using Summary FunctionsUsing Column AliasesOverview of Column AliasesColumn Alias ExtensionsUsing the CALCULATED Keyword with Column Aliases
What Are DICTIONARY Tables?Retrieving Information about DICTIONARY Tables and Sashelp ViewsUsing DICTIONARY.Tables
Using DICTIONARY.ColumnsDICTIONARY Tables and Performance
Overview of Using PROC SQL with the SAS Macro FacilityCreating Macro Variables in PROC SQLConcatenating Values in Macro VariablesDefining Macros to Create Tables
Using the PROC SQL Automatic Macro Variables
Overview of Accessing a DBMS with SAS/ACCESS SoftwareConnecting to a DBMS By Using the LIBNAME StatementConnecting to a DBMS By Using the SQL Procedure Pass-Through Facility
Updating PROC SQL and SAS/ACCESS Views
Computing a Weighted AverageProblemBackground InformationSolution
Comparing TablesProblemBackground InformationSolution
Overlaying Missing Data ValuesProblemBackground InformationSolutionHow It Works
ProblemBackground InformationSolution
Counting Duplicate Rows in a TableProblemBackground InformationSolution
Expanding Hierarchical Data in a TableProblemBackground InformationSolutionHow It Works
ProblemBackground InformationSolutionHow It Works
ProblemBackground InformationSolutionHow It Works
ProblemBackground InformationSolutionHow It Works
ProblemBackground InformationSolutionHow It Works
ProblemBackground InformationSolution
Creating and Using Macro VariablesProblemBackground InformationSolution
Using PROC SQL Tables in Other SAS ProceduresProblemBackground InformationSolutionHow It Works
What Is the SQL Procedure?What Are PROC SQL Tables?What Are Views?SQL Procedure Coding Conventions
Syntax: SQL Procedure (1/11)PROC SQL Statement
ALTER TABLE Statement
CONNECT StatementCREATE INDEX Statement
CREATE TABLE Statement
CREATE VIEW StatementDELETE StatementDESCRIBE Statement
DISCONNECT StatementDROP StatementEXECUTE StatementINSERT StatementRESET StatementSELECT Statement
SELECT ClauseINTO Clause
FROM ClauseWHERE Clause
GROUP BY ClauseHAVING ClauseORDER BY ClauseUPDATE Statement
VALIDATE Statement
Creating a Table and Inserting Data into ItCreating a Table from a Query's ResultUpdating Data in a PROC SQL Table
Joining Two TablesCombining Two Tables
Reporting from DICTIONARY TablesPerforming an Outer Join
Creating a View from a Query’s ResultJoining Three Tables
Querying an In-Line ViewRetrieving Values with the SOUNDS-LIKE Operator
Joining Two Tables and Calculating a New ValueProducing All the Possible Combinations of the Values in a Column
Matching Case Rows and Control RowsCounting Missing Values with a SAS Macro
Overview
BETWEEN ConditionBTRIM FunctionCALCULATEDCASE ExpressionCOALESCE Functioncolumn-definition
column-modifiercolumn-nameCONNECTION TOCONTAINS ConditionEXISTS ConditionIN Condition
IS Conditionjoined-table
LIKE ConditionLOWER Functionquery-expression
sql-expression
SUBSTRING Functionsummary-function
table-expressionUPPER Function
DictionarySQLCONSTDATETIME System OptionSQLGENERATION= System OptionSQLIPONEATTEMPT System Option
SQLREDUCEPUT= System OptionSQLREDUCEPUTOBS= System OptionSQLREDUCEPUTVALUES= System Option
SQLUNDOPOLICY= System OptionSYS_SQLSETLIMIT Macro Variable
Overview
HousesMatch_11
Proclib.March
Proclib.Schedule2Proclib.Staff
StoresSurvey

Content preview from SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Index

abbreviating column names 76

aggregate functions 56

creating macro variables from result of

158

HAVING clause with 70

table of 56

using 56

with unique values 61

alias

assigning a column alias 30

referring to calculated columns by 31

table aliases 76

ALL keyword 345

set operators and 143

ALTER TABLE statement 232

ANSI standard

SQL procedure and 387

arithmetic operators 388

asterisk (*) notation 254

automatic macro variables 157, 162

averages, weighted 178

BETWEEN condition 316

BETWEEN-AND operators

retrieving rows 51

Boolean operators

retrieving rows 47

BTRIM function 316

CALCULATED 317

calculated columns 29

assigning column alias to 30

referring to by alias 31

sorting ...

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.

Start your free trial

SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition

SAS Institute

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

SAS Institute

DB2® Universal Database for OS/390® Version 7.1 Certification Guide

Richard Yevich, Susan Lawson

Base SAS 9.4 Procedures Guide, Fifth Edition, 5th Edition

SAS Institute

Publisher Resources

ISBN: 9781629601359

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.

You might also like

SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

DB2® Universal Database for OS/390® Version 7.1 Certification Guide

Base SAS 9.4 Procedures Guide, Fifth Edition, 5th Edition

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.