Prof. MS EXCEL

  Adv. MS Access (RDBMS)

Understanding Databases 

• Starting and Opening an Existing Database
• Moving Around in Access
• Understanding Datasheet View & Design View
• Using the Mouse Pointer to Navigate
• Using the Keyboard to Navigate
•  

Creating Tables

• Creating a Database
• Creating a Table Using the Wizard
• Creating and Modifying a Table
• Adding Fields to Tables
• Adding and Editing Records
• Printing Tables
• Moving and Deleting Fields
• Deleting Records
•  

Working with Tables

• Formatting a Table
• Modifying Field Properties
• Sorting Records in a Table
• Finding Records in a Table
• Using Filters with a Table
• Establishing Relationships Between Tables
• Creating Subdatasheets
• Importing Records From an External Source
•  

Designing a Form

• Creating a Form Using Auto Form
• Creating a Form Using the Form Wizard
• Adding Controls to a Form
• Modifying Control Properties
• Resizing and Moving Controls
• Entering Records into a Form
• Creating Calculated Controls
•  

Designing a Report

• Creating a Report Using Auto Report
• Creating a Report Using Report Wizard
• Adding a Control to a Report
• Formatting a Report
• Resizing and Moving Controls
• Creating Calculated Controls
•  

Creating and Using Queries

• Creating and Running a Query
• Specifying Criteria in a Query
• Using Comparison Operators
• Creating a Calculated Field
• Creating a Multiple-Table Query
• Printing a Query

Automating Tasks

• Creating an Auto Keys Macro
• Using Controls to Run a Macro
• Assigning a Macro to an Event
• Assigning a Macro to a Condition
• Testing and Debugging a Macro

 SQL

SQL ─ Overview

• What is SQL?
• SQL Process
• SQL Commands
•  

SQL – Operators

• What is an Operator in SQL?
• SQL Arithmetic Operators
• Arithmetic Operators – Examples
• SQL Comparison Operators
• Operators – Examples
• SQL Logical Operators
• Logical Operators – Examples

SQL – Expressions

Boolean Expressions

Numeric Expressions

Date Expressions

SQL – CREATE Database

• SQL ─ DROP or DELETE Database
• SQL ─ SELECT Database, USE Statement
• SQL ─ CREATE Table
• SQL - Creating a Table from an Existing Table
• SQL ─ DROP or DELETE Table
• SQL ─ INSERT Query
• SQL ─ SELECT Query
• SQL ─ WHERE Clause
• SQL ─ AND & OR Conjunctive Operators
• The AND Operator
• The OR Operator
• SQL ─ UPDATE Query
• SQL ─ DELETE Query.
• SQL ─ LIKE Clause.
• SQL ─ TOP, LIMIT or ROWNUM Clause
• SQL ─ ORDER BY Clause
• SQL ─ Group By
• SQL ─ Distinct Keyword
• SQL ─ SORTING Results
•  

SQL ─ Constraints

• SQL ─ Constraints
• SQL - NOT NULL Constraint
• SQL - DEFAULT Constraint
• SQL - UNIQUE Constraint
• SQL ─ Primary Key
• SQL ─ Foreign Key
• SQL ─ CHECK Constraint
• SQL ─ INDEX Constraint
• Dropping Constraints
• Integrity Constraints
•  

SQL ─ Using Joins

• JOIN
• SQL ─ LEFT JOIN
• SQL - RIGHT JOIN
• SQL ─ FULL JOIN
• SQL ─ SELF JOIN
• SQL ─ CARTESIAN or CROSS JOIN
•  

SQL ─ UNIONS CLAUSE

• The UNION ALL Clause
• SQL ─ INTERSECT Clause
• SQL ─ EXCEPT Clause
•  

SQL ─ NULL Values

SQL ─ Alias Syntax

SQL – Indexes

• The CREATE INDEX Command
• The DROP INDEX Command
• SQL - INDEX Constraint
•  

SQL ─ ALTER TABLE Command

SQL - TRUNCATE TABLE Command

SQL ─ Using Views

• Creating Views
• The WITH CHECK OPTIONSQL ─ Having Clause
•  

SQL – Transactions

• Properties of Transactions
• Transactional Control Commands
•  

SQL ─ Wildcard Operators

SQL ─ Date Functions

SQL ─ Temporary Tables

• What are Temporary Tables?
• Dropping Temporary Tables
•  

SQL – Clone Tables

SQL – Sub Queries

• Subqueries with the SELECT Statement
• Subqueries with the INSERT Statement
• Subqueries with the UPDATE Statement
• Subqueries with the DELETE Statement
•  

SQL – Using Sequences

• Using AUTO_INCREMENT column
• Obtain AUTO_INCREMENT Values

 VBA MACROS

What Is Vba & How Vba Works With Excel

• Quick Review of Macros
• Introducing the Visual Basic Editor
• Uses of record Macros
• Understanding and creating modules
•  

Programming Concepts

• VBA Sub and Function Procedures
• How to create a message box
• Write a Program to update and retrieve information using Input Box
• Understanding and using Select Case statement
• How do I define a variables and Rules for defining a Variables Name and Type
• Creating And using Variables
• Working with range Objects
• How to save and Protect Modules
•  

Decision Makers

• If……Then……Else
• If……Then……Else If……If
•  

Other Kinds of Loops

• Working with Do While u. Loop Procedure
• Do…… Until Loop and Do…… Loop Until
• Do……While loop and Do…… Loop While
• For each…… Next
• For……Next
•  

Workbook Objects Create or Add Single and Multiple Workbooks

• Workbook Save and Save AS
• Open Single and Multiple Workbooks
• Close Specify and Multiple Workbooks
• Activate From one workbook to another Workbook
• Open Workbook from Specific Path
• Get Workbook Name and Paths
• Hide and Unhide for Single and Multiple Workbooks
• Protect and Unprotect Worksheets
•  

Worksheet Objects insert a single and Multiple Worksheets

• Delete Specific and multiple worksheets
• Get Count of Worksheets
• Select a Specific and all Worksheets
• Get All Worksheets Name
• Hide and Unhide For Single and Multiple Worksheets
• Rename for Single and Multiple Worksheets
• Protect and unprotect worksheets
• Sort and Move worksheets
• Calculate entire worksheet
• Using VBA and worksheet Function

Cell objects insert Single and multiple Row, Column and Cells

• Delete Single and Multiple rows, Column and Cells
• Get Range or Address of Cell and Selection
• Navigate from one Cell to another Cell
• Select specific Range, Cell, Rows and Column
• Types of Selection and Offset method
• Insert Function In cell
•  

Reading and Writing Arrays

• Defining Arrays
• Arrays as Outputs
• Arrays as inputs
• The Arrays as a Collection
• The Array as an Array
•  

Form Controls and User Forms (Create and Design an user Form)

• Working with User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.
• How to Create Dynamic Dashboard on User Form with Different Controls
• How to Link Various User Form With Each Other To Create A Complete Interface Between User and System
•  

VBA Programming Functions

• Create a Sum Functions
• Create Multiply Function
• Create Count Function
• Extract Text & Number
• Proper Function
• Vlookup Function
• Square root Function
• Public or Private function
•  

Excel VBA Power Programming for VBA Macros

• Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening &saving Files
• How to Analyze Data On multi Worksheets and Build Summary sheets
• how to Access the Windows File and Folder System to Open and Close Workbooks
• How to protect your code Against Errors
• How to use Excel And VBA to Create Basic Dash Boards
• How to create Your own custom Business Worksheet Function in VBA
• How to create Basic Report Generation Tools Using Excel Vba, Microsoft Word and PowerPoint
• How to use the Excel Visual Basic Macro record Excel Tasks in VBA and then Interpret the code
•  

Connection between Excel VBA & other platforms

• How to Establish Connection Between VBA And Power Presentation to Create Power Point Through VBA
• How to Establish Connection Between Excel Vba And Outlooks Through Vba
• How to Establish Connection Between Excel Vba And MS Word Through Vba

 SAS BASE & ADVANCE

MODULE 1

INTRODUCTION TO SAS

• Introduction
• Need for SAS
• Who uses SAS?
• What is SAS?
• Overview of base SAS software
• Data management facility
• Structure of SAS dataset
• SAS program
• Programming language
• Elements of the SAS language
• Rules for SAS statements
• Rules for most SAS names
• Special rules for variable names
• Types of variables
• Data analysis and reporting utilities
• Traditional output
• Ways to run SAS programs
• SAS windowing environment
• Noninteractive mode
• Interactive line mode
• Running programs in the SAS windowing environment

MODULE 2

HOW SAS WORKS

• Writing your first SAS program
• A simple program to read raw data and produce a report
• More on comment statements
• Internal processing in SAS
• How SAS works
• The compilation phase
• Inthe execution phasetroduction
• Processing a data step: a walkthrough
• Creating the input buffer and the program data vector
• Writing an observation to the SAS data set
• Four types of SAS libraries
• SAS libraries, work library
• SAShelp library, SASuser library

MODULE 3

READING RAW DATA INTO SAS

• What is raw data
• Definitions
• Data values
• Numeric value
• Character value
• Standard data
• Nonstandard data
• Numeric data
• Character data
• Choosing an input style
• List input
• Modified list input
• Column input
• Formatted input
• Named input
• Instream data
• Creating multiple records from single input row
• Reading data from external files
• Reading blank separated values (list or free form data):
• Reading raw data separated by commas (.csv files):
• Reading in raw data separated by tabs (.txt files):
• Using informats with list input
• Supplying an informat statement with list input
• Using list input with embedded delimiters
• Reading raw data that are aligned in columns:
• Method 1: column input
• Method 2: formatted input
• Using more than one input statement: the single trailing @
• Reading column data that is on more than one line
• Mixed-style input:
• Infile options for special situations
• Flowover
• Missover
• Truncover
• Pad
• Using lrecl to read very long lines of raw data
• Checking your data after it has been read into SAS

MODULE 4

READING DATA FROM A DATASET

• Introduction
• Set statement overview
• Automatic variables in SAS
• Interleave multiple SAS data sets
• Combine multiple SAS data sets
• Creating & modifying variables
• Creating multiple datasets in a single data-step
• Subsetting observations
• Conditional SAS statements
• Logical and special operators
• The SAS supervisor and the set statement
• Efficiency and the set statement
• Know your data
• Set statement data set options
• Drop and keep options
• Rename option
• Firstobs and obs options
• In option
• Where option
• Other set statement options
• End option
• Key option
• Nobs option
• Point option
• Do loops and the set statement
• Introduction to retain statement
• Carry over values from one observation to another
• Compare values across observations
• Assign initial values
• Determining column order in output dataset
• SAS system options

MODULE 5

READING DATA FROM A DATASET

• Input SAS data set for example
• Selecting observations for a new SAS data set
• Deleting observations based on a condition
• Accepting observations based on a condition
• Comparing the delete and Subsetting if statements
• Methods of creating new data sets with a subset
• Subsetting records from an external file with a Subsetting if statement
• Subsetting observations in a data step with a where statement
• Subsetting observations in a proc step with a where statement
• Subsetting observations in proc SQL
• Difference between if and where

MODULE 6

SAS INFORMATS AND FORMATS

• Overview
• Using SAS informats
• Input statement
• Input function
• Inputn and inputc functions
• Attrib and informat statements
• Using SAS formats
• Format statement in procedures
• Put statement
• Put function
• Putn and putc functions
• Bestw. Format
• Additional comments

MODULE 7

SAS FUNCTIONS

• Categories of functions
• SAS character functions
• Functions that change the case of characters
• Upcase
• Lowcase
• Propcase
• Functions that remove characters from strings
• Function: compbl
• Function: compress
• Functions that search for characters
• Function: anyalnum
• Function: anyalpha
• Function: anydigit
• Function: anypunct
• Function: anyspace
• Function: notalnum
• Function: notalpha
• Function: notdigit
• Function: notupper
• Functions that search strings
• Find and findc
• Index, indexc, and indexw
• Functions to verify data
• Function verify
• Functions that extract parts of strings
• Function: substr (on the left-hand side of the equal sign)
• Function: substrn
• Functions that join two or more strings together
• Function: cat
• Function: cats
• Function: catt
• Function: catx
• Functions that remove blanks from strings
• Function: left
• Function: right
• Function: trim
• Function: trimn
• Function: strip
• Functions that compare strings
• Function: compare
• Functions that divide strings into "words"
• Function: scan
• Function: scanq
• Functions that substitute letters or words in strings
• Function: translate
• Function: tranwrd
• Functions that compute the length of strings
• Function: length
• Function: lengthc
• Function: lengthm
• Function: lengthn
• Functions that count the number of letters or substrings in a string
• Function: count
• Function: countc
• Miscellaneous string functions
• Function: missing
• Function: repeat
• Function: reverse
• SAS date and time functions
• Introduction
• What is a SAS date and time literal?
• Date and time functions
• Functions to create date and time values
• Functions to takie datetime values apart
• Functions to get quarter, year or day of the date
• Functions that work with intervals
• Using formats for date and time
• System options fordate and time

MODULE 8

AN INTRODUCTION TO ARRAYS AND ARRAY PROCESSING

• Why do we need arrays?
• Basic array concepts
• Array statement
• Array references
• Variable name array reference
• Using array indexes
• One-dimension arrays
• Multi-dimension arrays
• Temporary arrays
• Sorting arrays
• Determining array bounds: lbound and hbound functions
• When to use arrays
• Common errors and misunderstandings
• Invalid index range
• Function name as an array name
• Array referenced in multiple data steps, but defined in only one

MODULE 9

BY - GROUP PROCESSING

• Definitions for by-group processing
• By-group processing
• By value
• By group
• First. Variable and last. Variable
• Modifying SAS data sets: examples.
• Invoking by-group processing
• Preprocessing input data for by-group processing
• Sorting observations for by-group processing
• Indexing for by-group processing
• How the data step identifies by groups
• Processing observations in a by group
• How SAS determines first. Variable and last. Variable
• Processing by-groups in the data step
• Overview
• Processing by-groups conditionally
• Data not in alphabetic or numeric order
• Data grouped by formatted values

MODULE 10

OVERVIEW OF METHODS FOR COMBINING SAS DATA SETS

• Definitions
• Concatenating
• Interleaving
• One-to- one reading or one-to-one merging
• Match-merging
• Updating
• Modifying
• Definitions for reading, combining, and modifying SAS data sets
• Reading a SAS data set
• Combining SAS data sets
• Modifying SAS data sets
• Overview of tools
• Reading SAS data sets
• Reading a single SAS data set
• Reading from multiple SAS data sets
• Combining SAS data sets: basic concepts
• One-to-one
• One-to-many and many-to-one
• Many-to-many
• Access methods: sequential versus direct
• Sequential access
• Direct access
• One-to-one reading
• Data step processing during a one-to-one reading
• One-to-one merging
• Match-merging
• Updating with the update and the modify statements:
• Definitions
• Syntax of the update statement
• Syntax of the modify statement -
• Updating with nonmatched observations, missing values, and new variables -
• Using an index with the modify statement

MODULE 11

SAS PROCEDURES

• Introduction
• The anatomy of a proc
• The proc statement
• Title and footnote statements
• By statement
• Label statement
• Format statement
• Run or quit statement
• Description of data used in reports
• SAS reporting procedures
• Procs for all that detail
• Using proc print
• Using proc sql, proc report
• Procs that summarize
• Using proc chart, using proc freq
• Using proc means
• Using proc univariate
• Introduction to proc tabulate
• Data manipulation and management procedure
• Proc sort, proc datasets
• Proc format, proc contents
• Other important procs
• Proc transpose, definitions
• Proc printto, compare procedure
• Proc append, how to import an excel file into SAS

MODULE 12

INTRODUCTION TO PROC SQL

• Introduction
• Why learn proc sql?
• Select statement
• The select statement syntax
• A simple proc sql
• A complex proc sql
• Limiting information on the select
• Creating new variables
• The calculated option on the select
• Using labels and formats
• The case expression on the select
• Additional select statement clauses
• Remerging
• Remerging for totals
• Calculating percentage
• Sorting the data in proc sql
• Sort on new column
• Subsetting using the where
• Incorrect where clause
• Where on computed column
• Selection on group column
• Use having clause
• Creating new tables
• Joining datasets using proc sql
• Inner join
• Joining three or more tables
• Outer joins
• Including nonmatching rows with the right outer join
• Selecting all rows with the full outer join
• Concatenating query results

MODULE 13

AN INTRODUCTION TO SAS MACROS

• Introduction
• SAS macro overview
• Traditional SAS programming
• The SAS macro language
• Macro language components
• Macro variables
• Macro statements
• Macro processor flow
• Automatic macro variables
• Macro debugging options
• What is a macro?
• Defining and using macros
• Positional macro parameters
• Keyword macro parameters
• Conditional macro compilation
• The %do statement
• SAS data step interfaces

MODULE 14

THE OUTPUT DELIVERY SYSTEM (ODS)

• Introduction
• Creating various types of reports listing output
• Other destinations
• Html
• Pdf and postscript
• Rtf files
• Microsoft Excel
• Adding style to your reports
• Locate existing styles
• Ods style= option
• Customize your reports
• Ods select; and ods exclude;
• Other customizations
• Ods proclabel=;
• Ods proctitle; and ods noproctitle;
• Advanced techniques
• Ods document
• Proc template

MODULE 15

INTRODUCTION TO DIAGNOSING AND AVOIDING ERRORS

• Introduction
• Understanding how the SAS supervisor checks a job
• Understanding how SAS processes errors
• Distinguishing types of errors. SAS recognizes four kinds of errors:
• Syntax errors
• Execution-time errors
• Data errors
• Semantic errors
• Diagnosing errors
• Diagnosing syntax errors
• Diagnosing data errors
• Using a quality control checklist

MODULE 16

ADVANCE TOPICS IN SAS

• Performing advanced queries using proc sql
• Introducing macro variables
• Creating and using macro programs
• Storing macro programs
• Creating samples and indexes
• Using lookup tables to match data
• Modifying SAS data sets and tracking changes
• Introduction to efficient SAS programming

 Tableau

Module 1- Introduction to Tableau

• Connecting Tableau to a Data file . CSV file
• Navigating tableau
• Creating calculated fields
• Adding colors
• Adding labels and formatting
• Exporting your worksheets

Module 2- Time series, aggregation, and filters

• Working with data extracts in tableau
• Working with time series
• Understanding aggregation and level of details
• Adding a filter and quick filter
• Time series , aggregation and filters

Module 3- Maps , scatterlpots and you first dashboard

• Joining data in tableau
• Creating a map working with hierarchies
• Creating a scatter plot, applying filters to multiple worksheets
• Let’s create our first dashboard
• Adding an interactive action- filter
• Adding an interactive action- highlighting
• Maps- scatterplots and your first dashboard

Module 4- Joining and bending data, PLUS Dual Axis Charts

• Understanding how LEFT, Right, Inner and outer join work
• Joins with duplicate values
• Joining on multiple fields
• Data blending in tableau
• The showdown joining data vs blending data in tableau
• Dual axis chart
• Creating calculated fields in a blend
• Joining and blending data PLUS dual axis charts

Module 5- Table calculation , advanced dashboards , storytelling

• Connecting datasets with tableau
• Mapping how to set geographic roles
• Creating table calculation for gender
• Creating bins and distributions for age
• Leveraging the power of PowerPoint
• How to create a tree map chart
• Creating a customer segmentation dashboard
• Advanced dashboard interactivity and coloring
• Analyzing the customer see mentation dashboard
• Creating a storyline
• Table calculations, advanced dashboards storytelling

Module 6- Advanced data prepration

• What format your data should be in
• Data interpreter – new Tableau 9 feature
• Pivot – new tableau 9 feature
• Splitting a column into multiple columns – new tableau 9 feature
• Metadata grid – new tableau 9 feature
• Fixing geographical data error in tableau
• Advanced data preparation
• The challenge startup expansion analytics
• Custom territories via groups
• Custom territories via geographic  roles
• Adding a highlighter
• Clustering in tableau 10
• Cross -database joins
• Modeling with clusters
• Saving your clusters
• New design features
• New design features
• New mobite features