Teradata and SAS with BigInt's

Question

We have a teradata database that is filled with BIGINT datatypes. SAS is the primary analytic engine for the organization. The SAS access library chokes on BIGINT and therefore forces all code to cast variables into a integer or decimal before returning them to SAS. Example:

proc sql;
connect to database (blah blah);
create table sas_dataset as 
  select * from connection to database(
    select
      cast(bigint_var as integer),
      varchar_var,
      char_var,
    from some_table
    );
  disconnect from database;
  quit;

Does anyone know a way to fix this? Possibly a macro for casting variables or SAS access fix? Keep in mind that there are literally thousands of variables in this database that are bigint and making changes to the database is out of the question.

Answer 1

If you can't fix it on the SAS side, you can always create a set of views on the Teradata side to do the casting. Then have SAS access the views:

create view sas_cast_db.some_table as 
select col1, col2, cast(bigint_var as decimal(18)), col3
from real_db.some_table;

Since you have lots of tables, it may be possible to write some SQL to auto-generate these from the data dictionary.