Retrieving Formatted Data

Goal

You want your application to retrieve data from a SAS Workspace Server with formatting applied as it is stored in the Base SAS data set.

Note: The SAS/SHARE driver does not support a connection property that controls formats. However, the drivers do support formatting in the SQL string.

Implementation

Sample Code for Retrieving Formatted Data

Base SAS data sets can have formats associated with each column. When reading SAS data sets with the IOM driver, you can control whether those formats are applied.

By default, the formats associated with a Base SAS data set are not applied when data is retrieved. In order to apply SAS formatting, you set the apply_formats connection property to true. The code looks like this:

properties.setProperty("apply_formats", "true");

When apply_formats is set to true, all data is returned from the server as a string with column formatting applied. To access the data, you use the getString method on the result set. For example, assume that a column with the format “5.” has a value of 15.2854. If apply_formats is set to true, then calling getString for that value will return “15”.

How Formats Are Determined Regardless of the Property Setting

Regardless of the apply_formats setting, the formats that are associated with a SAS data set determine what column type is returned by the driver.

When a ResultSet is created from a query, the driver obtains column metadata information from SAS. From this metadata, the driver uses parts of the column format to make a type determination. For example, if the format is "DATEx." or "DATETIME", then the type is considered to be an SQL DATE (or TIMESTAMP). If the format is DOLLAR12., then the type is considered to be SQL INT.