topic Re: Convert BIGINT data type to Character data type in SAS Programming

Convert BIGINT data type to Character data type

PhatRam33 — Wed, 25 Apr 2018 21:20:22 GMT

Hello, I'm trying to convert a variable that is stored as a BIGINT from a Neteeza database to a character data type and have not had much luck in doing so. I'm trying to convert this data type in a join without using a pass-through query and was wondering if this is possible.

Table1 as t1 Inner Join Table2 as t2 on t1.ID = put(t2.ID,20.)

Have to convert to a character with a length of 20. Received errors with this piece of code.

Thanks in advance.

Re: Convert BIGINT data type to Character data type

Patrick — Wed, 25 Apr 2018 22:41:27 GMT

@PhatRam33

Please post the relevant section from your log or at least the full error message.

If you say pass-through: Implicit or explicit?

If explicit then you need to use Netezza functions - to_char()

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_conversion_funcs.html

If implicit then be aware that the SAS put() function gets eventually not pushed to the data base meaning that SAS will pull all the data first into SAS before applying the join condition.

According to docu SAS can deal with Bigint and the put() function as such should work. So please show us some log so we can better understand what's going wrong.

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p07ge3f7fvfh5ln1msgdpqhbeh31.htm&docsetVersion=9.4&locale=en

Re: Convert BIGINT data type to Character data type

PhatRam33 — Thu, 03 May 2018 15:28:04 GMT

It is an implicit query. Tried

dbsastype=(IM.Field1='char(20)')

Still receiving same error.

Re: Convert BIGINT data type to Character data type

Patrick — Thu, 03 May 2018 20:54:49 GMT

@PhatRam33

What's the error? Which SAS version are you using? Which library engine are you using?

Could it be related to: http://support.sas.com/kb/38/707.html ?

Re: Convert BIGINT data type to Character data type

PhatRam33 — Fri, 04 May 2018 14:43:34 GMT

It is the numeric value out of range error. SAS EG 6.1 on UNIX. I think I may just stick with an explicit query as it works there.