topic Re: Formating in SQL in SAS Enterprise Guide

Formating in SQL

Scott86 — Thu, 10 May 2018 10:01:27 GMT

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

Re: Formating in SQL

SuryaKiran — Thu, 10 May 2018 12:56:02 GMT

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.

Re: Formating in SQL

Patrick — Fri, 11 May 2018 11:23:03 GMT

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

Re: Formating in SQL

SuryaKiran — Fri, 11 May 2018 12:19:33 GMT

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