Efficient Data Access u SAS Enterprise Guide Best

Efficient Data Access u 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 ORACLE 2 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. QURY 3428 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. QURY 3428 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