CONNECTING SAS TO A TERADATA SERVER KEVIN MARK DATA SCIENTIST, - - PowerPoint PPT Presentation

▶
connecting sas to a
SMART_READER_LITE
LIVE PREVIEW

CONNECTING SAS TO A TERADATA SERVER KEVIN MARK DATA SCIENTIST, - - PowerPoint PPT Presentation

Feb 17, 2023 38 likes •221 views

CONNECTING SAS TO A TERADATA SERVER KEVIN MARK DATA SCIENTIST, GROUP COLLECTIONS WEDNESDAY 25 OCTOBER 2017 Westpac Banking Corporation ABN 33 007 457 141. Problem of data not in SAS format Enterprise Guide GOAL: Bring the data stored on the

slide-1
SLIDE 1

Westpac Banking Corporation ABN 33 007 457 141.

CONNECTING SAS TO A TERADATA SERVER

KEVIN MARK DATA SCIENTIST, GROUP COLLECTIONS WEDNESDAY 25 OCTOBER 2017

slide-2
SLIDE 2

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Problem of data not in SAS format

2

Images from http://www.teradata.com/Resources/Videos/Enabling-in-database-processing-with-SAS-ACCE and http://www.teradata.com/Solutions-and-Industries/sas-optimization/

GOAL: Bring the data stored on the Teradata server into a SAS session.

Enterprise Guide

slide-3
SLIDE 3

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Overview

3

Connecting SAS with Teradata Two interfaces to connect SAS with Teradata Concealing your Teradata password Importing Teradata data to SAS Joining a small SAS dataset with Teradata data Questions

Disclaimer: The presentation are the views of the presenter and not that of the Westpac Group.

slide-4
SLIDE 4

CONNECTING SAS WITH TERADATA

Two interfaces

slide-5
SLIDE 5

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Two interfaces to connect SAS with Teradata

5

Images from http://www.teradata.com/Resources/Videos/Enabling-in-database-processing-with-SAS-ACCE and http://www.teradata.com/Solutions-and-Industries/sas-optimization/

SAS/Access Interface to Teradata SAS/Access Interface to ODBC UNIX SERVER SAS EG running with a Profile connected to a SAS Unix Server

slide-6
SLIDE 6

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Battle of the Interfaces

6

Access Interface to ODBC Access Interface to Teradata Connects from local PC Server Connects from SAS Unix Server

  • Requires SAS EG to be connected

to SAS Unix Server Implicit connection via a libname statement Implicit connection via a libname statement Explicit connection via an ODBC connection in a SQL procedure Explicit connection via a Teradata connection in a SQL procedure Ability to execute Teradata commands from SAS

  • Including ability to create and use

temporary (volatile) tables.

slide-7
SLIDE 7

CONCEALING YOUR PASSWORD

slide-8
SLIDE 8

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Concealing your Teradata password

8

proc pwencode in=‘my_password’; run; The SAS Log outputs the following: {sas002} DBCC5712369DE1C65B19864C1564FB850F39 8DCF %let password={sas002} DBCC5712369DE1C65B19864C1564FB850F39 8DCF; User names and passwords to a Teradata server need to be specified in SAS code. We want to write, save and run code without divulging passwords, especially in LOGs. The PWENCODE Procedure will encrypt a password which you can use. Not perfect. See SAS Documentation on the procedure for how to use this securely. Example SAS Code: Situation

slide-9
SLIDE 9

IMPORTING TERADATA DATA TO SAS

slide-10
SLIDE 10

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Importing Teradata data to SAS

10

Images from http://www.teradata.com/Resources/Videos/Enabling-in-database-processing-with-SAS-ACCE and http://www.teradata.com/Solutions-and-Industries/sas-optimization/

SAS/Access Interface to Teradata SAS/Access Interface to ODBC UNIX SERVER

Database=DB1 Table=Employees Columns: employee_id, date,…

GOAL: Create the SAS dataset Emps_Today of today’s employees with all available columns.

ODBC Data Source Name dsn=“TD_Server” Teradata Server Address tdpid=“12.34.567.89”

slide-11
SLIDE 11

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Implicit connection via a libname statement

11

libname TD_DB1 teradata tdpid=“12.34.567.89” user=“user-id” password=“&password.” database=“DB1”; data work.Emps_Today; set TD_DB1.Employees; where date=‘25OCT2017’d; run; Access Interface to Teradata libname TD_DB1

  • dbc

dsn=“TD_Server” user=“user-id” password=“&password.” schema=“DB1”; data work.Emps_Today; set TD_DB1.Employees; where date=‘25OCT2017’d; run; Access Interface to ODBC

slide-12
SLIDE 12

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Explicit connection via a SQL Procedure

12

proc sql; connect to teradata ( tdpid=“12.34.567.89” user=“user-id” password=“&password.”); create table work.Emps_Today as select * from connection to teradata (select * from DB1.Employees where date=date ‘2017-10-25’); quit; Access Interface to Teradata proc sql; connect to ODBC ( dsn=“TD_Server” user=“user-id” password=“&password.”); create table work.Emps_Today as select * from connection to ODBC (select * from DB1.Employees where date=date ‘2017-10-25’); quit; Access Interface to ODBC

slide-13
SLIDE 13

JOINING A SMALL SAS DATASET

WITH TERADATA DATA

slide-14
SLIDE 14

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

14

Images from http://www.teradata.com/Resources/Videos/Enabling-in-database-processing-with-SAS-ACCE and http://www.teradata.com/Solutions-and-Industries/sas-optimization/

Database=DB1 Table=Employees Columns: employee_id, date,… Has millions of rows

SAS Dataset=EMPS Columns: employee_id, date Has only 100 rows. Create a temporary (volatile) table in Teradata called EMPS_TEMP Join data in Teradata Upload SAS Dataset to Teradata SAS Dataset=EMPS_Output Columns: employee_id, date,… Has only 100 rows.

slide-15
SLIDE 15

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Joining a small SAS dataset with Teradata data

15

execute( create multiset volatile table EMPS_TEMP ( employee_id VARCHAR(10) date DATE) primary index (employee_id)

  • n commit preserve rows;

) by teradata; execute (commit work) by teradata; quit; libname temp teradata tdpid=“12.34.567.89” user=“user-id” password=“&password.” connection=GLOBAL dbmstemp=YES; proc sql; connect to teradata ( tdpid=“12.34.567.89” user=“user-id” password=“&password.” connection=GLOBAL); Create a volatile table EMPS_TEMP Connect globally to temporary Teradata

slide-16
SLIDE 16

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Joining a small SAS dataset with Teradata data

16

proc append base=temp.EMPS_TEMP (MULTISTMT=yes) data=EMPS (keep=employee_id date); run; Upload SAS data to a Teradata volatile table

slide-17
SLIDE 17

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Joining a small SAS dataset with Teradata data

17

select b.* from EMPS_TEMP a inner join Employees b

  • n b.employee_id=a.employee_id

and b.date=a.date ); quit; proc sql; connect to teradata ( tdpid=“12.34.567.89” user=“user-id” password=“&password.” connection=GLOBAL); create table work.EMPS_OUTPUT as select * from connection to teradata ( …an explicit Teradata SQL select statement Create the resultant SAS dataset with …

slide-18
SLIDE 18

| Connecting SAS to a Teradata server

has more than 12 series‘ you will need to manually colour the additional series.

Thank you and Questions

18

kevin.mark@westpac.com.au