Bulk loading data that contains missing values from SAS® into the Greenplum database using SAS/ACCESS Interface to Greenplum might result in an error similar to the following:
ERROR: [SAS ACCESS to Greenplum][ODBC 20101 driver][20101]ERROR: invalid input syntax for type double precision: " " (seg52 slice1 sdw9:40004 pid=15097)(Detail External table ext_grptest, line 3 of gpfdist://ci64xsas.unx.sas.com:8081/BL_GRPTEST_7.dat, column A; File cdbdisp.c;Line 1477;Routine cdbdisp_finishCommand;) : [SAS ACCESS to Greenplum] [ODBC 20101 driver][20101]Failed transaction. The currenttransaction rolled back. Load error code: 0
This error can occur when there is missing data and you are using the CSV format to bulk load data into Greenplum, as shown below:
data test; input A comma10.2; datalines; 11.22 22.33 . 33.44 run; data gplib.grptest (bulkload=yes bl_delimiter="E'\24'" bl_quote="e'\1'" bl_format='csv' bl_host='greenplum.company.com' bl_port='8081' bl_protocol='gpfdist' bl_delete_datafile=yes) ; set test; run;
Click the Hot Fix tab in this note to access the hot fix this issue.
The hot fix enables the use of CSV format and missing values.
Product Family | Product | System | Product Release | SAS Release | ||
Reported | Fixed* | Reported | Fixed* | |||
SAS System | SAS/ACCESS Interface to Greenplum | Microsoft Windows Server 2012 R2 Datacenter | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 |
Microsoft Windows Server 2012 Datacenter | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows Server 2008 for x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows Server 2008 R2 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows Server 2008 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8.1 Pro 32-bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8.1 Pro | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8.1 Enterprise x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8 Pro x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8 Pro 32-bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8 Enterprise x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows 8 Enterprise 32-bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft® Windows® for x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows Server 2012 R2 Std | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Microsoft Windows Server 2012 Std | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Enterprise 32 bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Enterprise x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Home Premium 32 bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Home Premium x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Professional 32 bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Professional x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Ultimate 32 bit | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Windows 7 Ultimate x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
64-bit Enabled AIX | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
64-bit Enabled Solaris | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
HP-UX IPF | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Linux for x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 | ||
Solaris for x64 | 9.4_M2 | 9.4_M3 | 9.4 TS1M2 | 9.4 TS1M3 |