Hello, I am trying to BULKLOAD a SAS dataset into Snowflake and I am having trouble with the DATETIME column in my SAS dataset. When trying to load it into Snowflake, I get the error message: ERROR: Error executing COPY command: Numeric value '2016-01-08 14:20:34.000000000' is not recognized File '@~/nucleus/xxxxx.gz', line 6187, character 186 Row 6187, column " CLR_CALL_ACTIV_HIST_2016 "["INTERACTION-ID":21] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client. For reference, here is my SAS code: /* Transform the data */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_0;
/* Load in work table containing the pre-transformed file */
SET WORK.CLR_CALL_ACTIV_HIST_2016_0;
/* Transformations */
'CHANNEL-ID-NUM'N = INPUT(STRIP('CHANNEL-ID'N), BEST32.);
IF MISSING('CHANNEL-OBJ-ID'N) OR UPCASE('CHANNEL-OBJ-ID'N) = "N/A" THEN
'CHANNEL-OBJ-ID-NUM'N = .;
ELSE
'CHANNEL-OBJ-ID-NUM'N = INPUT(STRIP('CHANNEL-OBJ-ID'N), BEST32.);
'GROUP-ID-NUM'N = INPUT(STRIP('GROUP-ID'N), BEST32.);
'INTERACTION-ID-NUM'N = INPUT(STRIP('INTERACTION-ID'N), BEST32.);
'DATE'N = INPUT(SUBSTR('TRANS-ACCEPT-TIME'N, 1, 10), YYMMDD10.);
FORMAT 'DATE'N YYMMDD10.;
'TIME'N = INPUT(SUBSTR('TRANS-ACCEPT-TIME'N, 12, 8), TIME8.);
FORMAT 'TIME'N TIME8.;
'DATETIME'N = DHMS('DATE'N, HOUR('TIME'N), MINUTE('TIME'N), SECOND('TIME'N));
FORMAT 'DATETIME'N DATETIME20.;
'TRANS-POST-PROC-TIME-NUM'N = INPUT(STRIP('TRANS-POST-PROC-TIME'N), BEST32.);
'TRANS-PROC-TIME-NUM'N = INPUT(STRIP('TRANS-PROC-TIME'N), BEST32.);
'TRANS-REC-NUM-NUM'N = INPUT(STRIP('TRANS-REC-NUM'N), BEST32.);
'TRANS-TOTAL-TIME-NUM'N = INPUT(STRIP('TRANS-TOTAL-TIME'N), BEST32.);
'TRANSACTION-ID-NUM'N = INPUT(STRIP('TRANSACTION-ID'N), BEST32.);
RUN;
/* Drop columns */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_1;
SET WORK.CLR_CALL_ACTIV_HIST_2016_0;
DROP 'CHANNEL-ID'N
'CHANNEL-OBJ-ID'N
'GROUP-ID'N
'INTERACTION-ID'N
'TRANS-ACCEPT-TIME'N
'DATE'N
'TIME'N
'TRANS-POST-PROC-TIME'N
'TRANS-PROC-TIME'N
'TRANS-REC-NUM'N
'TRANS-TOTAL-TIME'N
'TRANSACTION-ID'N
;
RUN;
/* Rename columns */
DATA WORK.CLR_CALL_ACTIV_HIST_2016_2;
SET WORK.CLR_CALL_ACTIV_HIST_2016_1;
RENAME 'CHANNEL-ID-NUM'N = 'CHANNEL-ID'n
'CHANNEL-OBJ-ID-NUM'N = 'CHANNEL-OBJ-ID'N
'GROUP-ID-NUM'N = 'GROUP-ID'N
'INTERACTION-ID-NUM'N = 'INTERACTION-ID'N
'DATETIME'N = 'TRANS-ACCEPT-TIME'N
'TRANS-POST-PROC-TIME-NUM'N = 'TRANS-POST-PROC-TIME'N
'TRANS-PROC-TIME-NUM'N = 'TRANS-PROC-TIME'N
'TRANS-REC-NUM-NUM'N = 'TRANS-REC-NUM'N
'TRANS-TOTAL-TIME-NUM'N = 'TRANS-TOTAL-TIME'N
'TRANSACTION-ID-NUM'N = 'TRANSACTION-ID'N
;
RUN;
/* Load final table */
DATA WORK.CLR_CALL_ACTIV_HIST_2016;
SET WORK.CLR_CALL_ACTIV_HIST_2016_2;
RUN; Here is my Snowflake connector code: libname NUC_LIB snow
SERVER = "xxxx"
USER = "xxxx"
PASSWORD = "xxxx"
DATABASE = "xxxx"
SCHEMA = "xxxx"
ROLE = "xxxx"
WAREHOUSE = "xxxx"
;
DATA NUC_LIB.CLR_CALL_ACTIV_HIST_2016
(
BULKLOAD = YES
BL_INTERNAL_STAGE = "user/nucleus"
BL_COMPRESS = YES
BL_DELETE_DATAFILE = YES
DBCOMMIT = 1000000
);
SET WORK.CLR_CALL_ACTIV_HIST_2016;
RUN; Essentially, I need the DATETIME columns from SAS to be DATETIME columns in Snowflake.
... View more