SAS DATA WAREHOUSING CONCEPTS

• What is a Data Warehouse ?
• What is a Data Mart ?
• What is the difference between Relational Databases and the Data in Data Warehouse (OLTP versus OLAP)
• Why do we need Data Warehouses when the Relational Source Database exists
• Multi Dimensional Analysis and Decision Support Reporting from Data Warehouse
• Data Warehouse Architecture (ETL Design)
• Normalized Relational Database Design (Entity Relationship Model)
• Dimensional Data Modeling
• Star Schema Design
• Snowflake Schema Design
• Slowly Changing Dimensions Why SAS BI ?Capabilities of SAS B12 Advantages of SAS BI Over Base & Advance SAS
• B1 Architecture
• SAS BI Tools

BASE SAS INTRODUCTION

• An Overview of the SAS System
• SAS Tasks
• Output produced by the SAS System
• SAS Tools (SAS Program - Data step and Proc step)
• A sample SAS program
• Exploring SAS Windowing Environment Navigation

DATA ACCESS & DATA MANAGEMENT

• SAS Data Libraries
• Rules for Writing SAS Programs / Statements, Dataset Variable Name Getting familiar with SAS Dataset
• Data portion of the SAS Dataset
• Rules for writing Dataset names / Variable names
• Attributes of a Variable (Numeric / Character)
• Options
• System Options (nodate, linesize, pagesize, pageno etc)
• Dataset Options (Drop, Keep, Rename, Where, Firstobs= Obs=)
• How SAS works (Flow of Data Step Processing - Compilation & Execution phase)
• Input Buffer
• Program data vector (PDV)
• Descriptor Information of a SAS Dataset

DATALINES OR CARDS DATA TRANSFORMATIONS

• SAS Date Values
• Length Statement
• Creating multiple output SAS datasets from singe input SAS dataset
• Conditionally writing observations to one or more SAS datasets
• Outputting Multiple Observations (Implicit Output)
• Selecting Variables and observations (DROP or KEEP statement and DROP= or KEEP = dataset options)
• Controlling which Observations are read (OBS= FIRSTOBS = Options)
• The Data Statement_Null_
• The_N_Automatic Variable
• Creating Subset of observations
• Conditional Processing using IF-THEN and ELSE statement, IF----THEN DO ; ----END;ELSE DO;----END;
• DO WHILE Statement
• DO UNTIL Statement
• Iterative DO loop Processing
• Where Statement OR Where Condition (dataset)
• Deciding whether to use a Where statement or Subsetting IF statement
• Accumulating Totals for a Group of Data (BY- Group Processing (First & Last)
• Multiple BY variables
• DATASETS Procedure ( To modify the Variable name/lable/format/informat)
• Reading SAS datasets and Creating Variables
• Creating an Accumulating Variable (The RETAIN Statement)
• The DELETE Statement
• The SUM Statement
• The RENAME = Data Set option
• Combining SAS Datasets
• 1. Concatenating SAS Data Sets Using SET statement in DATA Step
• Inter Leaving SAS Data Sets
• Merging SAS Data Sets
• Match-Merge
• Using Merge Statement
• THE IN = Data Set option
• Additional Features of merging SAS Datasets
• One-to-Many Merging
• Many-to-Many Merging

READING RAW DATA FROM EXTERNAL FILE ( INFILE & INPUT STATEMENT )

• Introduction to Raw Data
• Factors considered to examine the raw data
• Reading Unaligned Data (List Input)
• Reading Data Aligned to Columns (Column Input)
• Reading Data that requires Special Instructions (Formatted Input)
• Controlling the position of the Pointer in Formatted Input
• Absolute - Column pointer control (@)
• Relative- Column pointer control (+)
• Mixed Style Input ( Mixing List, Input. Formatted Input styles in one INPUT Staement)
• Using colon (:) modifier to specify an informat in the INPUT Statement )
• Recognize delimiter in the raw data file (Using DLM= option in INFILE Statement
• Missing data at the end of row (Using MISSOVER option in INFILE statement )
• Missing values without placeholders (DSD option in INFILE statement)
• Reading a raw data file with multiple records per observation(Column pointer controls)
• Method1: Using Multiple INPUT statement
• Method2: Using Line Pointer Control (/)
• Reading Variables from multiple records in any order (#n)
• Line Hold Specifies in INPUT statement
• The Single Trailing @
• The Double Trailing @@ ( Multiple Observations per Record)
• Methods of Control in INFILE statement
• FLOWOVER
• STOPOVER
• MISSOVER
• TRUNCOVER
• Writing to an External File (FILE & PUT Statement )
• Reading Excel Spreadsheets (IMPORT Wizard / Import Procedure)

SAS FUNCTIONS

• Manipulating Character Values (SUBSTRING / RIGHT / LEFT / SCAN/ CONCATENATION TRIM / FIND / INDEX / UPCASE / LOWCASE / COMPRESS / LENGTH )
• Manipulation Numeric Values ( ROUND / CEIL / FLOOR / INT / SUM / MEAN /MIN/MAX)
• Manipulating Numeric Values based on DATES ( MDY / TODAY / INTCK / YRDIF)
• Converting Variable Type
• INPUT ( character-to-numeric)
• PUT (numeric-to-character)
• Debugging SAS program (DEBUG Option)
• SAS VARIABLE Lists
• SAS Arrays
• Enhancing Report Output
• Defining Titles & Footnotes
• Formatting Data values ( Date, Character & Numeric values )
• Creating User-Defined Formats (Proc Format)
• Formats & Informats

ANALYSIS & PRESENTATION

• Descriptor portion of the SAS Data Set ( Proc Contents)
• Producing List Reports (Proc Print)
• Sequencing and Grouping Observations (Proc Sort)
• Producing Summary Reports
• PROC FREQ -(One Way & Two-Way Frequencies)
• PROC MEANS
• PROC REPORT
• PROC TABULATE
• PROC SUMMARY
• PROC PRINTO
• PROC APPEND
• PROC TRANSPOSE
• PROC COPY
• PROC COMPARE
• PROC DATASETS
• Regression Procedure
• Univariate / Multivariate Procedures
• Ranking Procedure
• Producing Bard and Pie Charts
• Producing Plots
• The Output Delivery System (SAS/ODS)
• Creating HTML Reports
• Creating Text Reports
• Creating PDF Reports
• Creating CSV Files

SAS MACRO LANGUAGE INTRODUCTION TO THE MACRO FACILITY PURPOSE OF THE MACRO FACILITY

• Generate SAS code using Macros (%Macro & %Mend)
• Tips on Writing Macro-Based Programs
• Replacing Text Strings using Macros Variables (%Let)

MACRO PROGRAMS

• MACRO PROGRAMS
• Defining a Macro (%Macro & %Mend )
• Macro Compilation
• Monitoring Macro Compilation (MCOMPILENOTE OPTION)
• Calling a Macro (%Macro-Name)
• Macro Execution
• Monitoring Macro Execution (MLOGIC OPTION)
• Viewing the generate SAS Code in the Log from Macro Program (MPRINT OPTION)
• Macro Storage
• Macro Parameters
• Macro Parameters Lists
• Macros with Positional Parameters
• Macros with Keyword Parameters
• Arithmetic and logical Operations
• Conditional Processing
• % IF expression % THEN text ; %ELSE %TEXT;
• % IF expression % THEN %DO; %END; %ELSE; %DO;
• Stored Compiled Macros
• %INCLUDE Statement

MACRO PROCESSING

• Tokens
• Macro Triggers
• How the Macroprocessor works

MACRO VARIABLES CONCEPTS

• Referencing a Macro Variable
• Displaying Macro Variable Value in the SAS log (SYMBOLGEN OPTION)
• Automatic Macro Variables
• System-Defined Macro Variables (_AUTOMATIC_)
• User-Defined Macro Variable (_USER_)
• Datatype
• %LET Statement
• Global Macro variables
• Local Macro Variables
• Deleting User-Defined Macro Variable (%SYMDEL)
• Macro Functions
• Character Strings
• Other SAS Functions
• %SYSFUNC
• %STR
• Combining Macro Variable References with Text
• Macro Variable Name Delimiter
• Quoting
• Creating Macro Variables in the Data Step (CALL SYMPUT ROUTINE) Obtaining Variable value during Macro Execution (SYMGET FUNCTION)Creating Macro Variables during PROC SQL Execution (INTO Clause)
• creating a delimited list of Values.

SAS SQL PROCESSING INTRODUCTION TO THE SQL PROCEDURE

• Terminology
• Features of PROC SQL
• PROC SQL Syntax (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
• VALIDATE Keyword
• NOEXEC Option
• Added PROC SQL Statements (ALTER, CREATE, DELETE, DESCRIBE, DROP)
• FEEDBACK OPTION
• PROC SQL and DATA Step Comparisons
• Queries
• Retrieving Data from a table
• Identify All Rows in a Table
• Remove Duplicate Rows
• Sub setting using WHERE clause
• Sub setting with Calculated Values
• Sub setting with Calculated Values
• Enhancing Query Output (LABEL, FORMAT)
• Grouping Data (Group By)
• Analyzing Groups of data (COUNT)
• Updating Data values (Update Statement)
• Using Table Alias
• Creating Views
• Creating Dropping Indexes
• Sub Queries
• Non-Correlated Sub Query
• Correlated Sub Query
• Combining Tables
• Joins
• Inner Joins
• Outer Joins
• Left Join
• Right Join
• Full Join

SET OPERATORS

• EXCEPT
• INTERSECT
• UNION
• Choosing between Data Step Merges and SQL Joins

UNIX

• Introduction to Unix
• Introduction to UNIX Architecture
• Understanding UNIX Commands
• Understanding ID/Groups/Permissions
• Introduction to Shell Scripting
• Writing UNIX Programs
• Understanding VI Editor
• Introduction to LSF
• Scheduling SAS Codes through UNIX