Monthly Subscription Starting at AED 99 + VAT

Structured Query Language (SQL) using SAS
  • Level Foundation
  • Duration 27 hours
  • Course by SAS
  • Offered by

About

Course Description In this course, you learn about Structured Query Language (SQL) and how it can be used in SAS programs to create reports and query your data. “By the end of this course, a learner will be able to…” ● Query and subset data. ● Summarize and present data. ● Combine tables using joins and set operators. ● Create and modify tables and views. ● Create data-driven macro variables using a query. ● Access DBMS data with SAS/ACCESS technology.

Modules

Overview and Course Logistics

    • 1 Videos
    • 2 Readings
Show info about module content

1 Videos

  • Course Overview

2 Readings

  • Learner Prerequisites
  • Using Forums and Getting Help

Access SAS Software and Set Up Data for the Course (REQUIRED)

    • 2 Readings
Show info about module content

2 Readings

  • Access SAS Software for this Course
  • Set Up Data for This Course

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

SQL and PROC SQL

    • 7 Videos
    • 1 Assignment
Show info about module content

1 Assignment

  • Activity 1.01

7 Videos

  • 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

Module Review

    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Essentials

1 Readings

  • Learning More (Optional)

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

Generating Simple Reports

    • 11 Videos
    • 1 Readings
    • 7 Assignment
Show info about module content

7 Assignment

  • 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

11 Videos

  • 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

1 Readings

  • Date, Time, and Datetime Values

Summarizing and Grouping Data

    • 9 Videos
    • 1 Readings
    • 5 Assignment
Show info about module content

5 Assignment

  • Activity 2.06
  • Activity 2.07
  • Activity 2.08
  • Level 1 Practice: Eliminating Duplicates
  • Level 2 Practice: Grouping and Summarizing Data

9 Videos

  • 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

1 Readings

  • Commonly Used Summary Functions

Creating and Managing Tables

    • 5 Videos
    • 1 Readings
    • 2 Assignment
Show info about module content

2 Assignment

  • Activity 2.09
  • Activity 2.10

5 Videos

  • Creating Tables
  • Creating Tables from a Query
  • Creating Table Structures
  • Inserting Rows into Tables
  • Dropping Tables in PROC SQL

1 Readings

  • Additional Statements to Maintain Tables

Using DICTIONARY Tables

    • 3 Videos
    • 3 Assignment
Show info about module content

3 Assignment

  • 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

3 Videos

  • Scenario
  • DICTIONARY Tables
  • Demo: Using DICTIONARY Tables

Module Review

    • 2 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • PROC SQL Fundamentals

2 Readings

  • SQL Order of Execution Cheat Sheet (optional)
  • Learning More (optional)

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

Introduction to SQL Joins

    • 2 Videos
    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Activity 3.01

2 Videos

  • Joining Tables
  • Types of Joins

1 Readings

  • Table Relationships

Inner Joins

    • 9 Videos
    • 5 Assignment
Show info about module content

5 Assignment

  • Activity 3.02
  • Activity 3.03
  • Activity 3.04
  • Practice Level 1: Performing an Inner Join
  • Practice Level 2: Joining on Inequality

9 Videos

  • 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

Outer Joins

    • 5 Videos
    • 4 Assignment
Show info about module content

4 Assignment

  • Activity 3.05
  • Activity 3.06
  • Practice Level 1: Using Outer Joins to Find Nonmatches
  • Practice Level 2: Using Outer Joins to Summarize Data

5 Videos

  • 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

Complex Joins

    • 5 Videos
    • 3 Assignment
Show info about module content

3 Assignment

  • Activity 3.07
  • Activity 3.08
  • Activity 3.09

5 Videos

  • 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

Module Review

    • 2 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • SQL Joins

2 Readings

  • SQL Join Summary Cheat Sheet (optional)
  • Learning More (optional)

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

Subquery in the WHERE and HAVING Clauses

    • 8 Videos
    • 5 Assignment
Show info about module content

5 Assignment

  • 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

8 Videos

  • 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

In-Line Views (Query in the FROM Clause)

    • 5 Videos
    • 1 Readings
    • 3 Assignment
Show info about module content

3 Assignment

  • Activity 4.04
  • Practice Level 1: Using an In-Line View
  • Practice Level 2: Building a Complex Query Using In-Line Views

5 Videos

  • Using Temporary Tables
  • What Is an In-Line View?
  • Demo: Using an In-Line View
  • Creating a View
  • Making a View Portable

1 Readings

  • Advantages/Disadvantage of Views

Subquery in the SELECT Clause

    • 5 Videos
    • 3 Assignment
Show info about module content

3 Assignment

  • Activity 4.05
  • Practice Level 1: Remerging Summary Statistics
  • Practice Level 2: Using a Subquery in the SELECT Clause with an In-Line Views

5 Videos

  • Using a Subquery in the SELECT Clause
  • Remerging Summary Statistics in PROC SQL
  • Demo: Remerging Summary Statistics
  • Controlling Remerging
  • Remerging GROUP BY Summary Statistics

Module Review

    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Subqueries

1 Readings

  • Learning More (optional)

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

Introduction to Set Operators

    • 3 Videos
Show info about module content

3 Videos

  • Combining Data Using Set Operators
  • What Are Set Operators?
  • Using Set Operators

Using the INTERSECT, EXCEPT and UNION Set Operators

    • 6 Videos
    • 2 Readings
    • 4 Assignment
Show info about module content

4 Assignment

  • 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

6 Videos

  • 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

2 Readings

  • Using a Set Operator Versus a Join
  • Using a Set Operator Versus a Subquery

Using an OUTER UNION

    • 2 Videos
    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Practice: Using the OUTER UNION Set Operator

2 Videos

  • Using the OUTER UNION Operator
  • Demo: Using the OUTER UNION Operator to Combine Tables

1 Readings

  • SQL Versus Traditional SAS Programming

Module Review

    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Set Operators

1 Readings

  • Learning More (optional)

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

User-Defined Macro Variables

    • 1 Videos
    • 1 Assignment
Show info about module content

1 Assignment

  • Activity 6.01

1 Videos

  • Creating User-Defined Macro Variables

Data-Driven Macro Variables

    • 5 Videos
    • 1 Readings
    • 3 Assignment
Show info about module content

3 Assignment

  • 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

5 Videos

  • 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

1 Readings

  • Using Formats with Macro Variables

Module Review

    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Using and Creating Macro Variables in SQL

1 Readings

  • Learning More (optional)

Module Overview

    • 1 Videos
Show info about module content

1 Videos

  • Overview

Overview of SAS/ACCESS Technology

    • 2 Videos
    • 1 Readings
Show info about module content

2 Videos

  • Defining SAS/ACCESS Technology
  • Connection Options

1 Readings

  • Note about the Demos that Access Databases in this Lesson

SQL Pass-Through Facility

    • 4 Videos
    • 1 Readings
Show info about module content

4 Videos

  • 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

1 Readings

  • Saving SQL Pass-Through Query Results

SAS/ACCESS LIBNAME Statement

    • 3 Videos
    • 1 Readings
Show info about module content

3 Videos

  • Overview of the SAS/ACCESS LIBNAME Statement
  • Demo: Using the SAS/ACCESS LIBNAME Statement
  • SAS/ACCESS LIBNAME Considerations

1 Readings

  • Access SAS DBMS-Specific Reference Documentation

FEDSQL Procedure

    • 5 Videos
    • 1 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Activity 7.03

5 Videos

  • What Is PROC FEDSQL?
  • Comparing PROC SQL to PROC FEDSQL
  • PROC FEDSQL Syntax
  • Examining the Submitted SQL Code
  • FedSQL and SAS Viya

1 Readings

  • PROC SQL Versus PROC FEDSQL

Module Review

    • 2 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Accessing DBMS Data with SAS/ACCESS

2 Readings

  • PROC SQL Versus PROC FEDSQL Cheat Sheet (optional)
  • Learning More (optional)

Certification Practice Exam

    • 1 ExternalTool
Show info about module content

1 External Tool

  • SAS Certification Practice Exam: SAS 9.4 Advanced Programming Performance-Based Exam

Case Study

    • 3 Readings
    • 1 Assignment
Show info about module content

1 Assignment

  • Test Your Case Study Results

3 Readings

  • The Business Problem and Required Deliverables
  • Create the Case Study Input Tables
  • Select a Case Study Guide

Auto Summary

Discover the essentials of Structured Query Language (SQL) in SAS with this foundational course designed for data enthusiasts. Learn to query, subset, and summarize data, combine tables, create and modify tables and views, and access DBMS data using SAS/ACCESS technology. Taught by expert instructors on Coursera, this 1620-minute course offers a comprehensive starter subscription for those looking to enhance their data science and AI skills. Perfect for beginners aiming to master SQL in SAS programs.

Peter Styliadis
Instructor

Peter Styliadis