SAS Formats installed in Teradata

RedPlanet — Mon, 07 Dec 2015 17:42:49 GMT

Do you have SAS/Access to Teradata, plus SAS formats installed in Teradata? I have a possible solution to the problem of viewing or analyzing BIGINT and very large (or very small) decimal values. But we don't have SAS formats installed in Teradata, so I cannot test my solution. If this works, then I can convince our admin and DBAs to permit this change in Teradata.

The basic idea is this:

Install SAS formats in Teradata,
Create SAS views using the libname access methode for your Teradata database.
Format data in those SAS views to Teradata data, based upon data ranges for big decimal columns.
I have published queries to find large decimal columns in Teradata, and compute the data ranges.
Run those SAS views with the trace optioins turned on.
The SAS views should make a request to Teradata to cast the columns.
Voila! Now your BIGINT and large (or small) decimal columns will appear in those SAS views.

Please let me know if you have SAS formats in Teradata, and I will write the code for the test.

I did my research, and was lucky enough to find code where Teradata does the cast, so I am theoretically confident in a positive result that will end the headache of BIGINT, etc. Here is the reference (and proof) in the SAS support site:

    After SAS formats are installed on Teradata, we should be able to create SAS
    views to access Teradata tables without having any issues reading BIGINT or
    large DECIMAL values. Of course, I plan to check those large numeric
    columns to make sure they are in the data range of 15 decimal digits.

A SAS document reference and programming example is here, or you can paste this URL:

    https://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003276900.htm

    If you scroll down a ways, you can see the SAS trace for a put() command, which casts that column:
    SAS Code:
    PUT(PRICE,Dollar8.2) AS PRICE_C

    SAS trace for Teradata code:
    TERADATA_2: Executed: on connection 0
     select distinct cast(sas_put("sas"."mailorderdemo"."PRICE", 'DOLLAR8.2')
     as char(8)) as "PRICE_C" from "sas"."mailorderdemo"

Re: SAS Formats installed in Teradata

LinusH — Tue, 08 Dec 2015 14:21:10 GMT

Great if it works, even if a NUM column in SAS would be preferred.

But don't you have a test environment for Teradata, where you could convince your DBAs to install SAS formats - that shouldn't be too hard...?

topic Re: SAS Formats installed in Teradata in SAS Data Management

SAS Formats installed in Teradata

Re: SAS Formats installed in Teradata