topic Re: SQL Passthrough creates character vars of length 32767 in SAS Programming

SQL Passthrough creates character vars of length 32767

jlh368 — Tue, 01 Jun 2021 20:40:15 GMT

Hello,

Support on an external server for the SQL driver ended. We are using in our ODBC connection with the driver SQL Server. We recreated the connection with ODBC Driver 17 for SQL Server. This reestablished our connection but the data returned for some character vars is returned at a length of 32767. I can cast the vars in the pass thru to get smaller numbers cast(var as varchar(9)) as var_name1. This seems like an extra step. What am I missing? Should I use the SAS/Access driver?

Re: SQL Passthrough creates character vars of length 32767

SASKiwi — Tue, 01 Jun 2021 21:30:34 GMT

What type are these columns in SQL Server? You can define variable lengths in SAS for these also. Also we have SAS option COMPRESS = YES set to avoid disk space blowing out.

Re: SQL Passthrough creates character vars of length 32767

jlh368 — Wed, 02 Jun 2021 12:09:49 GMT

Thanks that is a good direction to question. The SQL column is varchar(max). It is used to store multiple types of data with an associated key field. It looks like the field that is being returned was created as an integer when it should have been a character field of length 10. Instead it was stored as an integer hence the max length standard integer being returned. Thanks!

Re: SQL Passthrough creates character vars of length 32767

Tom — Wed, 02 Jun 2021 12:49:48 GMT

@jlh368 wrote:

Thanks that is a good direction to question. The SQL column is varchar(max). It is used to store multiple types of data with an associated key field. It looks like the field that is being returned was created as an integer when it should have been a character field of length 10. Instead it was stored as an integer hence the max length standard integer being returned. Thanks!

So the cause seems to be the way the variable is defined in the remote database. The variable is begin defined as length $32767 in SAS because that is the maximum length that SAS allows for a character variable.

If you are using implicit passthru you can control the SAS type created by using the DBSASTYPE= dataset option.

The second part of your message does not make any sense to me. If the variable is defined as an INTEGER in the remote database then SAS will transfer it to a numeric variable (SAS stores all numbers as 8 byte floating point numbers).