05-10-2018 06:01 AM
I was trying to load a data table off teradata in SAS and some of the formats of the variables were incompatible. I got around this by doing cast(var1 as varchar(8)) but I had to do this for each variable. Is there a way to change multiple variables formats with one function with SQL on Teradata?
05-10-2018 08:56 AM
Character values might not have any issues. SAS will apply default behavior for the datatypes (Check here). If you want the datatypes then you need to convert them from teradata side. Recently I came across where numeric values(Like 1234567891234) are defined in teradata as Float, SAS reads them as 8. format where I need format like 20. In this case in my pass-through I defined as CAST(t2.CUSTOMER_ID as decimal(20,0)) as CUSTOMER_ID (Note: IF teradata format is DECIMAL(n, m ) then SAS default will be (n+2 ).(m ) ). So SAS will have format 22.
First thing you need to do is find out the format associated in teradata. For this you can query the DBC.COLUMNS in teradata. Then if you want to convert the datatypes using CAST for multiple columns then you can create a macro from the table created from DBC.COLUMNS.
PROC SQL ; CONNECT TO TERADATA AS MYCON(SERVER= Authdomain= ); CREATE TABLE _Info AS SELECT * FROM CONNECTION TO MYCON ( SELECT TRIM(ColumnName) AS "ColumnName", ColumnType FROM DBC.COLUMNS WHERE TableName = 'myteradata' ); DISCONNECT FROM MYCON; QUIT; PROC SQL; select CASE WHEN ColumnType='FLOAT' then CAT('CAST(',ColumnName,'as decimal(20,0)) as',ColumnName) ELSE ColumnName END as Convert_Name INTO: New_Colums_Needed separated by ',' from _Info; quit; PROC SQL ; CONNECT TO TERADATA AS MYCON(SERVER= Authdomain= ); CREATE TABLE _Info AS SELECT * FROM CONNECTION TO MYCON ( SELECT &New_Colums_Needed. FROM myteradtata ); DISCONNECT FROM MYCON; QUIT;
This is just an approach and I didn't test. Use your datatypes that you wish to convert with proper function and so on.
05-11-2018 07:23 AM
You'd better cast such a customer_id to a CHAR or VARCHAR as SAS 9.4 uses only 8 Bytes to store numbers and you can't store 20 digits with full precision. You certainly don't want to end up with altered customer_id's in SAS.