Managing File Formats with the Local Provider

Goal

You want your application to specify which SAS file format to use when it accesses a data source. You also want to know how to access different file formats simultaneously, and what happens if your application does not specify a file format.

This recipe applies to the local provider. This recipe includes sample code for ADO and OLE DB.

ADO Implementation

Specifying a File Format

To specify which file format that the local provider should use to access a data source, set the "SAS File Format" property on the Connection object. You can use the following property values:

"V9" (the default) for SAS 9 and earlier (also valid for SAS 8 and SAS 7 data sets)
"V8" for SAS 8 or SAS 7 data sets
"V7" for SAS 7 or SAS 8 data sets
"V6" for SAS 6 data sets
"XPT" for SAS 5 transport files

For example, the following Visual Basic code could be used to access a SAS Version 6 data set named HRdata that is stored in a directory named c:\v6data.

Dim obConnection As New ADODB.Connection
Dim obRecordset As New ADODB.Recordset 

obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = "c:\v6data"
obConnection.Properties("SAS File Format") = "V6"
obConnection.Open

obRecordset.Open "HRdata", obConnection, adOpenStatic, adLockReadOnly, adCmdTableDirect

The code in the next example can read a SAS data set named TestDs1 from a Version 5 SAS transport file named xport1.dat:

Dim obConnection As New ADODB.Connection
Dim obRecordset As New ADODB.Recordset 

obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = "c:\xptdata\xport1.dat"
obConnection.Properties("SAS File Format") = "XPT"
obConnection.Open

obRecordset.CursorType = adOpenStatic
obRecordset.LockType = adLockReadOnly
obRecordset.Open "TestDs1", obConnection, adOpenStatic, adLockReadOnly, adCmdTableDirect

Accessing Different File Formats Simultaneously

You cannot simultaneously access different file formats by using the same Connection object. If you need simultaneous access, do one of the following tasks:

Store the data sets in different directories.
Create multiple Connection objects that point to the directory that contains the mixed format data sets. Use the "Data Source" property to specify the directory name. Use the "SAS File Format" property to specify the file format of each data set in the directory.

What Happens If No File Format is Set

If no "SAS File Format" property value is explicitly set and you are not accessing transport files, then the local provider applies the following two rules to set the file format for you:

If the data sets in the specified directory are all in the same format, then the local provider uses that format.
If there are no data sets in the directory or if the directory contains data sets with different formats, then the "SAS Default File Format" property value ("V9") is used.

The two rules for handling those cases when the file format is not explicitly set do not apply to transport files. To access transport files, enter "XPT" as the "SAS File Format" property value.

Tip

Even though you can store transport files in the same directory as SAS data sets, it is still a good practice to keep transport files in a separate directory.

Setting the Data Source Property for Transport Files

When you access SAS data sets, the Connection object "Data Source" property is the path to the directory that contains the data sets. When you are reading transport files, however, you should set the Connection object "Data Source" property to both the path and to the filename of the transport file.

The local provider can read data sets in a transport file even if there is more than one data set in the transport file. Set the first parameter of the Recordset object's Open method to the data set name, as shown in this line of code:

obRecordset.Open "TestDs1", obConnection, adOpenStatic, adLockReadOnly, adCmdTableDirect

OLE DB Implementation

Specifying a File Format

The OLE DB implementation is similar to the code used for ADO; however, for the OLE DB interface, you use these properties:

DBPROP_INIT_DATASOURCE (corresponding to the ADO property "Data Source").
DBPROP_SAS_INIT_FILEFORMAT (corresponding to the ADO property "SAS File Format"). The property values for DBPROP_SAS_INIT_FILEFORMAT are the same values that are used for "SAS File Format"(see Specifying a File Format).

The following code shows how to use the local provider to set up a data source object that accesses SAS Version 6 files in a directory named c:\v6data:

CLSID clsid;
IUnknown * pDSO;
// Turn the ProgID into a CLSID value
CLSIDFromProgID( "SAS.LocalProvider", &clsid );
// With the CLSID, create an instance of this provider's data source object
CoCreateInstance( clsid, // class identifier
NULL,  // no outer unknown (that is, no aggregation)	
CLSCTX_INPROC_SERVER, // all providers run in process
IID_IUnknown, // the id of the interface we want on our new object
(LPVOID *) &pDSO ); // address of interface pointer returned

DBPROPSET rgPropSet[2];
DBPROP PropA;
DBPROP PropB;
IDBProperties * pIDBProperties;
IDBInitialize *pIDBInitialize;

PropA.dwPropertyID = DBPROP_INIT_DATASOURCE;
PropA.dwOptions = DBPROPOPTIONS_REQUIRED;
PropA.colid = DB_NULLID;
PropA.vValue.vt = VT_BSTR;
PropA.vValue.pbstrVal = SysAllocString( "c:\v6data" );
PropB.dwPropertyID = DBPROP_SAS_INIT_FILEFORMAT;
PropB.dwOptions = DBPROPOPTIONS_REQUIRED;
PropB.colid = DB_NULLID;
PropB.vValue.vt = VT_BSTR;
PropB.vValue.pbstrVal = SysAllocString( "V6" );

rgPropSet[0].cProperties = 1;
rgPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
rgPropSet[0].rgProperties = &PropA
rgPropSet[1].cProperties = 1;
rgPropSet[1].guidPropertySet = DBPROPSET_SAS_DBINIT;
rgPropSet[1].rgProperties = &PropB
pDSOUnk->QueryInterface( IID_IDBProperties, &pIDBProperties );
pIDBProperties->SetProperties( 2, rgPropset );
pDSOUnk->QueryInterface( IID_IDBInitialize, &pIDBInitialize );
pIDBInitialize->Initialize();

Accessing Different File Formats Simultaneously

You cannot simultaneously access different file formats by using the same data source object. If you need simultaneous access, do one of the following tasks:

Store the data sets in different directories.
Create multiple data source objects that point to the directory that contains the mixed format data sets. Use the DBPROP_INIT_DATASOURCE property to specify the directory name. Use the DBPROP_SAS_INIT_FILEFORMAT property to specify the file format of each data set in the directory.

What Happens if No File Format is Set

If the DBPROP_SAS_INIT_FILEFORMAT property value is not explicitly set and if you are not accessing transport files, the local provider applies the following two rules to set the file format for you:

If the data sets in the specified directory are all in the same format, then the local provider applies that format.
If there are no data sets in the directory or if the directory contains data sets with different formats, then the DBPROP_SAS_DEFAULTFILEFORMAT property value ("V9") is used.

The two rules for handling those cases when the file format is not explicitly set do not apply to transport files. To access transport files, enter "XPT" as the DBPROP_SAS_INIT_FILEFORMAT property value.

Tip

Even though you can store transport files in the same directory as SAS data sets, it is still a good practice to keep transport files in a separate directory.