Example: Select and Combine DBMS Data

This example uses a WHERE statement in a DATA step to create a list that includes only unpaid bills over $300,000.

libname mydblib oracle user=testuser password=testpass;

proc sql;
  create view allinv as
      select paidon, billedon, invnum, amtinus, billedto
      from mydblib.invoices
quit;

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

proc print data=notpaid label;
  format amtinus dollar20.2 billedon datetime9.;
  label  amtinus=amountinus billedon=billedon
         invnum=invoicenum billedto=billedto;
  title 'High Bills--Not Paid';
run;

Use a WHERE Statement

                               High Bills--Not Paid

        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