SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS Formats installed in Teradata

Reply
Contributor
Posts: 22

SAS Formats installed in Teradata

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:

  1. Install SAS formats in Teradata,
  2. Create SAS views using the libname access methode for your Teradata database.
  3. Format data in those SAS views to Teradata data, based upon data ranges for big decimal columns.
  4. I have published queries to find large decimal columns in Teradata, and compute the data ranges.
  5. Run those SAS views with the trace optioins turned on.
  6. The SAS views should make a request to Teradata to cast the columns.
  7. 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"

 

Super User
Posts: 5,257

Re: SAS Formats installed in Teradata

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...?

Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 205 views
  • 0 likes
  • 2 in conversation