Monday, February 22, 2016

ISO 3166-1 alpha-2 (two-letter country code) format for SAS

Here is the widely-used ISO 3166-1 alpha-2 format for use in SAS. It is commonly called the two-letter country code format.

The PROC FORMAT code generates a character format, so where the raw data contains a code, such as US, it expands it to the pretty name, such as United States. As with any SAS format, applying the format does not change the underlying data.

Wednesday, February 3, 2016

Undocumented SAS feature: Bulkloading to Netezza with ODBC interface

The SAS/ACCESS Interface to ODBC in SAS 9.4M4 states it supports bulk loading only to "Microsoft SQL Server data on Windows platforms." However, in practice on the Windows platform it also supports bulk loading to Netezza.

Bulk loading is amazingly fast. In some of my benchmarks the duration of the whole bulk loading operation is independent of the number of rows inserted!

By default on Netezza the bulk loading interface delimits values using a pipe character, and for cases where the values contain a pipe, SAS Access Interface to ODBC unofficially supports the BL_DELIMITER option to specify an alternate delimiter. For the ODBC interface, this option is undocumented.

However, there are nuances with the BL_DELIMITER option. According to the SAS Access Interface to Netezza: