Creating sql table with varchar(Max)

Reply
Super Contributor
Posts: 418

Creating sql table with varchar(Max)


Hello ALl. Does anyone know of a way for SAS to use proc sql to create a table in sql server using varchar(max)?

Basically I have a dataset that has a mix of character and numeric variables, of which I need the character values to be able to be created using varchar(max) instead of varchar(8000) since some of my variables are much larger than varchar(8000). It is user inputted comment data that cannot be split up sadly.

Is there an option in the system to allow for this to happen? The only way I've been able to get around this is to use sql to create the table  (in actual sql) and then insert into the table. However this presents a unique issue because this tables variables can and will change on a regular basis, and I need this to be as dynamic as possible. My "solution" was to write a macro using the contents procedure to derived the sql create table statement, however I feel this is very inefficient and there must be a way to do this with base options.

Thanks!

Let me know if anyone needs a deeper explanation, or example data to further explain what I am trying to do!

Brandon

Super User
Posts: 5,257

Re: Creating sql table with varchar(Max)

I think best practice is to do a create table within SQL Server, at least if you ask a SQL Server DBA.

But I can't see any problem with that technique, since you are writing a macro...?

Otherwise, if you let SAS/ACCESS libname do the table creation, it will use SAS formats to determine the length of the RDBMS columns, hence, apply the appropriate format to the SAS table that you are pushing down to SQL Server.

Data never sleeps
Super User
Posts: 5,257

Re: Creating sql table with varchar(Max)

Sorry, is (MAX) a key-word in SQL Server? If so, I don't believe you can have SAS to generate that for you, in which case you need to to do a create table using explicit SQL pass-thru.

The maximum length in SAS for char columns is 32K, that goes both for SAS tables as well for tables accessed via SAS/ACCESS.

Data never sleeps
Super Contributor
Posts: 418

Re: Creating sql table with varchar(Max)

Hello Linus. Your second comment is correct. Max is a keyword in sql server that sas cannot replicate in the SAS/ACCESS libname statement, hence my troubles.

This is why I have been doing a macro to use explicit pass through in order to create the table, I just really dislike doing this so was hoping there was an option that would default all varchar(8000+) into varchar(max) in the SAS/ACCESS libname ability.

Super User
Super User
Posts: 6,500

Re: Creating sql table with varchar(Max)

What are you actually trying to do?  In SAS the limit on the length of a character variable is 32,767.  So why not just define you variables with this length?

What would VARCHAR(MAX) do differently than VARCHAR(32767) other than pick a different maximum value?

Super Contributor
Posts: 418

Re: Creating sql table with varchar(Max)

Oh i'm sorry I should have explained how sql server works with this. In sql server you CANNOT define a varchar(32767) as the limit is varchar(8000). So anything above 8,000 must be defined as a MAX (basically sql server can only store up to 8,000 bytes on a single page storage, and anything over that is stored off-page, which can be allowed when you use varchar(MAX).

Furthermore if you have a table and try to run the following code.

Libname clean odbc='odbc_myserver' bulkload=yes dbmax_text=32767;

proc sql;

create table clean.NewSqlTable as

select * from Tablewithlongcomments;

quit;

run;

THen sas throws the error "Database error: The size  (13,838)  given to the column X exceeds the maximum allowed for any data type  (8,000).

So basically I have to dynamically convert all of my variables > 8000 to varchar max, and also convert all of my numeric and date values to the correct datatypes, and then run a pass through function to define the table with varchar(max) and then insert into it.

But what I am trying to do is create a sql table from SAS, with varchar values > 8,000 dynamically, withing having to use a pass through to sql server to run a create table statement. I am not sure this is possible however.

Super User
Super User
Posts: 6,500

Re: Creating sql table with varchar(Max)

Try using the DBTYPE dataset option.

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

libname sqlsrv ..... ;

data sqlsrv.test1 (dbtype=(longchar='varchar(max)')) ;

  length longchar $32767 ;

  longchar='test';

run;

Ask a Question
Discussion stats
  • 6 replies
  • 2518 views
  • 0 likes
  • 3 in conversation