How to change formats within a PROC SQL INSERT statement?

[esvenson]

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;

[Shmuel]

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.

[esvenson]

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.

[Reeza · Accepted solution]

It should be INPUT, not PUT

[Reeza]

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.