First, you should download and familiarize yourself with the nature of the assignment. Next, you should upload the “Real Estate” database I have provided to you in this week’s module into SAS Studio.
The assignment is to perform some functions in Excel where you will produce a series of Scatterplots and also perform a Regression Analysis on the Real Estate database using a specific set of variables I provide. In Excel, you will need to add the “Add In” for “Data Analysis”. If you have not previously done this, here are the steps:
Go to the “File” tab. Select “Options” (near the bottom). Select “Add-ins” (also near the bottom). At the bottom (by “Manage”) click “Go”. Check “Analysis ToolPak” and “Analysis ToolPak – VBA”. Click “OK”
This will add “Data Analysis” to your “Data” tab in Excel. Then you can click on “Data Analysis” and perform the necessary Regression Analysis.
Then, you will go into SAS and perform a Regression Analysis on the Real Estate Database you previously uploaded into SAS. One critical thing to remember – you must correctly categorize your data types. Nominal data goes into the “Classification” variables when building your model, and all numeric data types going into “Continuous”.
You will need to print or edit the Word document provided and then save/scan it to submit in Canvas, along with the Excel spreadsheet and the SAS Output.
Clarification for Excel file upload into SAS
Based on common questions from students, there is always a little confusion on uploading the Excel file (Real Estate – Base.xlsx) into SAS. So, please let me clarify.
You should upload the pure, base file into SAS – NOT the one that you manipulate to make the Scatterplots and requested Excel Regression model(s). The point of this exercise is for you to see the difference between how you create Regression models in Excel and SAS, so we definitely want to start with the same database. In order to do that, you need to load the base file in SAS in order to set up your data for the Regression.
Helpful hints regarding the regression output from Excel
For the SAS (Critical Thinking project, I always want to add clarity regarding the output from Excel Regression (see below). The Output gives you what you need to complete the Critical Thinking Project form for the Excel part. You will see below that I highlighted “R” (called Multiple R here and Correlation Coefficient in my slides) in Bold. R2 is highlighted in Italic (that is the Coefficient of Determination). Remember, if you multiply it by 100, you get the percentage of the change in the Dependent Variable that is explained by the Independent Variable(s) – in this case 92.6%.
Finally, you need to know how to build your Regression model from the output below. The formula coefficients are highlighted below in Underline. This is the output from the Nodel Construction example you find in the PowerPoint slides for Chapter 4. If Sales is “Y,” Payroll is X1, and Interest Rate is X2, then your Regression model would read as follows:
Y = 5.214 – 0.017(X1) – 30.155(X2)
|Adjusted R Square||0.875966495|
If you have properly completed your SAS project you will upload the following three items:
1. The DOCX file with the original assignment and rubric (all fields completed).
2. The XLSX file you downloaded with the addition of the tab with the Scatterplots on it and the Regression Output tab for the regression of Price with the three independent variables.
3. A PDF file you produce from SAS that shows the output of your final regression (with a higher R2 than we had with the original model)