When you try to use the SORT procedure on a Google BigQuery table, an error might occur when the following is true:
- The BigQuery table contains a DATE, DATETIME, or TIMESTAMP data type and at least one of the variables contains a date prior to 01JAN1582.
- The BULKUNLOAD=YES LIBNAME or data set option is being used.
- The OUT= option for the PROC SORT statement also references a BigQuery table.
If the BULKUNLOAD=NO LIBNAME or data set option is being used, the error might look similar to the following:
ERROR: During insert: Error: JSON: error calling MarshalJSON for type *bigquery.TableDataInsertAllRequest: JSON: error calling MarshalJSON for type *bigquery.TableDataInsertAllRequestRows: JSON: error calling MarshalJSON for type time.Time: Time.MarshalJSON: year outside of range [0,9999] : Numeric value out of range
If the BULKUNLOAD=YES LIBNAME or data set option is being used, the error might look similar to the following:
ERROR: During insert: Numeric value out of range
NOTE: The data set DBLIB.xxxx has 4 observations and 3 variables.
ERROR: Error while executing bulk load: {Location: ""; Message: "Error while reading data, error message: Could not parse '',0*-24-10 00:30:24.000032' as TIMESTAMP for field dt (position 2) starting at location 0 with message 'Could not parse '\\',0*-24-10 00:30:24.000032' as a timestamp. Required format is YYYY-MM-DD HH:MM[:SS[.SSSSSS]] or YYYY/MM/DD HH:MM[:SS[.SSSSSS]]'"; Reason: "invalid"}
With BULKUNLOAD=YES, the note incorrectly indicates that some rows were successfully inserted into the table.
The workaround is to remove the BULKUNLOAD= option. If slow read performance is a concern, add the READ_MODE=STORAGE LIBNAME or data set option.