Explore

Structured Query Language (SQL) using SAS

Approx. 24 hours to complete

Save Course

Go to Course

Course Summary

Learn how to use SAS and SQL to manage and manipulate data in this comprehensive course. Gain practical skills that are in high demand in the data analysis industry.

Key Learning Points

Learn how to use SAS to manage and manipulate data
Understand how to use SQL to query databases
Gain practical skills that are in high demand in the data analysis industry

Learning Outcomes

Ability to use SAS to manage and manipulate data
Ability to use SQL to query databases
Practical skills that are in high demand in the data analysis industry

Prerequisites or good to have knowledge before taking this course

Basic understanding of programming concepts
Access to SAS software (free trial available)

Course Difficulty Level

Intermediate

Course Format

Online
Self-paced

Similar Courses

Data Management and Visualization
Data Analysis and Interpretation
Data Science Methodology

Related Education Paths

Notable People in This Field

Hadley Wickham
Hilary Mason
David Robinson

Related Books

Description

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.

Outline

Course Overview and Data Setup
Course Overview
Learner Prerequisites
Using Forums and Getting Help
Access SAS Software for this Course
Set Up Data for This Course

Essentials
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

PROC SQL Fundamentals
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

SQL Joins
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

Subqueries
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

Set Operators
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

Using and Creating Macro Variables in SQL
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

Accessing DBMS Data with SAS/ACCESS
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

Case Study (Honors) and Certification Practice Exam
The Business Problem and Required Deliverables
Create the Case Study Input Tables
Select a Case Study Guide
Test Your Case Study Results

Summary of User Reviews

Learn SAS and SQL data analysis with Coursera. Users praise the course for its comprehensive coverage and hands-on experience. The course is highly recommended for beginners and intermediate-level learners.

Key Aspect Users Liked About This Course

Comprehensive coverage of SAS and SQL data analysis with hands-on experience.

Pros from User Reviews

Well-structured course material
Practical and applicable learning approach
Interactive quizzes and assignments
Responsive instructor support
Great preparation for certification exams

Cons from User Reviews

Limited advanced-level content
Some technical issues with the platform
Not suitable for experienced SAS and SQL users
Lack of flexibility in pacing and schedule
Some learners find the course too basic

Recommended for you

Programming in SAS for beginners

Become an expert SAS analyst. Learn basics of SAS programming and advanced topics like SAS SQL Procedure and SAS Macros SAS Programming - from beginners to advanced users...

Save Course

Intro to Relational Databases

If you look under the hood of a lot of major web sites — from Wikipedia to Reddit — you'll find a relational database somewhere....

Save Course

FULLSTACK Web Development- HTML,CSS, JavaScript, PHP, ELIXIR

Learn to Master multiple Front & Back end technologies to develop an advanced Web Application or Website Are you passionate about developing a complete website from scratch?...

Save Course

SAS Advanced Programmer Professional Certificate

Grow Your Career with a SAS® Credential. Master the skills required for the SAS® Advanced Programming Professional certification. These skills prepare you for the SAS Advanced Programming Professional certification exam....

Save Course