Using Formats in FedSQL

How to Store, Change, Delete, and Use Stored Formats

How to Format Output with the PUT Function

How to Store, Change, Delete, and Use Stored Formats

Storage of format metadata is supported in SAS data sets, SPD Engine data sets, SPD Server tables, and SASHDAT files only. As a result, when they are used in Base SAS, SAS data sets that were created with FedSQL behave the same as data sets that were created with SAS.

You specify formats in the CREATE TABLE statement as an attribute of the HAVING clause. For more information, see CREATE TABLE Statement. For example, in the following statement, the column profit is declared with the EURO13.2 format.

create table monthly (profit double having format euro13.2);

To change or remove a stored format, you must use Base SAS. When you want to display a different format for a column that has a stored format value when reading a table with FedSQL, use the PUT function.

FedSQL supports stored formats as follows:

Both user-defined formats and formats that are supplied by SAS can be stored. For more information , see Using a User-Defined Format.
All formats that are supplied by SAS can be stored. For a list of formats, see SAS Formats and Informats: Reference. FedSQL does not validate the formats. If the stored format is invalid, an error occurs, but only when the invalid format is used in the client application.
Formats can be stored only for columns of data types CHAR and DOUBLE.
To access the stored formats, you must have a Base SAS session available. The Base SAS session contains the SAS format definitions and SAS catalog file that stores the user-defined SAS format definitions.
You can store and retrieve format names. The format name is associated with a column by storing the format as a metadata attribute on the column. The metadata then can be retrieved for subsequent operations.

How to Format Output with the PUT Function

The PUT function enables you to associate a format with data in third-party data sources, as well as with SAS data. Formats are specified as arguments in the PUT function to output formatted data. In the following example, the PUT function returns the formatted value of 4503945867 using the DOLLAR17.2 format. The example returns the value $4,503,945,867.00.

select put(4503945867, dollar17.2);

FedSQL supports formats that are specified with the PUT function as follows:

If the PUT function is used without a format, an error occurs.
The PUT function supports a subset of the formats that are available for Base SAS when the FedSQL language is executed outside a Base SAS session. For a list, see Formats Supported with the PUT Function, by Category.
Formats can be associated with any of the data types that are supported by FedSQL. However, the data types are converted. Any value that is passed to the PUT function with a numeric format is converted to NVARCHAR, VARBINARY, or BINARY. The type conversions are carried out based on the format name. Any value that is passed with a character format to the PUT function is converted to NVARCHAR.
The format that is specified in PUT is transient. The PUT function does not affect the stored data.
The PUT function does not require a Base SAS session to be available; however, the functionality is limited when a session is not available.
The PUT function cannot be used on SASHDAT files. FedSQL support for SASHDAT files is Write-only.