Publishing SAS Formats in Aster :: SAS(R) 9.3 In-Database Products: User's Guide, Fourth Edition

Overview of the Publishing Process

The SQL/MR function is the framework for enabling execution of user-defined functions within Aster through an SQL interface. A new SAS SQL/MR function, SAS_PUT( ), supports format publishing in Aster. The SAS_PUT( ) function is installed as part of the in-database deployment package. For more information, see the SAS In-Database Products: Administrator's Guide.

The %INDAC_PUBLISH_FORMATS macro creates the user-defined format files that are needed by the SAS_PUT( ) function and publishes those files to the Aster database.

This macro makes many formats that SAS supplies available inside Aster. 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 range label pairs into embedded data in Aster.

The %INDAC_PUBLISH_FORMATS macro performs the following tasks:

takes the format catalog and produces a sasput_type_fmtname.xml file for each user-defined format that is in the format catalog.
uses the SAS/ACCESS Interface to Aster to insert the format files into either the NC_INSTALLED_FILES table under the PUBLIC schema (Aster 4.5) or the NC_USER_INSTALLED_FILES table under a specified schema (Aster 4.6).

Note: Files larger than 32k are automatically divided into 32k chunks of data and then are concatenated back together by performing multiple updates.

Note: If there are no user-defined formats, you do not need to run the %INDAC_PUBLISH_FORMATS macro. The formats that SAS supplies are installed in either the NC_INSTALLED_FILES table (Aster 4.5) or the NC_USER_INSTALLED_FILES table (Aster 4.6) when the SAS 9.3 Formats Library for Aster is installed.

When the user accesses a SAS format through the SQL interface, the SAS_PUT( ) function retrieves the specified format's .xml file and activates the SAS Embedded Process to perform the formatting. For more information, see Using the SAS_PUT( ) Function in the Aster Database.

Running the %INDAC_PUBLISH_FORMATS Macro

To run the %INDAC_PUBLISH_FORMATS macro, follow these steps.

Start SAS 9.3 and submit these commands in the Program Editor or the Enhanced Editor:
```
%indacpf;
%let indconn = user=youruserid password=yourpwd
   dsn=yourdsn;
```
For more information, see %INDACPF Macro and the INDCONN Macro Variable.
Run the %INDAC_PUBLISH_FORMATS macro.

For more information, see %INDAC_PUBLISH_FORMATS Macro Syntax.

Messages are written to the SAS log that indicate the success or failure of the creation of the XML format files.

%INDACPF Macro

The %INDACPF macro is an autocall library that initializes the format publishing software.

INDCONN Macro Variable

The INDCONN macro variable is used to provide credentials to connect to Aster. You must specify user, password, and either a DSN name or a server and database name. You must assign the INDCONN macro variable before the %INDAC_PUBLISH_FORMATS macro is invoked.

The value of the INDCONN macro variable for the %INDAC_PUBLISH_FORMATS macro has one of these formats:

USER=username PASSWORD=password DSN=dsnname <SCHEMA=schemaname>

USER=username PASSWORD=password DATABASE=databasename
SERVER=servername <SCHEMA=schemaname>

USER=username

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

PASSWORD=password

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

Tip	You can use only PASSWORD=, PASS=, or PW= for the password argument. PWD= is not supported and causes an error.

DSN=datasourcename

specifies the configured Aster data source to which you want to connect.

Requirement

You must specify either the DSN= argument alone, or the SERVER= and DATABASE= arguments in the INDCONN macro variable.

DATABASE=databasename

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

Requirement

You must specify either the DSN= argument alone, or the SERVER= and DATABASE= arguments in the INDCONN macro variable.

SERVER=servername

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

Requirement

You must specify either the DSN= argument alone, or the SERVER= and DATABASE= arguments in the INDCONN macro variable.

SCHEMA=schemaname

specifies the schema name for the database.

Default	Your default schema. To determine your default schema name, use the show search_path command from the Aster Client Tool (ACT).
Restriction	The SCHEMA argument is valid only for Aster 4.6. For Aster 4.5, the format XML files are published to the PUBLIC schema.

Tip

The INDCONN macro variable is not passed as an argument to the %INDAC_PUBLISH_FORMATS macro. 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.

%INDAC_PUBLISH_FORMATS Macro Syntax

%INDAC_PUBLISH_FORMATS

(<DATABASE=database–name>
<, FMTCAT=format-catalog-filename | ALL>
<, FMTLIST=format-name <...format-name> | ALL>
<, ACTION=CREATE | REPLACE | DROP>
<, OUTDIR=diagnostic-output-directory>
);

Arguments

DATABASE=database-name

specifies the name of an Aster database to which the format files are published to either the NC_INSTALLED_FILES table (Aster 4.5) or the NC_USER_INSTALLED_FILES table (Aster 4.6). This argument lets you publish the sasput_type_fmtname.xml format files to a shared database where other users can access them.

Restriction	If you specify DSN= in the INDCONN macro variable, do not use the DATABASE argument. For more information, see Running the %INDAC_PUBLISH_FORMATS Macro.
Tip	It is not necessary that the format definitions and the SAS_PUT( ) function reside in the same database as the one that contains the data that you want to format. You can use the SQLMAPPUTO= system option to specify the database where the format definitions and the SAS_PUT( ) function have been published.

FMTCAT=format-catalog-filename | ALL

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 Aster.

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 Aster.
Interaction	If the format definitions that you want to publish exist in multiple catalogs, you must copy them into a single catalog for publishing. If you specify more than one format catalog using the FMTCAT argument, only the last catalog that you specify is published.
See	Considerations and Limitations with User-Defined Formats

FMTLIST=format-name <...format-name> | ALL

specifies a list of formats that are created, replaced, or dropped.

Default	ALL
Requirements	Format names must be separated with a space.
Requirements	Character format names must begin with a dollar sign ($); for example, $EMPNAME.
Interaction	When ACTION=CREATE or REPLACE, the list of formats that are in the specified format catalog (FMTCAT=) are added to either the NC_INSTALLED_FILES table (Aster 4.5) or the NC_USER_INSTALLED_FILES table (Aster 4.6). When ACTION=DROP and FMTCAT=ALL, all the formats listed in FMTLIST are dropped. If ACTION=DROP and FMTCAT=format-catalog-filename, only those listed formats that exist in the format catalog are dropped.

ACTION=CREATE | REPLACE | DROP

specifies that the macro performs one of these actions:

CREATE

creates a sasput_type_fmtname.xml file for each user-defined format in the format catalog.

Tip	If a format file already exists, an error occurs.

REPLACE

overwrites the current sasput_type_fmtname.xml file if it is already registered or creates a new sasput_type_fmtname.xml file, if one is not registered.

DROP

causes the sasput_type_fmtname.xml files to be dropped from either the NC_INSTALLED_FILES table (Aster 4.5) or the NC_USER_INSTALLED_FILES table (Aster 4.6) in the database.

Interaction

If FMTCAT=ALL, all user-defined format files are dropped.

Default	CREATE
Tip	If the format files was defined previously and you specify ACTION=CREATE, you receive warning messages from Aster. If the format files were defined previously and you specify ACTION=REPLACE, a message is written to the SAS log indicating that the format file has been replaced.

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

Format Publishing Macro Example

%indacpf;
%let indconn = server=acbase user=user1 password=open1 dsn=ncluster;
%indac_publish_formats(fmtcat= fmtlib.formats);

This sequence of macros generates an .xml file for each format. The format data types that are supported are numeric and character. The naming convention for the .xml file is sasput_type_fmtname.xml, where type is the format data type (N for numeric formats or C for character formats), and fmtname is the format name.

After the format files are installed, you can invoke user-defined formats in Aster nCluster by using the SAS_PUT( ) function. For more information, see Using the SAS_PUT( ) Function in the Aster Database.