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.
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"?
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.
The first result from google search of this issue is this thread from 5 years ago.
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 25. 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.