Accessing DBMS Data with the LIBNAME Statement

Using Other SAS Procedures with DBMS Data

Examples in this section illustrate basic uses of other SAS procedures with librefs that refer to DBMS data.

This example uses the PRINT and MEANS procedures on a SAS data set created from the Oracle table March. The MEANS procedure provides information about the largest number of passengers on each flight.

libname mydblib oracle user=testuser password=testpass;

title 'Number of Passengers per Flight by Date';

proc print data=mydblib.march noobs;
  var dates boarded;
  by flight dest;
  sumby flight;
  sum boarded;
  format dates datetime9.;
run;

title 'Maximum Number of Passengers per Flight';

proc means data=mydblib.march fw=5 maxdec=1 max;
  var boarded;
  class flight;
run;

Using the PRINT and MEANS Procedures

                       Number of Passengers per Flight by Date

   ----------------------------- FLIGHT=132 DEST=YYZ ------------------------------

                                      DATE    BOARDED

                                 01MAR1998      115
                                 02MAR1998      106
                                 03MAR1998       75
                                 04MAR1998      117
                                 05MAR1998      157
                                 06MAR1998      150
                                 07MAR1998      164
                                 ---------    -------
                                    FLIGHT      884

  
   ----------------------------- FLIGHT=219 DEST=LON ------------------------------

                                      DATE    BOARDED

                                 01MAR1998       198
                                 02MAR1998       147
                                 03MAR1998       197
                                 04MAR1998       232
                                 05MAR1998       160
                                 06MAR1998       163
                                 07MAR1998       241
                                 ---------    -------
                                    FLIGHT      1338

                        Maximum Number of Passengers per Flight

                                 The MEANS Procedure

                             Analysis Variable : BOARDED

                               FLIGHT   N Obs       Max
                               132         7      164.0

                               219         7      241.0

Using the DATASETS Procedure

This example uses the DATASETS procedure to view a list of DBMS tables, in this case, in an Oracle database.

Note: The MODIFY and ALTER statements in PROC DATASETS are not available for use with librefs that refer to DBMS data. [cautionend]

libname mydblib oracle user=testuser password=testpass;
                                                                 
title 'Table Listing';                                   
                                                                 
proc datasets lib=mydblib;                                      
  contents data=_all_ nods;                                      
run;

Using the DATASETS Procedure

               Table Listing
            The DATASETS Procedure

           -----Directory-----

           Libref:        MYDBLIB
           Engine:        Oracle 
           Physical Name:        
           Schema/User:   testuser 


           #  Name       Memtype
           ----------------------
           1  BIRTHDAY   DATA   
           2  CUST       DATA   
           3  CUSTOMERS  DATA   
           4  DELAY      DATA   
           5  EMP        DATA   
           6  EMPLOYEES  DATA   
           7  FABORDER   DATA   
           8  INTERNAT   DATA   
           9  INVOICES   DATA   
          10  INVS       DATA

Using the CONTENTS Procedure

This example shows output from the CONTENTS procedure when it is run on a DBMS table. PROC CONTENTS shows all SAS metadata that the SAS/ACCESS interface derives from the DBMS table.

libname mydblib oracle user=testuser password=testpass;

title 'Contents of the DELAY Table';

proc contents data=mydblib.delay;
run;

Using the CONTENTS Procedure

              Contents of the DELAY Table   

                The CONTENTS Procedure

Data Set Name: MYDBLIB.DELAY          Observations:         . 
Member Type:   DATA                   Variables:            7 
Engine:        Oracle                 Indexes:              0 
Created:       .                      Observation Length:   0 
Last Modified: .                      Deleted Observations: 0 
Protection:                           Compressed:           NO
Data Set Type:                        Sorted:               NO
Label:                                                        


          -----Alphabetic List of Variables and Attributes-----
 
#    Variable    Type    Len    Pos    Format         Informat       Label
-----------------------------------------------------------------------------
2    DATES       Num       8      8    DATETIME20.    DATETIME20.    DATES   
7    DELAY       Num       8     64                                  DELAY   
5    DELAYCAT    Char     15     32    $15.           $15.           DELAYCAT
4    DEST        Char      3     24    $3.            $3.            DEST    
6    DESTYPE     Char     15     48    $15.           $15.           DESTYPE 
1    FLIGHT      Char      3      0    $3.            $3.            FLIGHT  
3    ORIG        Char      3     16    $3.            $3.            ORIG

Using the RANK Procedure

This example uses the RANK procedure to rank flights in the DB2 table Delay by number of minutes delayed.

libname mydblib db2 ssid=db2;

options obs=20;

proc rank data=mydblib.delay descending 
    ties=low out=ranked;    
  var delay;                                                     
  ranks RANKING;                                                 
run;                                                             
                                                                 
proc print data=ranked;                                          
  title 'Ranking of Delayed Flights';
  format delay 2.0
         dates datetime9.;                            
run;

Using the RANK Procedure

                             Ranking of Delayed Flights                       1
                                                                                                                     
   OBS FLIGHT     DATES ORIG DEST DELAYCAT        DESTYPE         DELAY RANKING
                                                                            
     1  114   01MAR1998 LGA  LAX  1-10 Minutes    Domestic          8       9  
     2  202   01MAR1998 LGA  ORD  No Delay        Domestic         -5      42  
     3  219   01MAR1998 LGA  LON  11+ Minutes     International    18       4  
     4  622   01MAR1998 LGA  FRA  No Delay        International    -5      42  
     5  132   01MAR1998 LGA  YYZ  11+ Minutes     International    14       8  
     6  271   01MAR1998 LGA  PAR  1-10 Minutes    International     5      13  
     7  302   01MAR1998 LGA  WAS  No Delay        Domestic         -2      36  
     8  114   02MAR1998 LGA  LAX  No Delay        Domestic          0      28  
     9  202   02MAR1998 LGA  ORD  1-10 Minutes    Domestic          5      13  
    10  219   02MAR1998 LGA  LON  11+ Minutes     International    18       4  
    11  622   02MAR1998 LGA  FRA  No Delay        International     0      28  
    12  132   02MAR1998 LGA  YYZ  1-10 Minutes    International     5      13  
    13  271   02MAR1998 LGA  PAR  1-10 Minutes    International     4      19  
    14  302   02MAR1998 LGA  WAS  No Delay        Domestic          0      28  
    15  114   03MAR1998 LGA  LAX  No Delay        Domestic         -1      32  
    16  202   03MAR1998 LGA  ORD  No Delay        Domestic         -1      32  
    17  219   03MAR1998 LGA  LON  1-10 Minutes    International     4      19  
    18  622   03MAR1998 LGA  FRA  No Delay        International    -2      36  
    19  132   03MAR1998 LGA  YYZ  1-10 Minutes    International     6      12  
    20  271   03MAR1998 LGA  PAR  1-10 Minutes    International     2      25

Using the TABULATE Procedure

This example uses the TABULATE procedure on the Oracle table Payroll to display a chart of the number of employees for each job code.

libname mydblib oracle user=testuser password=testpass;

title "Number of Employees by Jobcode";

proc tabulate data=mydblib.payroll format=3.0;
   class jobcode;
   table jobcode*n;
   keylabel n="#";
run;

Using the TABULATE Procedure

                            Number of Employees by Jobcode                   1
       
          -----------------------------------------------------------------
          |                            JOBCODE                            |
          |---------------------------------------------------------------|
          |BCK|FA1|FA2|FA3|ME1|ME2|ME3|NA1|NA2|PT1|PT2|PT3|SCP|TA1|TA2|TA3|
          |---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---|
          | # | # | # | # | # | # | # | # | # | # | # | # | # | # | # | # |
          |---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---|
          |  9| 11| 16|  7|  8| 14|  7|  5|  3|  8| 10|  2|  7|  9| 20| 12|
          -----------------------------------------------------------------

Using the APPEND Procedure

In this example, the DB2 table Payroll2 is appended to the DB2 table Payroll with the APPEND procedure. The Payroll table is updated on DB2.

Note: When you append data to a DBMS table, you are actually inserting rows into a table. The rows can be inserted into the DBMS table in any order. [cautionend]

libname mydblib db2 ssid=db2;

proc append base=mydblib.payroll
            data=mydblib.payroll2;
run;

proc print data=mydblib.payroll;
  title 'PAYROLL After Appending PAYROLL2';
  format birth datetime9. hired datetime9.;
run;

Note: In cases where a DBMS table that you are using is in the same database space as a table that you are creating or updating, use the LIBNAME option CONNECTION=SHARED to prevent a deadlock. [cautionend]

Using the APPEND Procedure

                PAYROLL After Appending PAYROLL2             1
    
        OBS    IDNUM    SEX    JOBCODE    SALARY        BIRTH        HIRED

          1    1919      M       TA2       34376    12SEP1960    04JUN1987
          2    1653      F       ME2       35108    15OCT1964    09AUG1990
          3    1400      M       ME1       29769    05NOV1967    16OCT1990
          4    1350      F       FA3       32886    31AUG1965    29JUL1990
          5    1401      M       TA3       38822    13DEC1950    17NOV1985
          6    1499      M       ME3       43025    26APR1954    07JUN1980
          7    1101      M       SCP       18723    06JUN1962    01OCT1990
          8    1333      M       PT2       88606    30MAR1961    10FEB1981
          9    1402      M       TA2       32615    17JAN1963    02DEC1990
         10    1479      F       TA3       38785    22DEC1968    05OCT1989
         11    1403      M       ME1       28072    28JAN1969    21DEC1991
         12    1739      M       PT1       66517    25DEC1964    27JAN1991
         13    1658      M       SCP       17943    08APR1967    29FEB1992
         14    1428      F       PT1       68767    04APR1960    16NOV1991
         15    1782      M       ME2       35345    04DEC1970    22FEB1992
         16    1244      M       ME2       36925    31AUG1963    17JAN1988
         17    1383      M       BCK       25823    25JAN1968    20OCT1992
         18    1574      M       FA2       28572    27APR1960    20DEC1992
         19    1789      M       SCP       18326    25JAN1957    11APR1978
         20    1404      M       PT2       91376    24FEB1953    01JAN1980

Top of Page