Efficient Data Access using SAS Enterprise Guide: Best Practices

Efficient Data Access using SAS Enterprise Guide: Best Practices

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

Recently Viewed Presentations

  • Theories and Concepts of Motivation

    Theories and Concepts of Motivation

    Definition of Management "Management is the art of knowing what you want to do and then seeing that it is done in the best and cheapest way." - F.W.Taylor Management is the process of designing and maintaining of an environment...
  • Development of the Federation - elrgpfed.com

    Development of the Federation - elrgpfed.com

    Development of the Federation Out of hospital Integration - engage communities - manage networks Prevention New models / pathways MCP / Federations The Five Year Forward View Challenge Empower patients Increasing visits to GPs Demographics / increasing complexity Workforce Premises...
  • Da al-Ndbh (Arabic text with English and Urdu

    Da al-Ndbh (Arabic text with English and Urdu

    wa nashiru rayati alhuda. ... In what kind of statement and in what kind of talk can I describe you? wa ayya khitabin asifu fika wa ayya najwa. اور کسطرح آپ سے خطاب کروں اور سرگوشی کروں . Dūā al-Nūdbāh.
  • SENSATION - Administration

    SENSATION - Administration

    Sensory Transduction. Transduction - the process of transforming incoming information of the senses into neural messages that our brain can process. Applies to all five senses: Vision, Hearing, Touch, Taste and Smell.
  • Author: John Waters Act and Rule Utilitarianism Socratic

    Author: John Waters Act and Rule Utilitarianism Socratic

    Author: John Waters Act and Rule Utilitarianism Socratic Ideas Limited © All Rights Reserved
  • Admin - WPI

    Admin - WPI

    Summarizing Data. With lots of playtesting, there will be a lot of data. This is a good thing! But raw data is just a pile of numbers. Rarely of interest
  • Introductions Dan Lucariello President Distribution By Air Denver,

    Introductions Dan Lucariello President Distribution By Air Denver,

    C-TPAT - Customs Trade Partnership Against Terrorism. TSA as an Indirect Air Carrier (IAC) ISO 9004-2000 and 14001:2004. OHSAS18001:1999. SmartWay™ Transport Partner. IATA Endorsed by CNS. OFAC and FCPA Compliant. Government Associations: NDTA - National Defense Transportation Association. DOD Approved...
  • Your Health Triangle - Montgomery Township School District

    Your Health Triangle - Montgomery Township School District

    Choose a symbol to represent all of your "yes" answers. Graphing Your Results For example, if you like softball/baseball, you can use: Graphing Your Results Use your symbols to form a Health Triangle to represent your scores. Label each side....