Publishing SAS Formats in Teradata

Running the %INDTD_PUBLISH_FORMATS Macro

Overview of the Publishing Process

The SAS publishing macros are used to publish formats and the SAS_PUT( ) function in the Teradata EDW.

The %INDTD_PUBLISH_FORMATS macro creates the files that are needed to build the SAS_PUT( ) function and publishes these files to the Teradata EDW.

The %INDTD_PUBLISH_FORMATS macro also publishes the formats that are included in the SAS formats library. This makes many formats that SAS supplies available inside Teradata. For more information about the SAS formats library, see Deployed Components for Teradata.

In addition to formats that SAS supplies, you can also publish the PROC FORMAT definitions that are contained in a single SAS format catalog by using the FMTCAT= option. The process of publishing a PROC FORMAT catalog entry converts the value-range-set(s) into embedded data in Teradata.

Note: If you specify more than one format catalog using the FMTCAT= option, the last format that you specify is the one that is published. You can have only one formats library active in the Teradata database.

The %INDTD_PUBLISH_FORMATS macro performs the following tasks:

creates .h and .c files, which are necessary to build the SAS_PUT( ) function
produces a script of Teradata commands that are necessary to register the SAS_PUT( ) function in the Teradata EDW
uses SAS/ACCESS Interface to Teradata to execute the script and publish the files to the Teradata EDW

Running the %INDTD_PUBLISH_FORMATS Macro

Follow these steps to run the %INDTD_PUBLISH_FORMATS macro.

Start SAS and submit this command in the Program or Enhanced Editor:
```
%let indconn = server=myserver user=myuserid password=xxxx
   database=mydb;
```
For more information, see the INDCONN Macro Variable.
Run the %INDTD_PUBLISH_FORMATS macro.

For more information, see %INDTD_PUBLISH_FORMATS Macro Syntax.

Messages are written to the SAS log that indicate whether the SAS_PUT( ) function was successfully created.

INDCONN Macro Variable

The INDCONN macro variable is used to provide credentials to connect to Teradata. You must assign the INDCONN macro variable before the %INDTD_PUBLISH_FORMATS macro is invoked.

The syntax for the value of the INDCONN macro variable depends on whether you are using Teradata single sign-on (SSO) authentication for Kerberos.

Here is the syntax for the value of the INDCONN macro variable if you are not using Teradata single sign-on (SSO) authentication:

SERVER=server USER=userid | userid@LDAP PASSWORD=password
<DATABASE=database>
Here is the syntax for the value of the INDCONN macro variable if you are using Teradata single sign-on (SSO) authentication:

SERVER=server USER=@KRB5 | userid@KRB5 <PASSWORD=password>
<DATABASE=database>

Note: If you are using Teradata single sign-on (SSO) authentication and do not specify a user ID and password, the publishing macros use your Kerberos authentication information to generate connection credentials.

Arguments

SERVER=server

specifies the Teradata server name or the IP address of the server host.

USER=userid | userid@LDAP or userid@KRB5 | @KRB5

specifies the Teradata user name (also called the user ID) that is used to connect to the database.

Restriction	If you are using Teradata single sign-on (SSO) authentication with Kerberos, USER= must match the operating system identity of the user that is running SAS. Specifying another user ID (sometimes called “sign-on as”) is not supported with Kerberos.
Requirement	If you are not using Teradata single sign-on (SSO) authentication, you must specify both userid and password.
Tip	If you are using Teradata single sign-on (SSO) authentication, you can specify either userid@KRB5 or @KRB5. Using @KRB5 forces the publishing process to use Kerberos authentication.

PASSWORD=password

specifies the password that is associated with your Teradata user ID.

Requirement	If you are not using Teradata single sign-on (SSO) authentication, you must specify both a user ID and a password. If you are using Teradata single sign-on (SSO) authentication, password is optional.
Tip	Use only PASSWORD=, PASS=, or PW= for the password argument. PWD= is not supported and causes an error to occur.

DATABASE=database

specifies the Teradata database that contains the tables and views that you want to access.

Default	Your current database
Requirement	The maximum database name length is 128 characters, and it must be a valid Teradata database name.

Here is an example of using the INDCONN macro variable without single sign-on authentication.

indconn = server=servref user=johndoe password=xxxx database=2016db;

Here are some examples of using the INDCONN macro variable with single sign-on authentication.

indconn = server=servref user=johndoe@krb5 database=2016db;
indconn = server=servref user=johndoe@krb5 database=2016db;
indconn = server=servref user=@krb5 database=2016db;
indconn = server=servref user=johndoe@krb5;

Here is an example of using the INDCONN macro variable where single sign-on authentication is generated by your Kerberos ticket information.

indconn=server=servref database=2016db;

For more information, see “Authentication Using Kerberos” in SAS/ACCESS for Relational Databases: Reference.

Tip

The INDCONN macro variable is not passed as an argument to the %INDTD_PUBLISH_FORMATS macro. Consequently, this information can be concealed in your SAS job. You might want to place it in an autoexec file and set the permissions on the file so that others cannot access the user ID and password.

%INDTD_PUBLISH_FORMATS Macro Syntax

%INDTD_PUBLISH_FORMATS

(<DATABASE=database–name>
<, FMTCAT=format-catalog-filename>
<, FMTTABLE=format-table-name>
<, ACTION=CREATE | REPLACE | DROP>
<, MODE=PROTECTED | UNPROTECTED>
<, OUTDIR=diagnostic-output-directory>
);

Arguments

DATABASE=database-name

specifies the name of a Teradata database to which the SAS_PUT( ) function and the formats are published. This argument lets you publish the SAS_PUT( ) function and the formats to a shared database where other users can access them.

Default	The database specified in the INDCONN macro variable or your current database
Requirement	The maximum database name length is 128 characters, and it must be a valid Teradata database name.
Interaction	The database that is specified by the DATABASE= argument takes precedence over the database that you specify in the INDCONN macro variable. For more information, see Running the %INDTD_PUBLISH_FORMATS Macro.
Tip	The format definitions and the SAS_PUT( ) function do not need to reside in the same database as the one that contains the data that you want to format. You can use the SQLMAPPUTTO= system option to specify where the format definitions and the SAS_PUT( ) function are published. For more information, see SQLMAPPUTTO= System Option.

FMTCAT=format-catalog-filename

specifies the name of the format catalog file that contains all user-defined formats that were created with the FORMAT procedure and are made available in Teradata.

Default	If you do not specify a value for FMTCAT= and you have created user-defined formats in your SAS session, the default is WORK.FORMATS. If you do not specify a value for FMTCAT= and you have not created any user-defined formats in your SAS session, only the formats that SAS supplies are available in Teradata.
Interaction	If the format definitions that you want to publish exist in multiple catalogs, you must copy them into a single catalog for publishing.

FMTTABLE=format–table–name

specifies the name of the Teradata table that contains all formats that the %INDTD_PUBLISH_FORMATS macro creates and that the SAS_PUT( ) function supports. The table contains the columns in the following table.

Teradata Format Table Columns
Column Name	Description
FMTNAME	specifies the name of the format.
SOURCE	specifies the origin of the format. SOURCE can contain one of these values: SAS supplied by SAS. PROCFMT User-defined with PROC FORMAT.
PROTECTED	specifies whether the format is protected. PROTECTED can contain one of these values: YES Format was created with the MODE= option set to PROTECTED. NO Format was created with the MODE= option set to UNPROTECTED.

Default	If FMTTABLE is not specified, no table is created. You can see only the SAS_PUT( ) function. You cannot see the formats that are published by the macro.
Requirement	The maximum table name length is 128 characters, and it must be a valid Teradata table name.
Interaction	If ACTION=CREATE or ACTION=DROP is specified, messages are written to the SAS log that indicate the success or failure of the table creation or drop.

ACTION=CREATE | REPLACE | DROP

specifies that the macro performs one of these actions:

CREATE

creates a new SAS_PUT( ) function.

REPLACE

overwrites the current SAS_PUT( ) function, if a SAS_PUT( ) function is already registered or creates a new SAS_PUT( ) function if one is not registered.

DROP

causes the SAS_PUT( ) function to be dropped from the Teradata database.

Interaction

If FMTTABLE= is specified, both the SAS_PUT( ) function and the format table are dropped. If the table name cannot be found or is incorrect, only the SAS_PUT( ) function is dropped.

Default	CREATE.
Tip	If the SAS_PUT( ) function was defined previously and you specify ACTION=CREATE, you receive warning messages from Teradata. If the SAS_PUT( ) function was defined previously and you specify ACTION=REPLACE, a message is written to the SAS log indicating that the SAS_PUT( ) function has been replaced.

MODE=PROTECTED | UNPROTECTED

specifies whether the running code is isolated in a separate process in the Teradata database so that a program fault does not cause the database to stop.

Default	PROTECTED
Tip	Once the SAS formats are validated in PROTECTED mode, you can republish them in UNPROTECTED mode for a performance gain.
See	Modes of Operation

OUTDIR=diagnostic-output-directory

specifies a directory that contains diagnostic files.

Files that are produced include an event log that contains detailed information about the success or failure of the publishing process.

See	Special Characters in Directory Names

Modes of Operation

There are two modes of operation when executing the %INDTD_PUBLISH_FORMATS macro: protected and unprotected. You specify the mode by setting the MODE= argument.

The default mode of operation is protected. Protected mode means that the macro code is isolated in a separate process in the Teradata database, and an error does not cause the database to stop. It is recommended that you run the %INDTD_PUBLISH_FORMATS macro in protected mode during acceptance tests.

When the %INDTD_PUBLISH_FORMATS macro is ready for production, you can rerun the macro in unprotected mode. Note that you could see a performance advantage when you republish the formats in unprotected mode.

Format Publishing Macro Example

This sequence of macros generates a .c and a .h file for each data type. The format data types that are supported are numeric (FLOAT, INT), character, date, time, and timestamp (DATETIME).

%let indconn server=terabase user=user1 password=open1 database=mydb;
%indtd_publish_formats(fmtcat= fmtlib.fmtcat);

The %INDTD_PUBLISH_FORMATS macro also produces a text file of Teradata CREATE FUNCTION commands that are similar to these:

CREATE FUNCTION sas_put
(d float, f varchar(64))
RETURNS varchar(256)
SPECIFIC sas_putn
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME
'SL!"jazxfbrs"'
'!CI!ufmt!C:\file-path\'
'!CI!jazz!C:\file-path\'
'!CS!formn!C:\file-path\';

After it is installed, you can call the SAS_PUT( ) function in Teradata by using SQL. For more information, see Using the SAS_PUT( ) Function in the Teradata EDW.