SAS Institute Of Management Studies - Structured Query Language (SQL) using SAS 

Course Overview

Learner Prerequisites

Using Forums and Getting Help

Access SAS Software for this Course

Set Up Data for This Course

Overview

What Is SQL?

What Is PROC SQL?

PROC SQL Syntax

Exploring Tables

Demo: Exploring the Customer Table

SQL Options

Comparing SQL and the DATA Step

Learning More (Optional)

Activity 1.01

Essentials Review Quiz

Overview

Filtering Rows Using the WHERE Clause

Special WHERE Operators: Missing Values

Additional Special WHERE Operators

Sorting the Output with the ORDER BY Clause

Ordering Columns by Position

Enhancing Reports

Demo: Creating Simple Reports

Creating a New Column

Subsetting Calculated Values

Assigning Values Conditionally with the CASE Expression

Demo: Assigning Values Conditionally

Eliminating Duplicate Rows with the DISTINCT Keyword

Summarizing Data

Demo: Using Summary Functions to Analyze a Table

Summarizing Data Using the COUNT Function

Grouping Data

Demo: Analyzing Groups of Data

Summarizing Date and Time Data

Counting Rows Using a Boolean Expression

Demo: Summarizing Data Using a Boolean Expression

Creating Tables

Creating Tables from a Query

Creating Table Structures

Inserting Rows into Tables

Dropping Tables in PROC SQL

Scenario

DICTIONARY Tables

Demo: Using DICTIONARY Tables

Date, Time, and Datetime Values

Commonly Used Summary Functions

Additional Statements to Maintain Tables

SQL Order of Execution Cheat Sheet (optional)

Learning More (optional)

Activity 2.01

Activity 2.02

Activity 2.03

Activity 2.04

Activity 2.05

Level 1 Practice: Querying a Table

Level 2 Practice: Working with Datetime Values

Activity 2.06

Activity 2.07

Activity 2.08

Level 1 Practice: Eliminating Duplicates

Level 2 Practice: Grouping and Summarizing Data

Activity 2.09

Activity 2.10

Activity 2.11

Practice Level 1: Counting the Number of Tables in a Library

Practice Level 2: Counting the Number of Tables in All Libraries

PROC SQL Fundamentals Review Quiz

Overview

Joining Tables

Types of Joins

Joining Two Tables with an Inner Join

Demo: Performing an Inner Join with PROC SQL

Alternative SQL Inner Join Syntax

Using Table Aliases

Matching Rows with a Natural Join

Selecting Data from More Than Two Tables

Demo: Performing an Inner Join with Four Tables

Handling Missing Values

Creating Non-Equijoins

SQL Outer Joins

Performing Left and Right Outer Joins

Joining Two Tables with a Full Join

Demo: Performing a Full Join with Proc SQL

Identifying Nonmatching Rows

Using Reflexive Joins

Demo: Performing a Reflexive Join

Using Functions to Join Tables

Using Functions to Join When Column Types Are Different

Converting Column Values with Functions

Table Relationships

SQL Join Summary Cheat Sheet (optional)

Learning More (optional)

Activity 3.01

Activity 3.02

Activity 3.03

Activity 3.04

Practice Level 1: Performing an Inner Join

Practice Level 2: Joining on Inequality

Activity 3.05

Activity 3.06

Practice Level 1: Using Outer Joins to Find Nonmatches

Practice Level 2: Using Outer Joins to Summarize Data

Activity 3.07

Activity 3.08

Activity 3.09

SQL Joins Review

Overview

What Is a Subquery?

Using a Subquery in the WHERE Clause

Demo: Using a Subquery That Returns a Single Value

Using a Subquery in the HAVING Clause

Subquery That Returns Multiple Values

Demo: Using a Subquery That Returns Multiple Values

Using the ANY Keyword

Using Correlated Subqueries

Using Temporary Tables

What Is an In-Line View?

Demo: Using an In-Line View

Creating a View

Making a View Portable

Using a Subquery in the SELECT Clause

Remerging Summary Statistics in PROC SQL

Demo: Remerging Summary Statistics

Controlling Remerging

Remerging GROUP BY Summary Statistics

Advantages/Disadvantage of Views

Learning More (optional)

Activity 4.01

Activity 4.02

Activity 4.03

Practice Level 1: Using a Subquery That Returns a Single Value

Practice Level 2: Using a Subquery with Multiple Functions

Activity 4.04

Practice Level 1: Using an In-Line View

Practice Level 2: Building a Complex Query Using In-Line Views

Activity 4.05

Practice Level 1: Remerging Summary Statistics

Practice Level 2: Using a Subquery in the SELECT Clause with an In-Line Views

Subqueries Review

Overview

Combining Data Using Set Operators

What Are Set Operators?

Using Set Operators

Using the INTERSECT Operator

Using the EXCEPT Operator

Using the UNION Operator

Demo: Using the UNION Operator to Find All Unique Rows

Default Behavior of the UNION Operator

Combining Set Operators

Using the OUTER UNION Operator

Demo: Using the OUTER UNION Operator to Combine Tables

Using a Set Operator Versus a Join

Using a Set Operator Versus a Subquery

SQL Versus Traditional SAS Programming

Learning More (optional)

Activity 5.01

Activity 5.02

Practice Level 1: Using the EXCEPT Set Operator

Practice Level 2: Using the EXCEPT Set Operator with the DISTINCT Keyword

Practice: Using the OUTER UNION Set Operator

Set Operators Review

Overview

Creating User-Defined Macro Variables

Creating Data-Driven Macro Variables

Demo: Using a PROC SQL Data-Driven Macro Variable

Creating Multiple Macro Variables

Concatenating Values into One Macro Variable

Demo: Using PROC SQL to Concatenate Multiple Values into One Macro Variable

Using Formats with Macro Variables

Learning More (optional)

Activity 6.01

Activity 6.02

Practice Level 1: Creating a Macro Variable from an SQL Query

Practice Level 2: Creating a Macro Variable with a List of Values from an SQL Query

Using and Creating Macro Variables in SQL Review

Overview

Defining SAS/ACCESS Technology

Connection Options

Overview of the SQL Pass-Through Facility

Steps for Using the SQL Pass-Through Facility

Demo: Using an SQL Pass-Through Query

SQL Pass-Through Facility Considerations

Overview of the SAS/ACCESS LIBNAME Statement

Demo: Using the SAS/ACCESS LIBNAME Statement

SAS/ACCESS LIBNAME Considerations

What Is PROC FEDSQL?

Comparing PROC SQL to PROC FEDSQL

PROC FEDSQL Syntax

Examining the Submitted SQL Code

FedSQL and SAS Viya

Note about the Demos that Access Databases in this Lesson

Saving SQL Pass-Through Query Results

Access SAS DBMS-Specific Reference Documentation

PROC SQL Versus PROC FEDSQL

PROC SQL Versus PROC FEDSQL Cheat Sheet (optional)

Learning More (optional)

Activity 7.03

Accessing DBMS Data with SAS/ACCESS Review

The Business Problem and Required Deliverables

Create the Case Study Input Tables

Select a Case Study Guide

Test Your Case Study Results