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.
Note: In order to provide greater interoperability with third party clients such as Microsoft Excel, Schema rowsets will return missing character values as NULL values. The IsNull function will also return true for missing character values in these rowsets. Always check the type of the column to determine if the value is a missing character or a missing numeric.
Note: In order to provide greater interoperability with third party clients such as Microsoft Excel, Schema rowsets will return missing character values as NULL values. The dwStatus flag will also be set to DBSTATUS_S_ISNULL for missing character values in these rowsets. Always check the type of the column to determine if the value is a missing character or a missing numeric.
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 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_"
cn.Open
rs.LockType = adLockPessimistic
' rs.LockType was set above
' Therefore, no LockType is passed to the Open call
rs.Open "sashelp.shoes", cn, adOpenDynamic, , adCmdTableDirect
rs.MoveFirst
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
Else
Debug.Print "no missing value found"
End If
Next f
rs.MoveNext
Wend
rs.Close
cn.Close
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 rs.MoveNext Wend
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 rs.MoveNext Wend On Error GoTo 0 TotalStores = Total Exit Function Missing: If Err.Number = 94 Then MsgBox "Missing value encountered" Else MsgBox "Run-time error: " & Err.Number & vbNewLine & Err.description End If Resume Next End Function
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:
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
}
else
{
//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] == ' ';
j++);
if (j == pCol->dwLength)
{
//This is a missing character value
}
else
{
//This is a non-missing character value
}
}
else
{
// An unexpected type was returned
}
}