Hi everyone,
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?
Regards
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.
For Example:
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.
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.
@Patrick Agree, In fact my actual values are only 12 digits and just for an example I gave max value as 20. Sorry for the miss communication.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.