Teradata和SAS与BigInt的

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:

我们有一个充满BIGINT数据类型的teradata数据库。 SAS是组织的主要分析引擎。 SAS访问库在BIGINT上阻塞,因此强制所有代码在将变量返回SAS之前将变量转换为整数或小数。例:

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.

有谁知道解决这个问题的方法?可能是用于转换变量或SAS访问修复的宏?请记住,此数据库中有数千个变量是bigint,并且对数据库进行更改是不可能的。

3 个解决方案

#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:

如果您无法在SAS端修复它,您始终可以在Teradata端创建一组视图来进行转换。然后让SAS访问视图:

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.

由于您有很多表,因此可以编写一些SQL来从数据字典中自动生成这些表。

#2

The issue is that SAS can only handle a maximum of 15 digits for a BIGINT data type. SAS have provided a few work-arounds for SAS 9.2 (one mentioned above) for this issue. You can also get your SAS Platform Admin to arrange for the SAS server to be updated so that it truncates the BIGINT fields to 15 digits (obvious caveats apply), or update your LIBNAME statement to set all BIGINTs to be cast automatically.

问题是SAS只能处理BIGINT数据类型的最多15位数。 SAS针对此问题为SAS 9.2(上面提到的一个)提供了一些解决方法。您还可以让SAS平台管理员安排更新SAS服务器,以便将BIGINT字段截断为15位数(显然需要注意),或者更新LIBNAME语句以设置所有BIGINT自动投射。

http://support.sas.com/kb/39/831.html

#3

Sample code

data temp1; 
   set mylib.bigclass (dbsastype= (id='char(20)'));
run;

#1