Reading and Writing NULL and Missing Values

Goal

You want to read and write null and missing values.

Note: SAS uses libraries to organize collections of tables. In most cases, when a table name is used in an SQL string, the table name must be prefixed by a library name. If the SAS Work library is being used, the prefix is not needed. The examples in this recipe assume that the Work library is being used. For more information about SAS libraries, see What Are SAS Libraries?.

Implementation

What Are NULL and Missing Values?

The SAS/SHARE server and the SAS Workspace Server do not explicitly support ANSI SQL NULL values. Instead, they support a SAS concept called missing values. Although ANSI SQL NULL values are sometimes equivalent to SAS missing values, the two concepts are not exactly the same. Here are some of the differences:

Users can specify many types of SAS missing values for numeric data. An ANSI SQL NULL represents nonexistent data in only one way.
If a NULL value is written to a character type column, both the SAS/SHARE driver and the IOM driver interpret the value as a SAS missing value and return FALSE for wasNull.

Note: If a numeric type is set to NULL, wasNull returns TRUE as expected.

Note: For more information about SAS missing values, see “Definition of Missing Values” in SAS Language Reference: Concepts.

Writing SQL NULL Values

The following code creates a sample table named books and inserts two SQL NULL values. The SAS/SHARE server and the SAS Workspace Server store these types of values as SAS missing values.

stmt.executeUpdate("CREATE TABLE books (c1 varchar(32), c2 double precision)");
stmt.executeUpdate("INSERT INTO books VALUES(null, null)");

The following code stores SQL NULL values by using a prepared statement.

stmt.executeUpdate("CREATE TABLE books (c1 varchar(32), c2 double precision)");
PreparedStatement ps = connection.prepareStatement("INSERT INTO books VALUES(?,?)");
ps.setNull(1, Types.VARCHAR);
ps.setNull(2, Types.DOUBLE);
ps.executeUpdate();

Writing SAS Missing Values

The following code stores SAS missing values. Note that there is little reason to write missing values to character columns because writing null values is more portable. Writing missing values to numeric type columns in SAS data sets might be appropriate if the data sets are used for reporting and the type of missing value is important.

stmt.executeUpdate("CREATE TABLE books (c1 varchar(32), c2 double)");
stmt.executeUpdate("INSERT INTO books VALUES(' ', .a)"); /* NOTE: just .a */

The following code stores missing values by using a prepared statement.

stmt.executeUpdate("CREATE TABLE books (c1 varchar(32), c2 double)");
PreparedStatement ps = connection.prepareStatement("INSERT INTO books VALUES(?, ?");
ps.setString(1, " ");
ps.setObject(2, ".a", Types.DOUBLE);
ps.executeUpdate();

Missing character values are represented by a single blank. Numeric values can have special missing value representations. A special missing value enables you to represent different categories of missing data by using the letters A-Z or an underscore.

Note: For more information about special missing values, see “Special Missing Values” in SAS Language Reference: Concepts.

Reading Null and Missing Values

The following table describes the values that are returned by the SAS drivers depending on the server, column type, and call.

Values That Are Returned by the SAS Drivers for JDBC Depending on the Server, Column Type, and Call

Call	SAS/SHARE Driver	IOM Driver
Strings stored as NULL
getString, getObject	" "1	" "1
wasNull	false2	false2
Strings stored as missing
getString, getObject	" "	" "
wasNull	false	false
Numeric stored as NULL
getString, getObject	null	null
getDouble, getFloat, getInt, and so on	0.0	0.0
wasNull	true	true
Numeric stored as missing
getString, getObject	null	null
getDouble, getFloat, getInt, and so on	0.0	0.0
wasNull	true	true
1The SAS/SHARE and IOM drivers return the nonstandard empty string " ". This empty string is padded with spaces to the size of the column width.
2The SAS/SHARE and IOM drivers return false. Some users might not expect false as a return value.

Missing Values and SAS SQL

As indicated in the previous sections, the Java method wasNull returns true against any numeric type column that holds an ANSI SQL NULL value or a SAS missing value. Also indicated in the previous sections, wasNull unexpectedly returns false against character type columns when reading SAS data sets. If there is a need to detect null values in character columns under these conditions, you can use the SAS SQL "IS [NOT] MISSING" extension to the SQL language or you can trim the value and test against " ".

create table books (c1 varchar(32), c2 double precision);
insert into books values (null, null);
select count(*) from books where trim(c1) = " "; /* returns 1 */
select count(*) from books where c1 is missing;  /* also returns 1 */