topic Re: Length of Objects tripling in SAS Programming

Length of Objects tripling

Mike_Murray — Mon, 17 Sep 2018 12:53:46 GMT

Running a pretty basic PROC SQL in SAS EG against a Sybase IQ 16.1 database our result set all of the fields are 3x the length they should be. As an example Account_Key is 8 on the warehouse but SAS EG is assigning a length of 24.

SAS EG on LINUX version 7.15 HF2 (7.100.5.6112) (64-bit) using access to ODBC to connect to our Sybase data warehouse.

Mike

Re: Length of Objects tripling

SuryaKiran — Mon, 17 Sep 2018 13:36:08 GMT

CHAR(n) in Sybase Defaults to $n. in SAS. Documentation has a not "n specifies the current value for the Adaptive Server page size." I not sure about your problem but my guess is your problem might be related to value for the Adaptive Server page size.

Data Types for Sybase

Re: Length of Objects tripling

Patrick — Mon, 17 Sep 2018 22:31:02 GMT

@Mike_Murray

I don't have any experience with Sybase but I've had similar issues when interfacing with Oracle.

The reason there was that the Oracle instance used UTF-8 encoding. Oracle allows to define character variables with length as number of characters to be stored independent of how much storage a character needs. SAS defines its lengths in bytes. A UTF-8 character can use up-to 4 bytes and though the default behavior was that when transferring data from Oracle to SAS the character lengths in SAS were 4 times what had been defined in Oracle.

There is a set of options available for the Oracle/Access engine to control how the conversion works and as we knew that we're only dealing with English letters we've amended the settings to a 1:1 conversion.

Not sure what Access engine you're using for Sybase and which encoding could create a three fold increase - but it's the place I'd be looking first searching for options for your access engine which allow you to control the character conversion.