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




3
Explain the naming convention that is used for the
course files.
Compare the 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
s104d01x
course ID
Code
4
Type
a
Activity
d
Demo
e
Exercise
s
Solution
chapter #
type
item #
placeholder
s104a01 Example: The SAS® SQL 1:
s104a02
Essentials course
ID is s1, so
s104a02s
s104d01=
s104d01
SQL Chapter 4,
s104d02
Demo 1.
s104e01
s104e02
s104s01
s104s02
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
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

Invoke the SAS Help facility by doing one of the
following actions:
– Type Help on the command line.

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


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.
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


17
Describe the historical development of Structured
Query Language (SQL).
Explain how SQL is used.
Structured Query Language
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:
 enables the use of SQL in SAS
 is part of Base SAS software
 follows American National Standards Institute (ANSI)
standards
 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:
 query SAS data sets
 generate reports from SAS data sets
 combine SAS data sets in many ways
 create and delete SAS data sets, views, and indexes
 update existing SAS data sets
 sometimes reproduce the results of multiple DATA
and procedure steps with a single query
21
Structured Query Language
Input
Output
SAS Data Set
Report
DBMS Table
SAS Data View
PROC
PROC
SQL
SQL
SAS Data Set
SAS Data View
DBMS Table
22
23
Setup for the Poll



24
Issue a LIBNAME statement for the orion library,
which contains the course data. You can use the
s101a01 program if you want. Change the data
location, if necessary.
Submit the program s101a02.
Answer the following questions:
– What is the name of the input SAS data set?
– Do the column names appear in the SELECT
statement?
s101a02
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
 a tool for querying data
 a tool for data manipulation and management
 an augmentation to the DATA step.
The SQL procedure is not
 a DATA step replacement
 a custom reporting tool.
29
SAS Data Sets
A SAS data set can be any of the following:
 a SAS data file that stores data descriptions and data
values together in native SAS format
 a DBMS table accessed via a SAS/ACCESS engine
 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


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:
 employees in the United States and Australia
 customers from Australia, Canada, Germany, Israel,
South Africa, the United States, and Turkey
 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
Relationships between Types of Data
39
40
1.06 Multiple Answer Poll
Which of the Order data tables contain the column
Employee_ID?
a. orion.QTR1_2007
b. orion.QTR2_2007
c. orion.Order_Fact
d. orion.Price_List
e. orion.Product_Dim
f.
41
All of them
1.06 Multiple Answer Poll – Correct Answer
Which of the Order data tables contain the column
Employee_ID?
a. orion.QTR1_2007
b. orion.QTR2_2007
c. orion.Order_Fact
d. orion.Price_List
e. orion.Product_Dim
f.
42
All of them
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
Codes are numeric in the form XXYYZZZZZZZZ.
Individual Product Identifier
Product Type
XXYYZZZZZZZZ
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
s101a03
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.Qtr1_2007
3. orion.Qtr2_2007
48
s101a03
Descargar

Session 1