PROC SQL by Example: Using SQL Within SAS

Description

Using a side-by-side approach, this concise reference guide includes many extensively explained examples showing equivalent DATA step and SQL code, enabling SAS users to take advantage of existing SAS skills and knowledge while learning about SQL. Introduction 1.1 More about SQL Standards and Extensions Fundamental Differences between SQL and the DATA Step RDBMS Heritage 1.2 More about This Book Purpose Perspective Presentation Basic Building Blocks 2.1 The Simplest SELECT Statement 2.2 A More Selective SELECT 2.3 Storing Results 2.4 Column Subsets 2.5 New Columns 2.6 Aggregation Grand Totals and More Subtotals and More Details 2.7 Conditionality 2.8 Filtering Filtering from the Source Filtering Aggregated Data 2.9 Reordering Rows 2.10 Elimination of Duplicates 2.11 Summary More Building Blocks 3.1 Combining Summary Statistics with Original Detail 3.2 Summary Statistics Based on Distinct Values 3.3 Preprocessing the Source with Inline Views 3.4 Summary Joins 4.1 Avoiding Ambiguity in Column References 4.2 The Simplest Merges and Joins 4.3 Matching with Nonrepeating Keys Symmetrically Inclusive Joins Asymmetric Joins Match MERGE versus INNER JOIN Summary 4.4 Matching with Repeating Keys INNER JOIN OUTER JOIN 4.5 More about Joins and Merges Three or More Sources Composite Keys 4.6 More about Joins Natural Joins Implicit Joins Generality of Join Conditions 4.7 Summary Subqueries 5.1 Contexts That Expect Subqueries The EXISTS Condition The IN Condition The ANY Keyword The ALL Keyword Subqueries Outside the WHERE Clause 5.2 General Usage of Subqueries 5.3 Summary Set Operators 6.1 The Contrast between Joins and Set Operators 6.2 Set Operators: Preview 6.3 Concatenation and Interleaving with OUTER UNION 6.4 Data Type Compatibility 6.5 Overview: UNION, INTERSECT, and EXCEPT Column Alignment Leftover Columns Row Accretion Duplicate Rows Conclusion 6.6 UNION 6.7 INTERSECT 6.8 EXCEPT 6.9 Summary Global Statements, Options, and Session Management 7.1 Global Statements 7.2 PROC SQL Options 7.3 Summary Using the Macro Facility with PROC SQL 8.1 Generating PROC SQL Code 8.2 Populating Macro Variables User-Defined Macro Variables DICTIONARY Tables Automatic Macro Variables 8.3 Summary Table Maintenance and Alternate Strategies 9.1 Environment for Examples 9.2 Distinguishing Persistence from Replacement 9.3 Life Cycle of a Table Creation Using a Query Creation Using a Model Creation Using Specifications Help from the DESCRIBE TABLE Statement Deleting a Table 9.4 Data Maintenance Inserting Rows into an Existing Table Deleting Rows Selective Corrections 9.5 Metadata Maintenance 9.6 Changing Structure 9.7 Changing Features Indexes Integrity Constraints Audit Trails Generation Data Sets 9.8 Summary Views 10.1 Defining Views 10.2 Using Views 10.3 Inspecting Views 10.4 Changing a Table via a View 10.5 Managing Library References 10.6 Summary PROC SQL as a Report Generator 11.1 Simple Reports 11.2 Complex Reports 11.3 Reports with Long Character Strings 11.4 PROC SQL and the Output Delivery System 11.5 Summary Mixed Solutions 12.1 Example: Schedule Matrix 12.2 Example: Identifying Spikes in a Series 12.3 Example: Using PROC TRANSPOSE to Normalize 12.4 Summary Performance Tuning 13.1 Resource Example: The Effect of an Index 13.2 Code Example: The Advantage of Equijoins 13.3 Summary Documentation Roadmap 14.1 Where to Start? SAS 9.2 SQL Query Window User's Guide SAS 9.2 SQL Procedure User's Guide Base SAS 9.2 Procedures Guide (SQL Chapter) None of the Above 14.2 Following Cross References SELECT Statement sql-expression query-expression table-expression 14.3 The Three Expressions Revisited SELECT Statement query-expression table-expression sql-expression 14.4 Could It Be More Logical? 14.5 Summary SASHELP.CLASS Data Set Online Resources 65193_freechapter.pdf Cody_CodysCollection_frontcover TOC chap1_new Introduction Task: Converting character values to numeric values Keywords Program 1.1: Converting character values to numeric values Task: Converting character values to numeric values using a macro Keywords Program 1.2: Presenting a macro to perform character-to-numeric conversion Program 1.3: Testing the character-to-numeric conversion macro Task: Converting a specific value such as 999 to a missing value for all numeric variables in a SAS data set Keywords Program 1.4: Converting a specific value such as 999 to a missing value for all numeric variables in a SAS data set Task: Converting a specific value such as 'NA' to a missing value for all character variables in a SAS data set Keywords Program 1.5: Converting a specific value such as ‘NA’ to a missing value for all character variables in a SAS data set Task: Changing all character values to either uppercase, lowercase, or proper case Keywords Program 1.6: Changing case for all character variables in a SAS data set Task: Reading a numeric value that contains units such as Lbs. or Kgs. in the value Keywords Program 1.7: Reading data values that contain units Task: Solving part of the previous task using a Perl regular expression Keywords Program 1.8: Using a Perl regular expression to extract the digit and units part of a character value Conclusion AboutThisAuthor_new CodyCodysCollection_FreeChapAdFINAL chap7.new.pdf 7.1 Global Statements 7.2 PROC SQL Options 7.3 Summary