Chapter 1 Getting Started 1 1 Course Logistics

Chapter 1: Getting Started 1. 1 Course Logistics 1. 2 Introducing the Structured Query Language 1. 3 Introducing the Business Scenario 1

Chapter 1: Getting Started 1. 1 Course Logistics 1. 2 Introducing the Structured Query Language 1. 3 Introducing the Business Scenario 2

Objectives n n 3 Explain the naming convention that is used for the course files. Compare three levels of exercises that are used in the course. Describe at a high level how data is used and stored at Orion Star Sports & Outdoors. Navigate to the SAS Help facility.

Filename Conventions s 104 d 01 x course ID Code 4 Type a Activity d Demo e Exercise s Solution chapter # type item # placeholder s 104 a 01 Example: The SAS® SQL 1: s 104 a 02 Essentials course ID is s 1, so s 104 a 02 s s 104 d 01= s 104 d 01 SQL Chapter 4, s 104 d 02 Demo 1. s 104 e 01 s 104 e 02 s 104 s 01 s 104 s 02

Three Levels of Exercises Level 1 The exercise mimics an example presented in the section. Level 2 Less information and guidance are provided in the exercise instructions. Level 3 Only the task you are to perform or the results to be obtained are provided. Typically, you will need to use the Help facility. You are not expected to complete all of the exercises in the time allotted. Choose the exercise or exercises that are at the level you are most comfortable with. 5

Orion Star Sports & Outdoors is a fictitious global sports and outdoors retailer with traditional stores, an online store, and a large catalog business. The corporate headquarters is located in the United States with offices and stores in many countries throughout the world. Orion Star has about 1, 000 employees and 90, 000 customers, processes approximately 150, 000 orders annually, and purchases products from 64 suppliers. 6

Orion Star Data As is the case with most organizations, Orion Star has a large amount of data about its customers, suppliers, products, and employees. Much of this information is stored in transactional systems in various formats. Using applications and processes such as SAS Data Integration Studio, this transactional information was extracted, transformed, and loaded into a data warehouse. Data marts were created to meet the needs of specific departments such as Marketing. 7

The SAS Help Facility 8

The SAS Help Facility n Invoke the SAS Help facility by doing one of the following actions: – Type Help on the command line. n 9 – Select Help from the menu. – Select the Help button on the toolbar. Additional help and documentation are available at www. support. sas. com/documentation.

The SAS Help Facility 10

11

Setup for the Poll n n 12 Start your SAS session. Open the SAS Help facility.

1. 01 Poll Were you able to open the Help facility in your SAS session? Yes No 13

1. 02 Multiple Choice Poll Which choice best describes your programming and SQL experience level? a. b. c. d. e. 14 I have little or no programming experience. I can write programs in languages other than SQL. I can write database-specific SQL programs. I can write SAS PROC SQL programs. I can program in multiple languages, including SQL.

1. 03 Multiple Choice Poll What version of SAS do you use? a. b. c. d. e. f. 15 I do not use SAS 8. 2 SAS® 9 SAS 9. 1 SAS 9. 2 Other

Chapter 1: Getting Started 1. 1 Course Logistics 1. 2 Introducing the Structured Query Language 1. 3 Introducing the Business Scenario 16

Objectives n n 17 Describe the historical development of Structured Query Language (SQL). Explain how SQL is used.

Structured Query Language (SQL) is a standardized language originally designed as a relational database query tool. SQL is currently used in many software products to retrieve and update data. 18

Structured Query Language: Timeline 1970 1980 1990 2000 IBM develops SQL. 1970 – Dr. E. F. Codd of IBM proposes SQL. 1981 – First commercial SQL product is released. 1989 – More than 75 SQL-based systems exist. SAS 6. 06 includes PROC SQL. 1999 – PROC SQL is enhanced for SAS 8. 2004 – PROC SQL is enhanced for SAS® 9. 19

The SQL Procedure The SQL procedure has the following characteristics: n enables the use of SQL in SAS n is part of Base SAS software n follows American National Standards Institute (ANSI) standards n includes enhancements for compatibility with SAS software 20

The SQL Procedure Features With PROC SQL, you can use SQL language syntax to do the following: n query SAS data sets n generate reports from SAS data sets n combine SAS data sets in many ways n create and delete SAS data sets, views, and indexes n update existing SAS data sets n sometimes reproduce the results of multiple DATA and procedure steps with a single query 21

Structured Query Language Input Output Report SAS Data Set DBMS Table SAS Data View PROC SQL SAS Data Set SAS Data View DBMS Table 22

23

Setup for the Poll n n n 24 Issue a LIBNAME statement for the orion library, which contains the course data. You can use the s 101 a 01 program if you want. Change the data location, if necessary. Submit the program s 101 a 02. Answer the following questions: – What is the name of the input SAS data set? – Do the column names appear in the SELECT statement? s 101 a 02

1. 04 Multiple Choice Poll What is the name of the input SAS data set? a. b. c. d. 25 orion. Employee_payroll SQL SELECT None of the above

1. 04 Multiple Choice Poll – Correct Answer What is the name of the input SAS data set? a. b. c. d. 26 orion. Employee_payroll SQL SELECT None of the above

1. 05 Poll Did the names of the columns that appeared in the results appear in the SELECT statement in the code? Yes No 27

1. 05 Poll – Correct Answer Did the names of the columns that appeared in the results appear in the SELECT statement in the code? Yes No 28

The SQL Procedure The SQL procedure is n a tool for querying data n a tool for data manipulation and management n an augmentation to the DATA step. The SQL procedure is not n a DATA step replacement n a custom reporting tool. 29

SAS Data Sets A SAS data set can be any of the following: n a SAS data file that stores data descriptions and data values together in native SAS format n a DBMS table accessed via a SAS/ACCESS engine n a SAS data view, using one of the following technologies: – PROC SQL view – a stored SQL query that retrieves data stored in other tables – DATA step view – a stored DATA step that retrieves data stored in other files – SAS/ACCESS view – a stored ACCESS descriptor containing information required to retrieve data stored in a DBMS (older technology) 30

Terminology Data Processing 31 SAS SQL File Data Set Table Record Observation Row Field Variable Column

32

Chapter 1: Getting Started 1. 1 Course Logistics 1. 2 Introducing the Structured Query Language 1. 3 Introducing the Business Scenario 33

Objectives n n 34 Describe the data used in this course. Explain the relationships between the various tables.

The Orion Star Company Analyze a subset of Orion Star data including the following: n employees in the United States and Australia n customers from Australia, Canada, Germany, Israel, South Africa, the United States, and Turkey n the years 2002 through 2007 The tables and columns are related as shown on the next slide. 35

Orion Star Data Relationships Human Resources Data Employee_ID is the key column for HR data. 36

Orion Star Data Relationships Order Data 37 Order_ID is the key Product_ID is the key column for Order data. column for Product data.

Orion Star Data Relationships Customer Data Customer_ID is the key column for Customer data. 38

Orion Star Data Relationships between Types of Data 39

40

1. 06 Multiple Answer Poll Which of the Order data tables contain the column Employee_ID? e. orion. QTR 1_2007 orion. QTR 2_2007 orion. Order_Fact orion. Price_List orion. Product_Dim f. All of them a. b. c. d. 41

1. 06 Multiple Answer Poll – Correct Answer Which of the Order data tables contain the column Employee_ID? e. orion. QTR 1_2007 orion. QTR 2_2007 orion. Order_Fact orion. Price_List orion. Product_Dim f. All of them a. b. c. d. 42

Orion Country Codes Code Country 43 AU Australia CA Canada DE Germany IL Israel TR Turkey US United States ZA South Africa

Orion Product ID Codes are numeric in the form XXYYZZZZ. Individual Product Identifier Product Type XXYYZZZZ Subcategory 44

Orion Product ID Codes Code Product Type 45 21 Children 22 Clothes and Shoes 23 Outdoors 24 Sports

46

1. 07 Quiz Use the data relationship charts on pages 1 -16 through 1 -18 to answer the following question: Which table(s) contains the column Order_Date? 47 s 101 a 03

1. 07 Quiz – Correct Answer Use the data relationship charts on pages 1 -16 through 1 -18 to answer the following question: Which table(s) contains the column Order_Date? 1. orion. Order_Fact 2. orion. Qtr 1_2007 3. orion. Qtr 2_2007 48 s 101 a 03