topic Re: SAS DI Truncates Characters that have length greater than 2000 in SAS Programming

SAS DI Truncates Characters that have length greater than 2000

vfarmak — Thu, 10 Dec 2020 00:34:24 GMT

Greeting everyone,

I encounter the following problem:

I have a MongoDB database which reads the data through CData driver. The driver works fine and stores json arrays in SAS Dataset. By default the maximum number of characters that a character column accepts is 2000. Is there any configuration in which I could increase the number of characters stored in the column?

The version of SAS DI that I am using is the following:

Version: 4.904

Thank you in advance,

Vasilios

Re: SAS DI Truncates Characters that have length greater than 2000

ChrisNZ — Thu, 10 Dec 2020 09:10:47 GMT

Your process is unclear. Do your MongoDB fields contain JSON strings?

Re: SAS DI Truncates Characters that have length greater than 2000

LinusH — Thu, 10 Dec 2020 09:13:05 GMT

"By default the maximum number of characters that a character column accepts is 2000"

Where have you discovered hgtis?

Pretty sure that SAS DI don't have any limitations at this size.

When you say it works fine and it's stored in a SAS dataset, does this dataset have appriopriate char lengths?

Please share more details on this connection and how you integrate using DI Studio.

Re: SAS DI Truncates Characters that have length greater than 2000

vfarmak — Thu, 10 Dec 2020 09:21:53 GMT

@ChrisNZ Yes, since MongoDB is a document database it contains json strings.

To clarify in more detail, we store json strings that are read from mongodb through a driver (CData Driver) in sas datasets via SAS DI.

Columns that contain array of documents however are being stored in SAS datasets and are not "flattened". The driver reads the json documents and assigns them in columns that have length 2000 size.

The question is whether we could increase the size of the columns or not.

Re: SAS DI Truncates Characters that have length greater than 2000

vfarmak — Thu, 10 Dec 2020 09:24:43 GMT

@LinusH I have observed this through the sas datasets that the driver creates. So I was wondering if there is configuration that could actual increase the size of the columns in SAS DI.

Re: SAS DI Truncates Characters that have length greater than 2000

ChrisNZ — Thu, 10 Dec 2020 09:34:28 GMT

This is a CData question, not SAS question.

Their documentation states

DefaultColumnSize

Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.

Look there to see how to configure that driver.

Re: SAS DI Truncates Characters that have length greater than 2000

vfarmak — Sun, 13 Dec 2020 15:54:28 GMT

@ChrisNZ Indeed this is stated in the site.
I couldn't find the setting and I have contacted them. They stated that there is a field in the setting of the driver that I should declare the setting explicitly.

Re: SAS DI Truncates Characters that have length greater than 2000

ChrisNZ — Mon, 14 Dec 2020 20:26:55 GMT

Send a screenshot of the settings if you can, or the text of the tech support explanations, so the next person who encounters the issue can fix it.

Re: SAS DI Truncates Characters that have length greater than 2000

vfarmak — Mon, 14 Dec 2020 21:02:30 GMT

@ChrisNZ As you can see in the screenshot, in the Misc section of the driver, you have to set the DefaultColumnSize equal to 8000.

However, they suggested me to download another version of the driver (since the manual restart of DI or the re-execution of the ODBC Data Source did not came in handy). I haven't tried it yet (we need to perform that change in an isolated environment and not in production).

For the record, they proposed me this version of the program which can be found in the below Url:

http://cdatabuilds.s3.amazonaws.com/support/DGDF-A_7649.exe

Re: SAS DI Truncates Characters that have length greater than 2000

ChrisNZ — Tue, 15 Dec 2020 00:50:25 GMT

Very informative! Thank you!

Re: SAS DI Truncates Characters that have length greater than 2000

vfarmak — Tue, 15 Dec 2020 01:28:54 GMT

You're welcome!