topic Re: Enforcing a blank value as being NULL so that load to Oracle fails. in SAS Programming

Enforcing a blank value as being NULL so that load to Oracle fails.

KirstenM — Thu, 15 Jun 2023 09:54:31 GMT

I have created a table in Oracle with a column FIELD1 which is defined as VARCHAR2(20 BYTE) NOT NULL.

Users are able to append data to this table using SAS Enterprise Guide.

I need to prevent them from appending data where FIELD1 is blank.

However if the users make FIELD1 ='' in SAS (ie. blank or empty) they are able to append to the Oracle table so NOT NULL is not doing the trick.

Appears that SAS interprets = '' as a character field with a length of 1 rather than a null.

Re: Enforcing a blank value as being NULL so that load to Oracle fails.

LinusH — Thu, 15 Jun 2023 11:00:11 GMT

You can try the NULLCHAR=YES option:

SAS Help Center: NULLCHAR= Data Set Option

Re: Enforcing a blank value as being NULL so that load to Oracle fails.

Tom — Thu, 15 Jun 2023 12:59:21 GMT

In SAS there is no difference between an all blank character variable and missing value. Character strings are fixed length.

So if you don't want them to insert blanks then tell them not to set the value to blanks.

I question the value of allowing the Oracle table to accept a value that is blank anyway as you seem to want to exclude missing values. What is the difference between an all blank value and a null that you are trying to detect? How are you going to tell the difference between a character string that is only blanks and one that is "empty"?

Re: Enforcing a blank value as being NULL so that load to Oracle fails.

Tom — Thu, 15 Jun 2023 13:03:30 GMT

SAS variables are fixed length and padded with spaces. So assigning ' ' or '' to a character variable will result in all spaces.

You might try using the TRIMN() function. That will return a completely empty string when the value only contains spaces.

insert into myora.mytable select id,trimn(name),age from have.

Re: Enforcing a blank value as being NULL so that load to Oracle fails.

Tom — Thu, 15 Jun 2023 13:38:28 GMT

The first result from google search of this issue is this thread from 5 years ago.

https://communities.sas.com/t5/SAS-Data-Management/Insert-SAS-Character-Blank-as-Oracle-NULL/td-p/503632