In this recipe, you learn how to specify the SAS file format to use when you access a data source with the Local Data Provider. You will also learn how to access different file formats simultaneously, and see the results when no file format is set. Sample code is provided for both the ADO and OLE DB interfaces.
| Applies to: | SAS Local Data Provider |
| Implement using: | ADO or OLE DB |
To use ADO to specify the file format that the Local Data Provider will use to access a data source, you set the "SAS File Format" property on the Connection object. For example, the following Visual Basic code could be used to access a SAS Version 6 data set named data that is stored in a directory named c:\v6data.
Note: For more information about how to open a Connection object, see Opening an ADO Connection Object.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sas.localprovider.1"
cn.Properties("Data Source") = "c:\v6data"
cn.Properties("SAS File Format") = "V6"
cn.Open
rs.Open "data", cn, 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 cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sas.localprovider.1"
cn.Properties("Data Source") = "c:\xptdata\xport1.dat"
cn.Properties("SAS File Format") = "XPT"
cn.Open
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.Open "testds1", cn, adOpenStatic, adLockReadOnly, adCmdTableDirect
For more information about writing to the ADO interface, see ADO Details.
For information about how specify the file format by writing directly to the OLE DB interface, see OLE DB Details.
You can use the following values for the "SAS File Format" property:
You cannot simultaneously access different file formats using the same ADO Connection object. If you need simultaneous access, do one of the following:
If no "SAS File Format" property value is explicitly set and you are not accessing transport files, the local provider will apply the following two rules to set the file format for you:
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.
Note: Even though you can store transport files in the same directory as SAS data sets, it is still good practice to keep transport files in a separate directory.
When you are accessing SAS data sets, the Connection object's "Data Source" property is the path to the directory containing the data sets. When you are reading transport files, however, you should set the Connection object's "Data Source" property to both the path and to the file name 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 desired data set name, as shown in the second Visual Basic example in the Overview section.
The OLE DB recipe is similar to the recipe used for ADO; however, for the OLE DB interface, you use these properties:
The following code fragments show how to use the Local Data Provider to set up a data source object that will access SAS Version 6 files in a directory named c:\v6data:
CLSID clsid; IUnknown * pDSO; // Turn the ProgID into a CLSID value CLSIDFromProgID( "sas.localprovider.1", &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();
You cannot simultaneously access different file formats using the same data source object. If you need simultaneous access, do one of the following:
If the DBPROP_SAS_INIT_FILEFORMAT property value is not explicitly set and if you are not accessing transport files, the local provider will apply the following two rules to set the file format for you:
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.
Note: Even though you can store transport files in the same directory as SAS data sets, it is still good practice to keep transport files in a separate directory.