Reading Special Numeric Missing Values from a Data Set

Goal

You want to read the special numeric missing value indicator in SAS data sets.

This recipe applies to the local provider. Sample code for ADO is provided. For OLE DB, SAS recommends using the DBPROP_SAS_MISSING_VALUES property instead.

Reading Special Numeric Missing Values

The local provider provides ADO rowset property “SAS Get Missing Values Grid” so that the local provider can return results that indicate the special numeric missing values. SAS supports special missing numeric values to represent different categories of missing data. For more information about special numeric missing values, see “Missing Values” in SAS Language Reference: Concepts.

In order to return the special missing numeric value, when this property is set to True, the provider returns a grid with values that are the opposite of the standard data. Numeric fields with special numeric missing values normally return Null. When this property is set to True, those fields return a value that represents the special numeric missing value.

// This sample uses C# syntax
//
// obConnection is an ADODB.Connection object and
// obRecordset is an ADODB.Recordset object
//
try
{
    string connectionString = "Provider=sas.LocalProvider; Data Source=\"c:\\data\"";
    obConnection.Open(connectionString, "", "", 
        (int)ADODB.ConnectOptionEnum.adConnectUnspecified);

    obRecordset.ActiveConnection = obConnection;
    obRecordset.Properties["SAS Get Missing Values Grid"].Value = true;
    obRecordset.Open(dataSetName, System.Type.Missing, 
        CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, 
        (int)CommandTypeEnum.adCmdTableDirect);
} 
catch (Exception ex) 
{
    if (obConnection.Errors.Count > 0)
    {
        foreach (ADODB.Error e in obConnection.Errors)
            Console.WriteLine(e.Description);
    } 
    else 
    {
        Console.WriteLine(ex.toString());
    }
}

Consider the following table that represents a SAS data set. It has a character variable and a numeric variable. The second observation has the standard SAS missing value. The third and fourth observations use the special numeric missing variables.

Input Table with Special Numeric Missing Values

CharacterVariable	NumericVariable
FirstObservation	10
SecondObservation	.
ThirdObservation	.A
FourthObservation	._

When the “SAS Get Missing Values Grid” property is set to True and the data set is read, the results resemble the following table. All character variables are set to missing. Non-missing numeric variables are set to missing. The fields with special numeric missing values in the original data set return values to represent the missing value.

Result Recordset with “SAS Get Missing Values Grid” Set to True

CharacterVariable	NumericVariable

	32
	33
	16

The following table shows the mapping from the special numeric missing value to the value that is returned in the record set.

Special Numeric Missing Value Representation

Missing Value	Returned Value
.	32
A	33
B	34
C	35
D	36
E	37
F	38
G	39
H	40
I	41
J	42
K	43
L	44
M	45
N	46
O	47
P	48
Q	49
R	50
S	51
T	52
U	53
V	54
W	55
X	56
Y	57
Z	58
_	16

Missing Value	Returned Value
.	32
A	33
B	34
C	35
D	36
E	37
F	38
G	39
H	40
I	41
J	42
K	43
L	44
M	45
N	46
O	47
P	48
Q	49
R	50
S	51
T	52
U	53
V	54
W	55
X	56
Y	57
Z	58
_	16

Missing Value	Returned Value
.	32
A	33
B	34
C	35
D	36
E	37
F	38
G	39
H	40
I	41
J	42
K	43
L	44
M	45
N	46
O	47
P	48
Q	49
R	50
S	51
T	52
U	53
V	54
W	55
X	56
Y	57
Z	58
_	16

Missing Value	Returned Value
.	32
A	33
B	34
C	35
D	36
E	37
F	38
G	39
H	40
I	41
J	42
K	43
L	44
M	45
N	46
O	47
P	48
Q	49
R	50
S	51
T	52
U	53
V	54
W	55
X	56
Y	57
Z	58
_	16