What do you want to DO with the data? If it's just read it in, process, and write back to the database, then use PROC FedSQL for your SQL queries and PROC DS2 for your data programs. Those will work in SAS 9.4, the Viya Compute Server, and in CAS.
If you want to write the data out to a SAS dataset in SAS 9.4 or on the Viya Compute Server, read it in DS2, write the data program code to split it up into several SAS character variables (CHAR), and save results to a SAS library. If you have access to CAS, you can load it right into an in-memory CAS table - CAS VARCHAR supports up to 536,870,911 characters (in UTF-8 encoding).
May the SAS be with you!
Mark
If you are using a LIBNAME to access your DBMS table and loading the data to CAS with code like this, you are performing a client-side load:
libname db sqlsrv <connect info>;
proc casutil;
load data=db.mytable casout="myCASTable" outcaslib="casuser";
run;
As you can see, in a client-side load the data is first processed by the SAS/ACCESS LIBNAME engine, and so your large VARCHAR column is truncated before you get a chance to load it.
If your CAS server has the associated data connector configured for your DBMS, you can do a server-side load instead. The code would look more like this:
caslib sqlDB desc='Microsoft SQL Server Caslib'
dataSource=(srctype='sqlserver'
username='user1'
password='myPwd'
sqlserver_dsn="configured-DSN-name"
catalog='*');
proc casutil;
load casdata="myTable" incaslib="sqlDB"
casout="myCASTable" outcaslib="casuser";
run;
This loads the table directly from SQL Server to CAS using the CAS data connector:
As you can see here, the LIBANME engine is not involved in this process, so the data is not converted to SAS numeric / fixed-width character before loading into CAS.
So, are you doing a client-side load or a server-side load from SQL Server?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: