Course Overview and downloadable course materials
  • Course Introduction
  • Downloadable course materials
Free cloud-based SAS software option for learning: SAS OnDemand for Academics
  • Moving from SAS University Edition to SAS ondemand for Academics
  • Create a SAS account to access SAS ondemand for Academics
  • Upload course data files and SAS programs into SAS ondemand for academics
  • change file path/directory in SAS ondemand for academics
  • examples: update and run SAS programs in SAS ondemand for academics
SAS SQL: Proc SQL introduction
  • Proc SQL introduction
SAS SQL: SELECT statement: Retrieving Data from a Single Table
  • SELECT Statement: Overview
  • SELECT Clause: Eliminate Duplicate Rows Using the DISTINCT Keyword
  • SELECT Clause: Create Columns
  • SELECT Clause: Assign Values Conditionally by Using a CASE Expression
  • SELECT Clause: Replace Missing Values with COALESCE Function and Case Expression
  • ORDER BY Clause: Sort Data
  • WHERE Clause: Retrieving Rows That Satisfy a Condition
  • Summarizing Data using aggregate functions (or summary functions)
  • GROUP BY Clause: Group Data
  • HAVING Clause: Filter Grouped Data
  • Quiz
  • Coding Exercise
  • Coding Exercise: Solution
SAS SQL: JOIN: Retrieving Data from Multiple Tables
  • Inner Join Data: Use Where Clause, Use Inner Join keyword, Comparison Operators
  • Inner Join Data: Effect of Missing Values on Join, Use Multiple Matching Columns
  • Inner Join Data: Inner Join More Than Two Tables, Self-Joins
  • Outer Join Data: Left, Right and Full Outer Joins
  • Specialty Joins: cross joins, union joins, and natural joins
  • Comparison of DATA Step Match-Merges with PROC SQL Joins: Part 1
  • Comparison of DATA Step Match-Merges with PROC SQL Joins: Part 2
  • Quiz
  • Coding Exercise
  • Coding Exercise: Solution
SAS SQL: Subqueries: Using Subqueries to Select Data
  • Single-Value and Multiple-Value Subqueries
  • Testing for the Existence of a Group of Values
  • Correlated Subqueries and Combining a Join with a Subquery
  • Multiple Levels of Subquery Nesting
  • Quiz
  • Coding Exercise
  • Coding Exercise: Solution
SAS SQL: Combine Queries with Set Operators
  • UNION: Produce all unique rows from both queries
  • EXCEPT: Produce all unique rows from both queries
  • INTERSECT: Produce rows that are common to both query results
  • OUTER UNION: Concatenate the query results
  • EXCLUSIVE UNION: Produce Rows from the First or Second Query
  • Quiz
  • Coding Exercise
  • Coding Exercise: Solution
SAS SQL: Creating and Updating Tables and Views
  • Create New Table without Rows by using the CREATE TABLE Statement
  • Create Tables from a Query Result using CREATE TABLE with AS Keyword
  • Create Tables like an Existing Table with LIKE clause & Copy an Existing Table
  • Insert Rows into Tables using Insert Statement with the SET Clause
  • Insert Rows into Tables using Insert Statement with the VALUE Clause
  • Insert Rows into Tables using Insert Statement with a Query
  • Update All Rows with the Same Expression using Update Statement
  • Update All Rows with Different Expressions using Update Statement
  • Delete Rows with the DELETE statement
  • Alter Columns with the ALTER TABLE statement: Add a Column
  • Alter Columns with the ALTER TABLE statement: Modify or Delete a Column
  • Use SQL Procedure Tables & Delete Tables
  • Proc SQL Views: Create, Describe, and Delete PROC SQL Views
  • Proc SQL Views: Use Proc SQL Views
  • Proc SQL Views: Specify/Use In-Line Views
  • Quiz
  • Coding Exercise
  • Coding Exercise: Solution
SAS SQL: Practical Examples Using PROC SQL
  • Comparing Old and New Tables
  • Overlaying Missing Data Values
  • Computing Percentages within Subtotals
  • Counting Exact Duplicate Rows in a Table
  • Expanding Hierarchical Data in a Table
  • Summarizing Data in Multiple Columns
  • Creating a Summary Report
  • Creating a Customized Sort Order
  • Updating a Table with Values from Another Table
SAS Macro: SAS Macro Introduction
  • SAS Macro Overview
  • Replacing Text Strings Using Macro Variables
  • Generating SAS Code Using Macros
  • Passing Information into a Macro Using Parameters
  • Quiz
  • Coding Exercise
  • Coding Exercise: Solution
SAS Macro: Macro Variables & Macro Functions
  • SAS macro variables: Introduction
  • Automatic macro variables
  • User-Defined Macro Variables
  • Displaying Macro Variable Values in the SAS Log: using SYMBOLGEN
  • Displaying Macro Variable Values in the SAS Log: using %PUT
  • Using Macro Quoting Function to Mask Special Characters:%STR
  • More on Using Macro Quoting Functions to Mask Special Characters:%STR
  • Using Macro Functions to Manipulate Character Strings: %UPCASE
  • Using Macro Functions to Manipulate Character Strings: %SUBSTR