BookmarkSubscribeRSS Feed
Anotherdream
Quartz | Level 8


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

6 REPLIES 6
LinusH
Tourmaline | Level 20

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
LinusH
Tourmaline | Level 20

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
Anotherdream
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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?

Anotherdream
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 7481 views
  • 0 likes
  • 3 in conversation