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;
It should be INPUT, not PUT
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.
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.
It should be INPUT, not PUT
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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.