topic Re: Writing to SQL Server Converts Empty Strings To NULL in SAS Enterprise Guide

Writing to SQL Server Converts Empty Strings To NULL

Placeholder — Mon, 27 Sep 2021 16:08:27 GMT

I am attempting to perform an update statement via proc sql on an existing table stored in SQL Server. The table I am writing to does not allow null values in any fields but has numerous records with blank values. When I attempt to update some records, I receive an error that the fields do not accept null values. Some fields in the table I am updating from have empty strings, but no null values. I even have forced them to be empty strings by performing a statement like "CASE WHEN col1 is null then '' else col1 end". When I look at the table I am updating from in another program (Advanced Query Tool), I still see null values where empty strings should be, whereas the table I am updating to shows empty strings.

What option do I need to change to get SAS to write empty strings instead of nulls to a SQL Server table?

AQT shows (null)SAS EG shows empty string

Re: Writing to SQL Server Converts Empty Strings To NULL

Tom — Mon, 27 Sep 2021 16:26:08 GMT

I suspect the answer is the same as it was in 2014.

https://communities.sas.com/t5/General-SAS-Programming/Getting-Null-string-into-Sql-Server-database/td-p/180324

But the link to the SAS documentation in that answer is broken.

Search:

https://www.google.com/search?q=%40sas.com+nullchar%3Dno

NULLCHAR=

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n1hmopc3y74oy2n1svhu03pfoiy9.htm

NULLCHARVAL=

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1j4yi0qpmf6wfn1vfqp3wudt0pu.htm

Re: Writing to SQL Server Converts Empty Strings To NULL

Placeholder — Mon, 27 Sep 2021 17:03:46 GMT

Thanks for the links. It appears both options are valid when using the proc step, but when I attempt to use either one in my code, I receive the following error:

29 proc sql nullcharval=no;
___________
22
202
ERROR: Entry NO.TRANTAB not found in catalog SASHELP.LOCALE.

29 proc sql nullchar=no;
________
22
202
ERROR: Entry NO.TRANTAB not found in catalog SASHELP.LOCALE.

Re: Writing to SQL Server Converts Empty Strings To NULL

Tom — Mon, 27 Sep 2021 17:30:06 GMT

Why did you try to add those to the PROC SQL statement?

Aren't those either connection options

libname mylib sqlserver ... nullchar=no ;

or dataset options?

proc sql;
create table mylib.mytable(nullchar=no) as
  select *,' ' as empty_var
  from sashelp.class
;
quit;