BA using SAS: 03 Basic Analytic Techniques

3.1 Basic Analytic Techniques using SAS and Excel - Part 1

Objective Slide

Get an introduction to SAS Studio
Learn how to upload a file to SAS
Know the advantages if using SAS
Understand data exploration through SAS and Excel
Implement distribution analysis throgh SAS and Excel
Learn the application of data visualization
Visualize data using SAS and Excel
Understand diagnostic analytics
Perform correlation, ANNOVA and t-tests through SAS and Excel
Understand the concepts with the help if case studies.

Introduction - SAS Studio

One of the most effective, interative tool for data exploration and analysis.

It is used for Exploring data
Transforming data
Analysing distribution
Fitting and evaluating models
Developing interactive programs
Creating custom analysis
Sharing analysis

Command used for uploading a file in SAS:

Use proc import procedure to import data file into working directory.
The file from the SAS Folder is taken into the Working directory by using the syntax;

data file = file destination

The command out = "file name" directs the file name to the work library which cab be viewed under the work folder of library.
Specify the type of file by using the syntax dbms=filetype
Syntax replace is used if any manipulation is to be done with the data
Run is used in executing the commands.

Demo - Load Data File

Let us now see a video to understand these commands better.

In this section we will now see:

How to upload a file
How to view a file and
The different syntax used for the same to upload a file into the SAS working directory.

Click on folder section, click upload and load the required file using the Choose File (browse) option.
After loading the file use proc import procedure to import data file into working directory.

proc import datafile="/folders/myfolder/sasuser.v94/sales_reg.csv"

out=Sales

dbms=csv

replace;

run;

Here in this case we have uploaded the data file into "/folders/SASuser.v94/" by the name sales_reg so we give the appropriate syntax as shown above.
Second command out=sales directs the sales file to work library. This can be viewed under the work folder of the library.
Specify the type of file by using the syntax "dbms=filetype"
Syntax replace is used if any manipulation is done with the data. For example using only one column from the data set.

The run icon is used to execute the commands.

To view and check whether the right data is loaded into the system we can use "proc print".

The syntax is "proc print data=filename"

proc print;

run;

Once you click on the run icon you can see the loaded file.

After loading the file into SAS library.

All the statistical functions can be performed on the data.

SAS also provides a wide range of sample data for learning data analysis.

During the learning phase you can work on these sample data to get a better understanding of the concepts that are covered in this course.

The steps to upload a file has not been added for excel. Since Excel does not require importing data.

In the next section we will look at the advantages of using excel for data exploration.

3.2 Data Exploration

Since data exploration was already covered in the previous slides we will cover how data exploration can be covered using other tools.

Data Exploration through SAS

Complete exploration of data is possible in very less time.
Simplified steps for data exploration
Quality of statistical analysis is better compared to other tools.
User Friendly
Time for debugging program errors significantly reduced and the training required to learn SAS is relatively less.
Macro approach - integration of graphical analysis tool; thereby quickly completing data analysis tasks without using SAS program code.
Limitations - Only the most commonly used data analysis methods available.

Steps for data exploration using SAS studio

The steps involved are:

Open SAS
Extract data into the SAS system from the disk
Click on Tasks
Click on summary statistics
Check whether we have the right data set under the field "DATA".

If not, then load appropriate data set.

Once the appropriate data set is loaded, pick the Analysis variables
In certain cases, we need to fill in an additional field named Classification Variables
Run the program.
The summary statistics will be displayed in the results window.

Now let us understand the flow and instructions better.

In this section we will look at how to explore data through SAS studio.
The first step is to upload the data set on which you want to perform the analysis.
You can upload a new data set as shown in the previous section or
You can use an existing sample data set within SAS Studio under the Library > SASHelp.

We will select a data set which is already available in SAS...the CARS data set and form data exploration.
To start with click on the Tasks tab under which you can see various functions which can be performed.
Click on summary statistics under Statistics. A new tab Summary Statistics is displayed.
And you will see the data section under the Summary Statistics.
Select the particular data set on which you are going to perform summary statistics.
The next step is to provide the Analysis variable.
Click the + button and the list of columns is displayed. In this case we are considering the column MSRP for analysis.
You can also specify the classification variable if required. We will classify cars based on the Make.

Now click the Options Tab
A list of Basic Statistics and Additional Statistics option is displayed.
Here few statistics are already selected. We will check on the Median under Basic Statisitics and Mode under Additional Statistics
You can look at the list of options and select them as necessary and the case that is being analyzed.
There are also options for Percentile Statistics, Plots etc. We will look at them at the later sections of this lesson.

The information tab provides the properties and resources. You can note that as you can make changes to data and options tab. The code window at the right keeps getting updated with the corresponding code.
Finally Click on the Run icon and the output will be displayed in the Results tab.

The result shows mean, standard deviation, minimum, maximum, median and mode for the analysis variable MSRP classified by the Make as seen in the first column.
You can also download the results as .RTF or .PDF file from the icons on the top left of the Results tab.

Steps for Data Exploration using Excel

Open the file
Click on the Office button and select Excel Options, and select Add-ins.
Select the Analysis ToolPak option from the list of options, and click on Go.
Place a check next to the Analysis ToolPak Add-in and click OK.
Click on Data ribbon from Menu. On the right most side, the Data Analysis tool options will be displayed.
Click on the Data Analysis button.
Select Descriptive Statistics from the options and click OK.
Select an input range for the data , by clicking the icon in Input Range field. Click and drag on the data to select the range.
Check on labels in first row option if data extracted has column name in its first row.
Check on Summary Statistics and click OK.
The results will be obtained in a new worksheet.

Let us know look at a demo to understand the steps better.

Let us take a students marks list as a data set to perform data exploration.

To perform any data analysis we need to load the data analysis "add-in" in Excel.
To load "add-in" click on "Office Button" (on the top left) and "Excel Options".

From the "Excel Options" dialog box, click on the "Add-Ins" option on the left.

The list of available "Add-ins" will be displayed. Select "Analysis ToolPak". Then select "Go..." button.

Now you will see the list of "Add-ins" available.

Check option "Analysis ToolPak" and click on Ok.

Go to the Data Option in the Menu Bar. Select the Data Analysis Tool.

Select 'Descriptive Statistics' from the window that is displayed and click OK.

Now you can specify your input variables in the Input Range by clicking and dragging on the required cells.

Here we are selecting Percentage 1 column as our input range. Check on "Labels in first row" options.
Specify your output range where your results are to be obtained. By default the results are displayed in the "New Worksheet Ply".
Check on "Summary Statistics". You can also change your "Confidence Level for Mean" if you want and click on Ok.

Results are obtained in a new worksheet. You can see that the statistics are displayed.
Mean, Median, Mode, Std. Dev., Variance, Kurtosis, etc.
It is easier to infer that the minimum of this variable is 12.5 and the maximum value is 80. Thus, the range is 67.5.
Count shows the number of instances i.e. 115. The average of the %ages are nearly 53.2.
Similarly other statistics can be used to get useful information from the data.

Steps for Distribution Analysis in SAS Studio

Open SAS
Extract data into the SAS system from the disk.
Click on the Tasks section
Click on Distribution Analysis
Define the appropriate variable as discussed in the previous slide.
Once this is done, click on Options
Check histogram, to view a graphical result and click on run.
The histogram results will be displayed in the results window.
Additionally, there are also other options for checking normality. That is, if required, you can perform a goodness of fit test, normal probability plot, etc.

Now let us look at a demo to under the steps better:

How to perform Distribution Analysis for given data through SAS.

First we will load the data set into SAS. (Following the steps mentioned into earlier videos)
To illustrate the distribution analysis we will look at the same car sample data set from the SASHELP library.
Once the file is loaded just select task "Distribution Analysis" from the Statistics section.
Fill in the required fields
In the Data Field select SASHELP.CARS
Select the Analysis Variable and the Classification Variable.
In our example we will analyse the Horsepower of the CAR.
For the classification variable we will again use the Make of the Car.

Click on the options tab.
Here check the Histogram Options to view the graphical output.

Other options are also available to check the normality assumptions, goodness of fit, etc.

Finally click on the Run Icon
You will see the histogram plot of the Horse Power in the resultant window.

In the next section we will cover data distribution in Excel.

Steps for performing Distribution Analysis in Excel.

Open the file for performing data for exploration.
Make sure that the Analysis ToolPak add-In is installed in Excel.
If not, install it by selecting Add-ins from the Office button and selecting Manage add-ins.
Click on data analysis from the right corner of data ribbon.
Select Histogram from the list of options and click on ok.
Select the input range for the data by clicking and dragging on the data.
Check on labels in first row option if data extracted has column name in its first row.
Check on chart output and click ok.
The result will be obtained in New Worksheet ply by default.

Let us now look at a demo to understand the steps better.

We will use the student mark sheet as an example to show how to perform distributions analysis.

For performing analysis operation for data analysis tool we need to install the data analysis "Add-ins"
Following the same steps which we discussed in the previous video they have the data and the analysis tool.
Click on the Data tab and you will see the Data analysis tool at the top right corner.
The data analysis window is displayed. Select the Histogram option to analyse the distribution of the data.
The Histogram Dialog is displayed.

Please select the Input range. In this example we will use the Percentage1 column.

If required you can also specify the Bin Range.

To Specify the bin range enter the values in a column in the same sheet. So create a column Bin with values 30, 40, 50, 60, 70, and 80.

Select this as bin values. Check on Labels if your first row specifies the column name.
Check Chart Output to give your result as chart.
Finally click on Ok.

The resultant worksheet ply defines the bins and frequency for particular input variable and a Histogram chart is also displayed.

From the resultant tab we can see that there are 2 percentages below 30, 5 percentages above 80 and similarly the frequencies for other bins are displayed.
It can also be seen that the maximum of marks fall within 50 to 70 range. With 80 values in this range.
Similar inferences can be made from Mark1, Mark2 and Percentage2 as well.

3.3 Data Visualization

Data Visualization

Data Visualization is the process of graphically or pictorially representing your information with qualitative understanding.

It makes interpretation very easier.

Things essential to make good visualization:

Effective format
Accurate result
Efficient visuals
Aesthetics
Adaptable form

In case of business organization it helps in decision making by

Identifying regions where improvement has to be done.
Influencing customer behavior and predicting sales volumes.
Helping in increasing revenues and decreasing dispenses.

Steps for Data Visulization in SAS Studi

Open SAS
Extract the required data into the SAS system from the disk.
Click on Tasks.
Click on the graph section and select the appropriate chart for your analysis.
Click on bar chart from the options tp make bar chart comparision between car make and its weight.
Confirm whether the right data set is loaded under the data option.
Select the appropriate category variable.
Select the appropriate response variable (Group Variable if required).
Click on Run.
The bar chart will be displayed in the result window.
Similarly, you can also perform various other chart like bar-line chart, line chart, pie chart and scatter plot etc.

Now let us look at a demo to understand the steps better.

We will try to plot the weight of cars on the basis of their make.
We will first load the particular data-set into SAS studio.
In this video we will take the same example of cars data set.
To perform data visualization in SAS click on the Tasks tab, and select bar chart from the graph section.
You can also visualize your data by selecting other graphs that suits your data like bar-line chart, line chart, and scatter plot etc.
Here we have chosen bar chart for R visualization.
In the data tab ensure that the CARS data is loaded into the data field.
Select the category variable by clicking the + icon.
Here for example we have chosen "Make" as our category variable.
Click on OK.
Next choose the response variable, in our example we will use "Weight" as our response variable.
You can also group your data by specifying group variable. We will leave this option blank for now.

Click on the Options tab.
Here you can specify whether your graph should be vertical or horizontal.
You can also add a title and a footnote to the bar chart.
Let us add the title as "Weight by Model".
You can also aggregate by mean or sum by Statistics section.
We will use the default option by aggregating by the average.

Other options are also available like Axis, legend details, category, graph size etc.

Here we will use the default options.

In the information tab the resources and explanation are being displayed.
Finally click on run.
The Bar chart is displayed.

Since the bar chart does not fit within the window let us open it in a new browser tab.using the right most icon on the top.
We can infer from the plot that the "Hummer" cars are the heaviest having an average over 6000 pounds.
Majority of cars are having average weight around 4000 pounds.
Similar plots can be created to analyse different variables.

Steps for Data Visualization in Excel

Open the file for performing data visualization.
Select the Input Range
Click on Insert from the menu bar
Click on the required graph icon from the Chart section.
The graph or chart will be displayed.
The chart tools ribbons are displayed with the design, layout and format sections.
To modify the legend or name the axes, select the layout section.
You can see the labels and axes sections in the ribbon. These ribbons can be used to modify the chart in any way.

Now let us look at the video to understand the concepts better:

Let us look at the steps to visualize data through excel.
As usual we will look at the student mark list example.

Select the variable for which you need to make the visualization.
For this example let us try to visualize the Mark1 variable.
Select the Mark1 column or click and drag on the cell.

After selecting the variable, click on the Insert Menu.
You can see many charts that help in visualizing the data.
In our example since it is a continuous variable, we will use the line chart option.

Click on the line icon and excel shows the different types of line charts.
Let us choose the line with the markers option.

On Selecting the option the chart is automatically displayed.
Let us resize the chart to make it more visually readable.

The x-axis denotes the entry and the y-axis denotes the marks obtained.
To know any particular data hover the mouse over the marker and the value is displayed.

For example let us find out the lowest value in the data.
When we hover the mouse over the lowest values it displays the value 4 at point 106.
As mentioned before the chart option can be changed from Design, Layout and Format ribbons which is available when we select the chart.

Case Study

Now we will take up the case studies to apply the techniques of data visualization and data exploration in real world problems so that we can understand the concepts better.

Let us now take a banking related data set as an example for doing data exploration.
Officer David from the sales department has been asked to do a study on the overall performance of a bank by analyzing the bank loans. As a part of the investigation, David has asked his colleague to categorize the loans as seasoned or bad loans with respect to Age category.
The following data categories have been captured:

Age Group
Total number of loans
Number of good loans
Number of bad loans
Percentage of good loans
Percentage of bad loans

Let us have a look at the data.

The table below shows the bank loan data

As mentioned before the number of good loans and bad loans has been tabulated as per the age category.
There are 15 categories of the age in the table.
Given the total number of loans and the number of good and bad loans, the percentage of good and bad loans has been calculated and tabulated.

Now we will look at the questions that David wants to answer:

Should bank loans be restricted to 40-45 age category?
There are any relationship between loans and age?
Is there any pattern for defaulters?
How is the bank performing on the whole?

Now we will look at how to approach these questions with the help of SAS and Excel.

In the following demo we will look at different statistics and graphical representation of the data that will help finding the answers.
We will start visualizing the data to get meaningful insights.
As shown earlier the steps the visualize data are
Open SAS
Extract data into the SAS system from the disk.
Click on Tasks
Click on Graphs and you will see various charts for visualization.
Select appropriate chart for the analysis.
Here we will use Histogram to plot number of good loans and number of bad loans.
Select Histogram from the Graph section of the Tasks.
Confirm whether the right data is loaded under the data in Data tab.
Select the Analysis variable i.e. "Number of Good Loans"
Run the code.

The histogram is displayed in the results window

Change the Analysis variable from "Number of Good Loans" to "Number of Bad Loans" and run the code
A new histogram is displayed for the number of bad loans.

Data Exploration using SAS - Descriptive Analysis

To get the better understanding of the data we use data exploration. i.e. the Descriptive Analysis.
Thus we get the numerical summaries of the data.
Let us try to obtain descriptive statistics through SAS.
Open SAS
Extract Data into the SAS from the disk.
Click on Tasks
Click on Summary Statistics
To do the basic data exploration.
Check whether we have the right data set under the field data.
Select the Analysis Variables as

Number of Good Loans
Number of Bad Loans

Select the Classification Variable as "Age Group".
Run the program.
The Summary Statistics are displayed in the result window.

From these results we obtain the following inferences
Number of Good Loans in comparatively greater than the Number of Bad Loans. And hence we can conclude that the bank is performing well in this segment.
Since the percentage of good loans is high in the 42 to 45 years Age_Group category, we can conclude that it is worth taking a risk in the category.
However, there are other categories, that have better percentage of good loans, and hence it is preferable not to concentrate on 42 to 45 years Age_Group category.
We will try to answer the remaining questions through Excel.

Data Exploration and Visualization using Excel - Descriptive Analysis

Open the file in Excel for Data Visualization.
Select the Input range.
Click on Insert
Select the required graph.
The graph will then be displayed
Let us start creating graphs
First we create a graph of "Number of Bad Loans" v/s the Age_Category.
Select the Age_Category and "Number of Bad Loans" column.
Click on the Insert Menu and select any Bar Graph.

The resulting Bar Graph shows the bar of "Number of Bad Loans" and the function of the Age_Category on the X-axis.

Here the first thing that comes to our notice that the maximum number of bad loans is in the 42-45 Age_Category.
However, it does not necessarily mean that the loans should not be given to this category as maximum profit also falls under the same category.
Another trend that can be noticed from this graph is that the defaulters are fewer as the borrower gets older. That is a good insight.
It supports that fact that there is a definite relationship between age and loan patterns.
Let us now create a graph of total number of loans v/s the age group.
Select the two columns and click on the insert menu.
Let us create the line chart.

From the chart we can infer that the distribution of loans across age group is reasonably smooth normally distributed curve without too many outliers.
In most business cases age often shows this kind of interesting patterns for most products. However, other closely related businesses cannot produce similar smooth curves. In such cases we might have to smoother the curve.
Adding to the above insights it is very difficult to draw results form the end values. But neglecting the end values could be fatal if it is not based on sound business acumen.Often neglecting the end point is a common mistake as far as analysis is concerned.

Summary Statistics using Excel

To obtain summary statistics in Excel, open the file.
Make sure that the data analysis tool pack is installed.
Click on Data Analysis from the Data Menu.
Click on Descriptive Statistics and click Ok.
Select the Input range from the data. In this case select the whole data set.
Check on "Labels in the First Row" options, since the data extracted has the column names in its first row.
Check on "Summary Statistics" and click Ok.
The results are obtained in a new worksheet.
The results show different statistics for every variable of the data set.
For the questions if the bank is performing well on the whole let us look at the mean of the number of good loans and the number of bad loans.
There are an average of 3952 good loans and about a 100 bad loans. So we can confidently say bank is doing well in the overall sense.

BA using SAS

Sunday, May 8, 2016

03 Basic Analytic Techniques - Part 1