How to change formats within a PROC SQL INSERT statement?

esvenson — Thu, 19 Apr 2018 15:11:02 GMT

Hello SAS Community,

I'm trying to insert some, but not all, columns from a SAS dataset into a Teradata table with the following statement below. I'm receiving the following error message. Is it possible to change the datatypes of columns from a SAS dataset to match those of the destination columns in a Teradata table when inserting? I've tried numerous ways of doing this without success. I tried a statement like this put(input(From_Dt,mmddyy10.),mmddyy10.); but even getting that changed to a datatype resembling the format found in TD did not work.

I would like to match the data types within the INSERT statement and avoid an extra preceding step to change the formats to match those in the TD table. I am using Base SAS 9.4 and Teradata v 15.10. I apologize if this is overly simple or rudimentary but I can't find an example of this scenario anywhere and I have been stuck a long time. Any assistance you can lend would be greatly appreciated. Many thanks in advance for your help.

ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

ERROR: Value 2 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

ERROR: Value 4 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

ERROR: Value 7 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

rsubmit;
proc sql noerrorstop;
connect to teradata (user="&xxxxxxx" password="&yyyyyyy" tdpid='xxxxxxxxxxxxxxx' schema='xxxxxxxx' mode=teradata);

INSERT INTO DB.TABLE (

REPORTING_START_DT /* Shows as DATE FORMAT 'yyyy-mm-dd' in Teradata table */
,REPORTING_END_DT /* Shows as DATE FORMAT 'yyyy-mm-dd' in Teradata table */
,PROGRAM_NM
,TIN /* Shows as DECIMAL(9,0) in Teradata table */
,ACCT_NBR
,ACCT_PRDCT_CD
,ACCT_CO_NBR /* Shows as SMALLINT in Teradata table */
,ZONEID_1
,RF1)

SELECT
From_Dt /* Shows as $12. (03/01/2018) in SAS dataset */
,To_Dt /* Shows as $12. (03/31/2018) in SAS dataset */
,'Lab Monitoring' AS PROGRAM_NM
,TIN /* Shows as $9. in SAS dataset */
,Acct_Num
,Product_Code
,COID /* Shows as $3. in SAS dataset */
, 0 AS ZoneID_1
,-1 AS RF1

from SAS_dataset;

DISCONNECT FROM TERADATA;
QUIT;
ENDRSUBMIT;

Re: How to change formats within a PROC SQL INSERT statement?

Shmuel — Thu, 19 Apr 2018 15:19:04 GMT

Is the problem with dates only ?

SAS dates are numeric containing teh days passedsince 01/01/1960.

Have you tried: put(From_Dt ,mmddyy10.); assuming that mm/dd/yyyy is a teradata literal format,

otherwise replace the mmddyy10. format to an appropriate format to be read by teradata sql.

Re: How to change formats within a PROC SQL INSERT statement?

Reeza — Thu, 19 Apr 2018 15:25:41 GMT

You're changing TYPES not FORMATS (different meaning in SAS).

You can use INPUT() to convert character to numeric and PUT() to convert numeric to character.

input(from_dt, yymmdd10.) as from_dt format = date9.

Re: How to change formats within a PROC SQL INSERT statement?

esvenson — Thu, 19 Apr 2018 15:26:15 GMT

When I try this to test that approach,

data test;

set test_1

From_Dt_Test = put(From_Dt,mmddyy10.);

run;

it returns this ERROR 48-59: The format $MMDDYY was not found or could not be loaded.

Re: How to change formats within a PROC SQL INSERT statement?

Reeza — Thu, 19 Apr 2018 15:40:45 GMT

It should be INPUT, not PUT

topic Re: How to change formats within a PROC SQL INSERT statement? in SAS Programming

How to change formats within a PROC SQL INSERT statement?

Re: How to change formats within a PROC SQL INSERT statement?

Re: How to change formats within a PROC SQL INSERT statement?

Re: How to change formats within a PROC SQL INSERT statement?

Re: How to change formats within a PROC SQL INSERT statement?