BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
shan4
Calcite | Level 5

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.

 

shan4_0-1697737127146.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
shan4
Calcite | Level 5

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

 

View solution in original post

4 REPLIES 4
SASKiwi
PROC Star

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.

Tom
Super User Tom
Super User

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.

shan4
Calcite | Level 5

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

 

SASKiwi
PROC Star

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 446 views
  • 2 likes
  • 3 in conversation