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

  • Scientific Benefit vs. Ethical Cost - WordPress.com

    Scientific Benefit vs. Ethical Cost - WordPress.com

    Are there any unintended ethical issues that may arise in the study on bystander apathy? What was the scientific benefit? Could it be predicted? Consider Milgram , was the harm caused anticipated? What does this suggest about ethical committees? What...
  • SEER*DMS Development Defining Road Maps and Setting Priorities

    SEER*DMS Development Defining Road Maps and Setting Priorities

    Status Report - MU2. A Squish "group" was created for the MU2 workgroup. Use it to notify others in the group on MU2 issues. Summary of Action Items from April 27, 2017:
  • Starbucks Time Line Launches Starbucks VIA Ready Brew

    Starbucks Time Line Launches Starbucks VIA Ready Brew

    Porter's Five Forces: Current Competitive Force. Threat of Potential Entrants. Bargaining Power of Buyers. Bargaining Power of Suppliers. Threat of Substitute Products "Starbucks is always managing the tension between opposites. One the other hand, I'd say we're comforting and predictable.
  • Intercultural Study of Personal Space - rockus

    Intercultural Study of Personal Space - rockus

    Intercultural Study of Personal Space by Linh Bui & Julielynn Wong Personal space is an innate element of all human beings but the length, the thickness and the characteristics of that space will vary according to the culture. 4 dimensions...
  • Poetry Sound Devices Meter poetry's rhythm, or its

    Poetry Sound Devices Meter poetry's rhythm, or its

    Half Rhyme (Slant Rhyme): imperfect, approximate rhyme. In the . mustardseed. sun. By full tilt river and switchback sea. Where the cormorants scud, In his house on stilts high among beaks. Dylan Thomas. Poem on His Birthday
  • Task shifting: field experience and current thinking within MSF

    Task shifting: field experience and current thinking within MSF

    Task shifting & HRH Crisis: field experience and current thinking within MSF Mit Philips, Médecins Sans Frontières, Brussels. WHO satelite conference, Kigali June 2007
  • sight touch hearing smell taste  What can you

    sight touch hearing smell taste What can you

    sight smell touch hearing taste What can you see? What can you hear? What can you smell? What can you touch? What can you taste? Task: Make a mind map to show what you can see, hear, smell, touch and...
  • Learning from Challenging Text - Shanahan on Literacy

    Learning from Challenging Text - Shanahan on Literacy

    Vocabulary. The ACT does not have a vocabulary test. However, academic vocabulary is essential to interpreting text and some ACT questions may ask students to interpret the meaning of vocabulary within particular texts (as part of reading comprehension—not as a...