BookmarkSubscribeRSS Feed
Scott86
Obsidian | Level 7

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

3 REPLIES 3
SuryaKiran
Meteorite | Level 14

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(nm ) 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.

Thanks,
Suryakiran
Patrick
Opal | Level 21

@SuryaKiran

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.

SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran

SAS Innovate 2025: Call for Content

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!

Submit your idea!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1129 views
  • 1 like
  • 3 in conversation