Publishing SAS Formats in Aster nCluster

Overview of the Publishing Process

The SQL/MR function is the framework for enabling execution of user-defined functions within Aster nCluster through an SQL interface. A new SAS SQL/MR function, SAS_PUT( ), supports format publishing in Aster nCluster. 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 nCluster database.
This macro makes many formats that SAS supplies available inside Aster nCluster. 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 nCluster.
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 nCluster to insert the format files into either the NC_INSTALLED_FILES table under the PUBLIC schema (Aster nCluster 4.5) or the NC_USER_INSTALLED_FILES table under a specified schema (Aster nCluster 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 nCluster 4.5) or the NC_USER_INSTALLED_FILES table (Aster nCluster 4.6) when the SAS 9.3 Formats Library for Aster nCluster 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 nCluster Database.

Running the %INDAC_PUBLISH_FORMATS Macro

To run the %INDAC_PUBLISH_FORMATS macro, follow these steps.
  1. 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.
  2. 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 nCluster. 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 nCluster 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 nCluster 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 nCluster 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 nCluster 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 nCluster 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 nCluster 4.6. For Aster nCluster 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 nCluster database to which the format files are published to either the NC_INSTALLED_FILES table (Aster nCluster 4.5) or the NC_USER_INSTALLED_FILES table (Aster nCluster 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 nCluster.
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 nCluster.
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.
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 nCluster 4.5) or the NC_USER_INSTALLED_FILES table (Aster nCluster 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 nCluster 4.5) or the NC_USER_INSTALLED_FILES table (Aster nCluster 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 nCluster. 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 nCluster Database.
Copyright © SAS Institute Inc. All rights reserved.