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

  • Which Poem? Why? Ozymandias London The Prelude My

    Which Poem? Why? Ozymandias London The Prelude My

    The Emigrée (1993) . Carol . Rumens. There once was a country… I left it as a childbut my memory of it is sunlight-clearfor it seems I never saw it in that Novemberwhich, I am told, comes to the mildest...
  • 4-H Science, Engineering, and Technology (SET) Presentation for

    4-H Science, Engineering, and Technology (SET) Presentation for

    Presentation for the California Afterschool Network. April 5, 2013. Steven Worker. 4-H SET Coordinator. Audience: Active members, 40 people from around California. Program directors, regional leads, frontline staff. Purpose: 1 resource presentation per committee meeting; getting resources to the field-----California...
  • Slope Stability - Binus University

    Slope Stability - Binus University

    course : s0705 - soil mechanic year : 2008 topic 7 slope stability content slope failure and slope stability (session 25-26 : f2f) bishop method (session 25-26 : f2f) slope failure causal factor of failure the slope is too steep...
  • What is a scale factor and how do

    What is a scale factor and how do

    Scale factor of small to large =23 =46 Scale factor of large to small =64 = 32 Core Lesson. The Scale Factor is the ratio of the lengths of corresponding sides in similar figures. Consider these two similar parallelograms. Since...
  • Cremation or Bodily Burial

    Cremation or Bodily Burial

    Darrell Stein Origins of Cremation Derived from the Latin cremare ("to burn"), cremation is the process of disposing (destroying) a dead body in the flames of fire. Historical evidence indicates that burning dead human beings apparently did not occur until...
  • Clinical Research Center Presents Scheduling Participant Visits With

    Clinical Research Center Presents Scheduling Participant Visits With

    If the subject has an MRN#, please enter it here. Otherwise, a dummy MRN# will be created by the CRC for the subject. Please note: If the protocol requires Shands' services, including Investigational Drug Services (IDS) then an MRN is...
  • PTSD Awareness Presentation March 2018

    PTSD Awareness Presentation March 2018

    in vivo, or live exposure, you help the client identify situations that they have been avoiding and then help them to repeatedly confront those situations until their distress decreases. Then the client begins imaginal exposure to the trauma.
  • Primary 3 English * Countable and Uncountable Nouns*

    Primary 3 English * Countable and Uncountable Nouns*

    Primary 3 English Countable nouns & Uncountable nouns Countable nouns e.g. I eat a banana every day. I like bananas. "Banana" is a countable noun. Countable nouns A countable noun can be 1. singular (banana) or Countable nouns Examples of...