BookmarkSubscribeRSS Feed
u787bruw
Calcite | Level 5

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.

5 REPLIES 5
SASKiwi
PROC Star

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

mkiran
Quartz | Level 8

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?.

SASKiwi
PROC Star

@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.

 

mkiran
Quartz | Level 8

@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.

SASKiwi
PROC Star

@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:

SASKiwi_0-1716495128426.png

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 488 views
  • 0 likes
  • 3 in conversation