Help using Base SAS procedures

Greenplum gpfdist problem with bigint values

Reply
Super Contributor
Posts: 316

Greenplum gpfdist problem with bigint values

Hi,

I have a sas table with numeric fields and appending it to a Greenplum table using bulkload thru gpfdist. However, I am getting an error when there are values greater than 1 billion eventhough the fields in greenplum are declared bigint.

Is there an option to allow loading such large numbers to be loaded in Greenplum when using bulkload option. Below is a sample script of what I am currently using.

proc append base=gplib.tgttable (BULKLOAD=YES BL_DATAFILE="<file>" BL_HOST=<host> BL_PORT=<port> BL_ENCODING='LATIN1' BL_FORMAT=CSV BL_NULL='\NULL\')

data=saslib.srctable force;

run;

Thanks!

Valued Guide
Posts: 3,208

Re: Greenplum gpfdist problem with bigint values

Bigint is not a type supported by SAS (classic). It is converted to floating.  See:
- SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

- https://support.sas.com/techsup/technote/ts654.pdf  39831 - Problem occurs with BIGINT data types within SAS® 9.2

There is a problem having more than 14 digits.... they are at best becoming randomly. 

To overcome those type conversions SAS has new appraoches

- SAS(R) 9.4 FedSQL Language Reference, Third Edition

- Base SAS(R) 9.4 Procedures Guide, Third Edition (Proc Ds2)

These are for accessing data out of DBMS and using in database technologies

Dow you want to use the bigint type for some reason my advice would be first loading it to an other type of field and have it converted later in Greenplum.
It is the common misperception, it would be better defined as:

- measurements With a certain precision (floating)

- classifications (character but possible restricted to use the numeric ones as a constraint)

- Time  (date datetime time is UTC or localized   if you like star-time)  

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 1 reply
  • 184 views
  • 3 likes
  • 2 in conversation