Resources

• Products & Solutions
• System Requirements
• Samples
• Install Center
• Third-Party Software Reference
• Documentation
  • What's New in SAS
  • Product Index A-Z
  • SAS Viya
  • SAS 9.4
  • SAS 9.3 and Earlier
• Papers
• Focus Areas

Subsetting Data Sets for Update Access

In this recipe, you learn how to subset data sets for random and update access by using the SAS WHERE clause and the SAS/SHARE provider. Sample code for the ADO recipe is included.

Applies to:	SAS/SHARE Data Provider
Implement using:	ADO or OLE DB

---

Overview

The SAS SQL procedure enables you to subset data sets using a WHERE clause. In ADO, WHERE clause processing is controlled through the "SAS Where" provider property. The property takes a string value that must be a complete and valid SAS WHERE expression. A data set opened using a SAS WHERE clause has the same features as other SAS data sets, so you can open it for both random and update access.

The following code assumes the connection is already open. (For information about how to open a Connection object, see Opening an ADO Connection Object.)

   obRecordSet.ActiveConnection = obConnection
   obRecordSet.Properties("SAS Where") = "flight > 306"
   obRecordSet.Open "airline.Flights", , adOpenStatic, adLockReadOnly, adCmdTableDirect
  

Note: The second parameter must remain empty, as shown in the code fragment.

Note: See SAS Language Reference: Concepts and the SAS Procedures Guide for more information about WHERE clause processing.

---

Details

Differences between "SAS Where" and "Filter"

The "SAS Where" provider property may seem to serve the same function as the ADO Recordset object's "Filter" property; however, there are differences between the two.

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

Other Methods of Subsetting

You also can perform subsetting using SQL queries and the Command object, which will return read-only, sequential data sets. See Executing SQL Queries and Commands for more information.