Preserving blank values when loading to SQL Server

[HeidiDT]

Hi

I am extracting data from a SQL Server table, loading it into SAS and performing some lookups, and then loading it into another SQL Server table in a different schema. The problem I have is that some of the character values are blank in the source table, but when SAS loads it into the target table, the blanks are loaded as NULL. I know this is because SAS treats blanks as NULLs, but is there a way to preserve the blank values when loading back into SQL Server? I.e. if the source data is NULL I want to load NULL, but if it is blank I want to load blank.

Thanks!

[Patrick]

Have a look into data set options NULLCHAR and NULLCHARVAL

[Tom]

---

@HeidiDT wrote:

Hi

I am extracting data from a SQL Server table, loading it into SAS and performing some lookups, and then loading it into another SQL Server table in a different schema. The problem I have is that some of the character values are blank in the source table, but when SAS loads it into the target table, the blanks are loaded as NULL. I know this is because SAS treats blanks as NULLs, but is there a way to preserve the blank values when loading back into SQL Server? I.e. if the source data is NULL I want to load NULL, but if it is blank I want to load blank.

Thanks!

---

You are going to have to calculate another variables in your source table to in order to know whether the original SQL table had spaces or null value in the character variable.  If the original variable was VARCHAR() instead of CHAR() then you will probably want to calculate the length.

Who would design a table/variable that needed to make a distinction between a blank character string and a "null" character string?  What real world problem does that represent?

[HeidiDT]

Thanks Tom.

The reason is mainly for auditing purposes, when the counts per field are done in the landing area a certain number of NULLs are reported, which doesn't then match with the number of NULLs in the target database. SAS reports the length of a blank and a NULL as the same thing (1 when using the length function and 0 when using lengthn), so calculating the length does not seem to be an option. 

[Patrick]

SAS doesn't have a concept of NULL and the moment you load the data from the database into SAS the NULL values in source become Blank in SAS. So it's not a reporting issue but actually what's in the data.

When you then load data from SAS into a database table then depending on your configuration (NULLCHAR) all SAS character values which are BLANK only get either ALL inserted as NULL or ALL inserted as BLANK. 

[Tom]

---

@HeidiDT wrote:

Thanks Tom.

The reason is mainly for auditing purposes, when the counts per field are done in the landing area a certain number of NULLs are reported, which doesn't then match with the number of NULLs in the target database. SAS reports the length of a blank and a NULL as the same thing (1 when using the length function and 0 when using lengthn), so calculating the length does not seem to be an option. 

---

You need to calculate the length in the remote database BEFORE pulling the data into SAS.

[JackHamilton]

The LENGTHN function returns 0 if the string is entirely blank.

There's also a SUBSTRN function, similar to SUBSTR except that it can return a zero-length result.

While looking this up in the docs, I discovered the SUBPAD function, which I would have used many times over the years if I had known of its existence.  It pays to look at the documentation occasionally.