DATA Step, Macro, Functions and more

How to change formats within a PROC SQL INSERT statement?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How to change formats within a PROC SQL INSERT statement?

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;


Accepted Solutions
Solution
‎04-24-2018 01:38 PM
Super User
Posts: 23,320

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

It should be INPUT, not PUT

View solution in original post


All Replies
Trusted Advisor
Posts: 1,831

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

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.

Occasional Contributor
Posts: 17

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

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.

Solution
‎04-24-2018 01:38 PM
Super User
Posts: 23,320

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

It should be INPUT, not PUT

Super User
Posts: 23,320

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 82 views
  • 0 likes
  • 3 in conversation