topic Re: Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake in SAS Programming

Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

u787bruw — Mon, 10 Jul 2023 14:54:17 GMT

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.

Re: Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

SASKiwi — Mon, 10 Jul 2023 19:58:20 GMT

If you don't use the BULKLOAD option do DATETIMEs work?

Re: Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

mkiran — Tue, 21 May 2024 18:49:30 GMT

I also have the same issue while loading with bulkload options (regular load doesn't have this problem)

@u787bruw : can you suggest how did you overcome this error?.

Re: Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

SASKiwi — Tue, 21 May 2024 19:59:59 GMT

@mkiran I suspect you might be getting this issue: https://support.sas.com/kb/69/628.html

Follow the instructions in the SAS Note to see if it helps.

Re: Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

mkiran — Thu, 23 May 2024 18:26:12 GMT

@SASKiwi : No, this one did not help. We are on windows environment and have SAS 9.4 M8.

I see the below error while loading the data using BULKLOAD options

Libname statement:

LIBNAME RAW SASIOSNF DSN=RAW SCHEMA=SAS USER=SVC_SAS_USER PASSWORD="XXXXXXXXXXXX"
bulkload=yes bulkunload=yes bl_internal_stage="user/testfolder" ;

ERROR: Error executing COPY command: Numeric value '3êÿW½ØXc]Ó'8§‹Éƒ„S³ñ' is not recognized File
'@~/testfolder/SASSNBL_E28F343A-C79B-4FEB-A5E1-6C68E5A08B42-01.dat', line 5, character 1 Row 5, column
"DATASET "["YEAR":1] 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.

Data load is fine with proc sql if I don't use BULKLOAD options in the LIBNAME but it is taking lot of time with the volume of data I have.

Re: Error When Trying to Load 'DATETIME' Columns from SAS to Snowflake

SASKiwi — Thu, 23 May 2024 20:12:25 GMT

@mkiran - The problem documented also occurs on Windows and like it says may require additional bulk load options. I recommend you open a track with Tech Support if you haven't already.

BTW, as SAS explains it is using the COPY INTO statements for bulk loading. If you look up the Snowflake COPY INTO there is a DATE_FORMAT option that may help in your case: