Subsetting Data Sets for Random and Update Access

Goal

You want your application to use the WHERE clause with the SAS SQL procedure to subset data sets for random and update access.

This recipe applies to the SAS/SHARE and Base SAS providers. Sample code for ADO is included.

Note: For more information about WHERE clause processing, see SAS Language Reference: Concepts and the Base SAS Procedures Guide.

Implementation

Sample Code for Subsetting Data Sets

The SAS SQL procedure enables you to subset data sets by using a WHERE clause. A data set that is opened by a SAS WHERE clause has the same features as other SAS data sets, so you can open it for both random and update access.

To control WHERE clause processing, you use the "SAS Where" provider property. The value of the "SAS Where" property is a string that is a complete and valid SAS WHERE expression. The following Visual Basic code shows how this task is done.

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

obRecordSet.ActiveConnection = obConnection
obRecordSet.Properties("SAS Where") = "flight > 306"

' The second parameter on the Open method must remain empty.
obRecordSet.Open "airline.Flights", , adOpenStatic, adLockReadOnly, adCmdTableDirect

A Closer Look at the 'SAS Where' Property

Although the "SAS Where" provider property might seem to serve the same function as the ADO Recordset object's "Filter" property, there are differences:

The "SAS Where" property sends the clause to the server, and the server determines which records pass the WHERE clause. The server returns only records that pass the WHERE clause.
The "Filter" property sends all of the records in the data set to the client. The client then processes each record to determine whether it should be in the recordset.

Subsetting Data Sets for Random and Update Access

Goal

Implementation

Sample Code for Subsetting Data Sets

A Closer Look at the 'SAS Where' Property

See Also