SAS column truncating ntext sql server field

Reply
Occasional Contributor
Posts: 11

SAS column truncating ntext sql server field

I have a SQL server field that is 4000 characters long but when the library is created the SAS column is defaulted to 1024. If i edit the SAS table in the meta data and change it to 4000 it still seems to be truncating the data. Im sure there is a small change to make this occur but i am lost on how to implement it.

any ideas?

Super User
Posts: 5,383

Re: SAS column truncating ntext sql server field

If it's the libname engine that truncates the field, it doesn't matter what you register in metadata. A PROC CONTENTS reveals how the libname maps the column.

Exactly what datatype do you have in SQL Server? Are you using OLEDB or ODBC?

Data never sleeps
Super User
Posts: 7,412

Re: SAS column truncating ntext sql server field

If you connect via ODBC, you will have a limitation there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

Re: SAS column truncating ntext sql server field

Yes its ODBC so i am now researching the limitations of that driver hopefully there is another way to get get the entire data set.

Super User
Posts: 7,412

Re: SAS column truncating ntext sql server field

The most stable method to extract data from a database (and which we use exclusively) is to unload from the DB into a flat file and read that with a simple data step.

No installation and maintenance of DB clients or ODBC interfaces, files can be transferred across any network topology, and no installing/licensing of SAS modules.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,233

Re: SAS column truncating ntext sql server field

Have you tried the DBMAX_TEXT = option yet? Your problem matches exactly to the default value of 1024 for this option.

Super User
Posts: 3,233

Re: SAS column truncating ntext sql server field

You need to set the DBMAX_TEXT= option. The default value is 1024.


You can do this on a LIBNAME statement or CONNECTION statement that connects to your database:


SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

Respected Advisor
Posts: 4,132

Re: SAS column truncating ntext sql server field

Others have already pointed you to DBMAX_TEXT.

"If i edit the SAS table in the meta data and change it to 4000 it still seems to be truncating the data."

Once the physical table has been created changing SAS Metadata won't help. Delete the physical SAS table and make sure that in your code the SAS Metadata definition is used to create the table - the re-run the code. Now the physical table should be in synch with your Metadata.


Ask a Question
Discussion stats
  • 7 replies
  • 336 views
  • 0 likes
  • 5 in conversation