topic Re: Formatting Teradata columns as numeric in SAS Programming

Formatting Teradata columns as numeric

supp — Tue, 05 Nov 2019 20:07:56 GMT

Hello, I am connecting to a Teradata database. There are columns that I need to be numeric to perform calculations. In Teradata these columns are varchar(50). I have tried the 'dbtype' option with no luck:

LIBNAME teradata teradata server=server USER=user PASSWORD=password schema=schema;

data work.want;
	set teradata.have(dbtype=(COL_1='Numeric(3)'));
run;

The program runs without error but COL_1 is still character, not numeric. I have also tried adding a format statement before the set statement

format COL_1 3.;

But then I get an error that COL_1 has been defined as both numeric and character.

How do I bring in COL_1 as numeric?

Re: Formatting Teradata columns as numeric

SASKiwi — Tue, 05 Nov 2019 20:25:11 GMT

The correct option to use is DBSASTYPE:

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n0v4ma1zb9lu99n1728j279rjcqi.htm&docsetVersion=9.4&locale=en

DBTYPE is used when creating database tables.

Re: Formatting Teradata columns as numeric

Kurt_Bremser — Tue, 05 Nov 2019 20:27:49 GMT

Play the rename/convert/drop game:

data work.want;
set teradata.have(rename=(COL_1=_col_1));
col_1 = input(_col_1, best32.);
drop _col_1;
run;

Re: Formatting Teradata columns as numeric

supp — Wed, 06 Nov 2019 13:45:14 GMT

Thanks @SASKiwi, this was it! Thanks for the explanation of the two options.