SAS/ACCESS Software for Relational Databases: Reference

Selecting and Combining Data

This example shows two ways to use the WHERE statement to subset DBMS data. The first method uses descriptors. The second method uses the new SAS/ACCESS LIBNAME statement to accomplish the same task in an easier and more direct way.

Using the WHERE Statement with Descriptors

The view descriptor VLIB.ALLINV lists invoices for all customers and is based on the table INVOICE. You can use a DATA step to create a SAS data file that contains information on customers who have not paid their bills and whose bills amount to at least $300,000.

proc access dbms=oracle;

/* create access descriptor   */

   create adlib.invoice.access;
   user=scott;  password=tiger;
   path='myorapath'
   table=invoice;
   assign=yes;
   rename invoicenum = invnum
          amtbilled  = amtbilld
          amountinus = amtinus;
   format paidon       date9.
          billedon     date9.
          invoicenum   5.0
          billedby     6.0
          amtbilled    15.2
          amountinus   15.2;
   list all;

/* create vlib.allinv view  */

   create vlib.allinv.view;
   select all;
run;

data work.notpaid(keep=invnum billedto amtinus billedon);
   set vlib.allinv;
   where paidon is missing and amtinus>=300000.00;
run;

proc print data=work.notpaid;
   format amtinus dollar20.2;
   title 'High Bills--Not Paid';
run;

In the DATA step's WHERE statement, be sure to use SAS variable names, not DBMS column names. The DATA statement uses the KEEP= data set option. This option specifies that you want to include only the listed variables in the new SAS data file WORK.NOTPAID. However, you can still reference the other view descriptor variables in other statements within the DATA step.

The SAS WHERE statement includes two conditions to be met. First, it specifies to select only observations that have a missing value for the PAIDON variable. Second, the SAS WHERE statement requires that the amount in each bill be higher than a certain figure. You must be familiar with the DBMS data so that you can determine reasonable values for these expressions.

When you are referencing a view descriptor in a SAS procedure or DATA step, it is more efficient to use a SAS WHERE statement rather than a subsetting IF statement. When possible, a WHERE statement's selection criteria is passed to the DBMS for processing and returns a subset of rows to the SAS System for further processing. In contrast, when you use a subsetting IF statement, every row is returned to the SAS System to be evaluated by the IF statement. For more information about how WHERE clauses are passed to the DBMS for processing, see Using a Subset of the DBMS Data.

Output for both of these examples is shown in Using the WHERE Statement.

Using the WHERE Statement with a SAS/ACCESS LIBNAME

This example uses a WHERE statement directly in the PRINT procedure to print only unpaid bills over $300,000. This example differs from the previous example in that the SAS/ACCESS LIBNAME statement is used to define a SAS libref that references the DBMS data. Descriptors are not used. Output from this example is identical to the previous example, except for the column names, which are limited to eight characters when you use descriptors but can be up to 32 characters when you use the SAS/ACCESS LIBNAME statement.

libname myoralib oracle user=dmitry pass=elvis
  path='txtdata' schema=textile;

proc sql;
  create view allinv as
      select paidon, billedon, invoicenum, amountinus, billedto
      from myoralib.invoice
      where paidon is null and amountinus>=300000.00; 
quit;

proc print data=allinv(drop=paidon);
  format amountinus dollar20.2;
  title 'High Bills--Not Paid';
run;

Using the WHERE Statement

High Bills--Not Paid 1 Obs billedon invoicenum amountinus billedto 1 05OCT1998 11271 $11,063,836.00 18543489 2 10OCT1998 11286 $11,063,836.00 43459747 3 02NOV1998 12051 $2,256,870.00 39045213 4 17NOV1998 12102 $11,063,836.00 18543489 5 27DEC1998 12471 $2,256,870.00 39045213 6 24DEC1998 12476 $2,256,870.00 38763919

Chapter Contents
Previous
Next
Top of Page