Transcript Efficient Data Access using SAS Enterprise Guide: Best

Efficient Data Access using
SAS Enterprise Guide:
Best Practices
Chris Hemedinger
Software Manager
Enterprise Clients
Copyright © 2005, SAS Institute Inc. All rights reserved.
Data like
water
Copyright © 2005, SAS Institute Inc. All rights reserved.
2
Copyright © 2005, SAS Institute Inc. All rights reserved.
3
Agenda
 Overview of how SAS Enterprise Guide works
 Specific data access scenarios
 Considerations for building queries
Copyright © 2005, SAS Institute Inc. All rights reserved.
4
Geography of SAS Enterprise Guide
SAS Integration
Technologies
SAS/ACCESS
SAS Application Server
OLEDB / ODBC / etc.
SAS Enterprise Guide
Copyright © 2005, SAS Institute Inc. All rights reserved.
Data source
5
File->Open: Decisions, decisions…
Copyright © 2005, SAS Institute Inc. All rights reserved.
6
Scenario: Data in Oracle Database
One way: Use ODBC driver on Windows to get to
Oracle
Copyright © 2005, SAS Institute Inc. All rights reserved.
7
Scenario: Data in Oracle Database
(continued)
Better: Use SAS/ACCESS to Oracle to define
a SAS library
libname ORACLE2 oracle
user=scott
password=tiger
path='hrdept_002';
Copyright © 2005, SAS Institute Inc. All rights reserved.
8
Scenario: Data in Oracle Database
(continued)
Alternative: Use SAS/ACCESS to ODBC to
access a DSN that gets to Oracle
Copyright © 2005, SAS Institute Inc. All rights reserved.
9
Scenario: Data in PC database file
One way: Open data directly in Enterprise
Guide for use in analysis
Copyright © 2005, SAS Institute Inc. All rights reserved.
10
Scenario: Data in PC database file
(continued)
Better: Use Import Data task to create SAS data
table, then begin analysis
Copyright © 2005, SAS Institute Inc. All rights reserved.
11
Considerations for Queries
 Query builder can be a one-stop shop for data
manipulation
• Expressions, joins, filters
 All work is done using PROC SQL
Copyright © 2005, SAS Institute Inc. All rights reserved.
12
Considerations for Queries
(continued)
Copyright © 2005, SAS Institute Inc. All rights reserved.
13
Considerations for Queries
(continued)
 When performing joins, decide which server to
use
Copyright © 2005, SAS Institute Inc. All rights reserved.
14
Considerations for Queries
(continued)
 Goal: Pass as much work as possible to the
database
 Query builder is “database agnostic”
 Relies on implicit pass-through
Copyright © 2005, SAS Institute Inc. All rights reserved.
15
Considerations for Queries
(continued)
 Example of pass-through OK
CREATE TABLE SASUSER.QURY3428 AS SELECT
CUSTOMERS.CUSTOMER FORMAT=$8.,
CUSTOMERS.STATE FORMAT=$2.,
CUSTOMERS.ZIPCODE FORMAT=$7.,
...
FROM ORACLE.CUSTOMERS AS CUSTOMERS
WHERE UPCASE(CUSTOMERS.COUNTRY) = “CANADA”;
Copyright © 2005, SAS Institute Inc. All rights reserved.
16
Considerations for Queries
(continued)
 Example of no pass-through
CREATE TABLE SASUSER.QURY3428 AS SELECT
CUSTOMERS.CUSTOMER FORMAT=$8.,
CUSTOMERS.STATE FORMAT=$2.,
CUSTOMERS.ZIPCODE FORMAT=$7.,
...
FROM ORACLE.CUSTOMERS AS CUSTOMERS
WHERE STNAME(CUSTOMERS.STATE) = "MARYLAND";
Copyright © 2005, SAS Institute Inc. All rights reserved.
17
Summary
 Know your “data geography”
 Analyze each data access scenario – follow the
flow
 Consider your queries
Copyright © 2005, SAS Institute Inc. All rights reserved.
18
Copyright © 2005, SAS Institute Inc. All rights reserved.
19