Knowledge Base

Reading Missing Values from a Data Set

In this recipe, you learn how to test for missing values by using either ADO or OLE DB. Sample code is provided for both interfaces.

Applies to: SAS Local Data Provider, SAS/SHARE Data Provider, SAS IOM Data Provider
Implement using: ADO or OLE DB


The OLE DB specification only provides for one kind of missing value for each data type. Consequently, the SAS Data Providers only support numeric missing values and character missing values. You can test for missing values (character or numeric) using either the ADO or the OLE DB interface.

For both ADO and OLE DB, the representation of missing character values is influenced by whether or not blank padding is preserved (see Padding Character Data with Blanks ).

ADO Details

ADO represents missing numeric values as a Variant data type with a value of Null, which means that the IsNull function can be used to test for missing values.

Representation of missing character values depends on whether or not blank padding is preserved (see Padding Character Data with Blanks ).

Note: If you are not sure about the state of the "SAS Preserve Trailing Blanks" property, you can use the Trim function and compare the value against the empty string "".

The following example uses the SAS IOM Data Provider to test every character and numeric value in a data set to determine if a value is missing or not. (The IOM provider is running locally, which is done by using "_LOCAL_" as the "Data Source" property value. For more information about connection properties, see Opening an ADO Connection Object.)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim f As Field

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = "sas.IOMProvider.1"
cn.Properties("Data Source") = "_LOCAL_"
rs.LockType = adLockPessimistic

' rs.LockType was set above
' Therefore, no LockType is passed to the Open call

rs.Open "sashelp.shoes", cn, adOpenDynamic, , adCmdTableDirect
While Not rs.EOF
    For Each f In rs.Fields
        If (f.Type = adVarChar Or f.Type = adVarWChar) And _
           (IsNull(f.Value) Or Trim(f.Value) = "") Then
            Debug.Print "missing character value found in column " & f.Name
        ElseIf IsNull(f.Value) Then
            Debug.Print "missing numeric value found in column " & f.Name
            Debug.Print "no missing value found"
        End If
    Next f

Trapping Errors Caused by Missing Numeric Values

Because missing numeric values are represented as a variant data type with a value of Null, you cannot use those values in operations that require numeric data types. For example, if your data set has one observation per sales region and one of the columns is the number of stores in that region, it would seem logical to write code like the following to calculate the total number of stores as an integer.

Dim TotalStores as Integer

TotalStores = 0
While Not rs.EOF
   TotalStores = TotalStores + rs!Stores

Unfortunately this code will produce a run-time error on the first missing numeric value that appears in rs!Stores. To trap the error, use Visual Basic's On Error statement. The following example will trap that run-time error and display a message to the user.

Function TotalStores() as Integer
	Dim Total as Integer
	Total = 0
	On Error GoTo Missing
	While Not rs.EOF
   		Total = Total + rs!Stores
	On Error GoTo 0
	TotalStores = Total
	Exit Function

	If Err.Number = 94 Then
   		MsgBox "Missing value encountered"
   		MsgBox "Run-time error: " & Err.Number & vbNewLine & Err.description
	End If
	Resume Next
End Function

OLE DB Details

OLE DB identifies missing numeric values with a status of DBSTATUS_S_ISNULL, which is returned in the client's row data buffer after a call to IRowset::GetData.

The representation of missing character values depends on whether or not blank padding is preserved. In the case of OLE DB, the DBPROP_SAS_BLANKPADDING property controls this behavior.

The following C++ code is an example of how to test for missing values in a single observation that is returned from IRowset::GetData. Here is a summary of the tasks that must be done before the sample code starts:

  1. Call IColumnsInfo::GetColumnInfo to get a DBCOLUMNINFO array and to determine the number of columns in the data set.
  2. Use the DBCOLUMNINFO array to create a DBBINDING array.
  3. Call IAccessor::CreateAccessor to request an accessor.
  4. Call IAccessor::GetBindings to recover the bindings for the accessor that you created.
  5. Use the bindings to determine how much space to allocate for the row data.
  6. Call IRowsetLocate::GetRowsAt or IRowset::GetNextRows to get a HROW.
  7. Use the HROW and the accessor in the call to GetData.

Note: The exact steps showing how to get a row of data from an OLE DB provider can be found in the OLE DB specification.

hr = pIRowset->GetData(hRow, hAccessor, pRowData)
if (FAILED(hr)){ /* an error occurred */ }
for (int i = 0; i < cNumColumns; i++)
	pCol = (COLUMNDATA*)(pRowData + pBindings[i].obLength);
	if (pBindings[i].wType == DBTYPE_R8)
		if (pCol->dwStatus == DBSTATUS_S_ISNULL)
			//This is a missing numeric value
			//This is a non-missing numeric value
	else if (pBindings[i].wType == DBTYPE_STR || pBindings[i].wType == DBTYPE_WSTR)
		DWORD j;
		// Loop through each character in the data member to
		// see if it contains a string of blanks or if it has a length of zero 
		for (j = 0;
			j < pCol->dwLength &&
			(char*)pCol->bData[j] &&
			(char*)pCol->bData[j] == ' ';
		if (j == pCol->dwLength)
			//This is a missing character value
			//This is a non-missing character value
		// An unexpected type was returned

See Also:

Writing Missing Values to a Data Set