Getting to know SAS A Data Warehouse Mining Tool Stephen Turner 5756281 Chris Frala 5756286 1 There and Back Againa SAS Tale WARNING There are A LOT of slides but they are stepbystep examples ID: 763838
Download Presentation The PPT/PDF document "Getting to know SAS A Data Warehouse Min..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Getting to know SAS A Data Warehouse Mining ToolStephen Turner 575-6281 Chris Frala 575-6286 1
There and Back Again…a SAS Tale WARNING: There are A LOT of slides….but they are step-by-step examplesLearn how to gain accessTake a look around Build some queries and then learn how to manipulate them to serve us better!2
There and Back Again…a SAS Tale Building some queriesSummary of Cost Centers for a BUManipulating existing queriesSummary of Cost Centers for a PI Details of Payroll for a BUAdding filter for a Time RangeDetails of Payroll for an IndividualAdding a Sort featureOutstanding Invoices for a BU3
Do I already have SAS on my computer? Icon looks like thisIf you don’t have the icon or you do or it is not active, then here is how to gain access. 4
How to gain access Via Information Technology Services website http://its.uark.edu/ 5
How to gain access 6
How to gain access http://its.uark.edu/Data_Warehouse_Account_Request_Form.pdf 7
Let’s take a look around 8
Let’s take a look around 9
Let’s take a look around 10
Let’s take a look around 11
Let’s take a look around 12
Now, for some fun 13
Summary of Cost Centers for a BU 14
Summary of Cost Centers for a BU 15
Summary of Cost Centers for a BU Now that we have two tables, we need to combine themRight click on one them and select “Query Builder”Then in the command bar, select “Join Tables”And then “Add Tables” 16
Summary of Cost Centers for a BU This is the default of joining two tables and what is used 99% of the time 17
Details of Payroll for a Cost Center This is the default of joining two tables and what is used 99% of the time 18
Summary of Cost Centers for a BU 19
Summary of Cost Centers for a BU 20
Summary of Cost Centers for a BU 21
Summary of Cost Centers for a BU 22
Summary of Cost Centers for a BU 23
Summary of Cost Centers for a BU 24
Summary of Cost Centers for a BU This is great, but what if I wanted to pull in the Category? 25
Summary of Cost Centers for a BU part 2 26
Summary of Cost Centers for a BU part 2 27
Summary of Cost Centers for a BU part 2 28
Summary of Cost Centers for a BU part 2 29
Summary of Cost Centers for a BU part 2 Let’s save what we have done first 30
Summary of Cost Centers for a PIWhat if, we need to know it by PI instead? 31
Summary of Cost Centers for a PI 32
Summary of Cost Centers for a PI 33
Summary of Cost Centers for a PI 34
Summary of Cost Centers for a PI 35
Summary of Cost Centers for a PI 36
Summary of Cost Centers for a PI 37
Summary of Cost Centers for a PI 38
Details of Payroll for Multiple BUs 39
Details of Payroll for Multiple BUs 40
Details of Payroll for Multiple BUs If I have not been to a Table in a while, I usually just Double Click the table. 41
Details of Payroll for Multiple BUs Now I look for things I can use. For this example we will use BU, CCN, Pay Amount, Benefits 42
Details of Payroll for Multiple BUs Now right click on “LABOR_CURRENT” and select “Query Builder” 43
Details of Payroll for Multiple BUs Add a Filter 44
Details of Payroll for Multiple BUs Selecting multiple BUs 45
Details of Payroll for Multiple BUs 46
Details of Payroll for Multiple BUs 47
Details of Payroll for Multiple BUs by Time Range But I wanted to know WHO was paid on a given time period.Right click on the Query Builder 48
Details of Payroll for Multiple BUs by Time RangeAdding a new field 49
Details of Payroll for Multiple BUs by Time Range Adding a new Filter 50
Details of Payroll for Multiple BUs by Time Range Now let’s filter by a “range” 51
Details of Payroll for Multiple BUs by Time Range 52
Details of Payroll for Multiple BUs by Time Range For this example, we will look all the payments for the month of July 2012 53
Details of Payroll for Multiple BUs by Time Range Remember, we can “Send To” excel 54
Details of Payroll for Multiple BUs by Time Range To save, go to “File” and “Save As” 55
Get Up and Stretch Time? 56
Payroll for an Individual 57
Payroll for an Individual Now right click on “LABOR_CURRENT” and select “Query Builder” 58
Payroll for an Individual Adding a filter 59
Payroll for an Individual 60
Payroll for an Individual 61
Payroll for an Individual 62
Payroll for an Individual Adding a “Sort” 63
Payroll for an Individual Ascending or Descending 64
Payroll for an Individual 65
Outstanding Invoices Combining multiple tables (5) into one query66
Outstanding Invoices 67
Outstanding Invoices 68
Outstanding Invoices 69
Outstanding Invoices Right click on the top table and select “Query Builder” 70
Outstanding Invoices 71
Outstanding Invoices When joining these, we sometimes see the error message below.Usually because column names that are not actually the same. 72
Outstanding Invoices 73
Outstanding Invoices 74
Outstanding Invoices 75
Outstanding Invoices When combining multiple tables, I usually RUN the query before doing a Filter. 76
Outstanding Invoices Unfiltered 186,194,858 lines 77
Outstanding Invoices 78
Outstanding Invoices 79
Outstanding Invoices 80
Outstanding Invoices 81
Outstanding Invoices 82
Outstanding Invoices 83
Outstanding Invoices 84
Outstanding Invoices 85
....and breathe A lot of informationHopefully a lot of good/useful informationThere is a TON of information out there, so don’t get afraid to get dirty playing in the data 86