topic Re: Data format for string defined in a libname for GCP dataset in SAS Programming

Data format for string defined in a libname for GCP dataset

shan4 — Thu, 19 Oct 2023 17:41:28 GMT

I am defining a libname which pointing to a GCP bigquery table with slibref.

I noticed the string column all have a format/length of 2000 where the actual length is 15.

Is it expected ?

We also tried option scanstringcolumns=yes, it does not make any difference.

Re: Data format for string defined in a libname for GCP dataset

SASKiwi — Fri, 20 Oct 2023 00:26:23 GMT

Refer to the BigQuery SAS doc for an explanation of how SAS defines the type and length of BigQuery data read into SAS.

You need to change the MAX_CHAR_LEN option from the default 2000 value.

Re: Data format for string defined in a libname for GCP dataset

Tom — Fri, 20 Oct 2023 03:19:53 GMT

The problem is not FORMAT (or the INFORMAT) that is attached to variable. Instead it is the LENGTH that was chosen for the variables.

The problem is that BIGDATA seems to be using a VARCHAR variable type that does not include metadata about the maximum length that SAS can use to pick a smaller LENGTH to use when defining the fixed length character variable that SAS uses.

The $2000 length is probably the setting you have for the default length to use for really long strings.

You might have to do more work defining the variables if you need to pull it into SAS datasets.

Re: Data format for string defined in a libname for GCP dataset

shan4 — Fri, 20 Oct 2023 03:38:12 GMT

Thanks for your response. I think in our Big Query environment, there is DefaultStringLength = 2000 defined somewhere. This caused all string type field created with default length of 2000.

I looked at this SAS doc https://documentation.sas.com/doc/en/pgmsascdc/v_044/ds2ref/n08ihhj1d03nlnn13zv4tnp69t8l.htm

and add SCANSTRINGCOLUMNS=yes in my libname definition, the actual length of the string is populated which resolved my problem.

Thanks. Shan

Re: Data format for string defined in a libname for GCP dataset

SASKiwi — Sun, 22 Oct 2023 21:32:35 GMT

@shan4 Please update your problem as being answered in that case.