topic Re: Read varchar(max) from SQL Server into SAS in SAS Programming

Read varchar(max) from SQL Server into SAS

avinashjha1787 — Wed, 21 Jul 2021 09:46:53 GMT

I have SQL Server table with columns of type varchar(max) and they hold values > 32767. How can i read the complete column in varchar(*) column using SAS V9 or SAS Viya ?

Re: Read varchar(max) from SQL Server into SAS

SASJedi — Wed, 21 Jul 2021 14:50:03 GMT

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

Re: Read varchar(max) from SQL Server into SAS

avinashjha1787 — Wed, 21 Jul 2021 16:45:58 GMT

I tried using Proc Casutil load statement to read data directly onto CAS
but it brings zero records for any table with varchar(max) variable.

Re: Read varchar(max) from SQL Server into SAS

SASJedi — Thu, 22 Jul 2021 12:26:29 GMT

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?