SAS/ACCESS Software for Relational Databases: Reference

Using Other SAS Procedures with DBMS Data

The following examples illustrate basic uses of other SAS procedures with librefs that refer to DBMS data.

Using the MEANS Procedure

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

libname myoralib oracle user=anita password=traveler
  path='fltdata' schema=airport;

title 'Number of Passengers per 
       Flight by Date';

proc print data=my_data noobs;
  var date boarded;
  by flight dest;
  sumby flight;
  sum boarded;
run;

title 'Maximum Number of 
       Passengers per Flight';

proc means data=my_data fw=5 maxdec=1 max;
  var boarded;
  class flight;
run;

Partial output for this example is shown in Using the PRINT and MEANS Procedures.

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 N FLIGHT Obs MAXIMUM 132 7 164.0 219 7 241.0 271 6 177.0 622 6 210.0

Using the DATASETS Procedure

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

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

libname mydb2lib db2 ssid=db2;
                                                                 
title "Table Listing for DB2";                                   
                                                                 
proc datasets lib=mydb2lib;                                      
  contents data=_all_ nods;                                      
run;

Partial output for this example is shown in Using the DATASETS Procedure.

Using the DATASETS Procedure

Table Listing for DB2 DATASETS PROCEDURE -----Directory----- Libref: MYDB2LIB Engine: DB2 Filefmt: Physical Name: DB2 # Name Memtype -------------------- 1 DELAY DATA 2 INTERNAT DATA 3 MARCH DATA 4 PAYROLL DATA 5 PAYROLL2 DATA 6 SCHEDULE DATA 7 STAFF DATA 8 SUPERV DATA

Using the CONTENTS Procedure

These examples show output from the CONTENTS procedure when it is run on a DBMS table. Note that PROC CONTENTS shows all of the SAS metadata derived from the DBMS table by the SAS/ACCESS engine.

libname mydb2lib db2 ssid=db2;

proc contents data=mydb2lib.delay;
run;

Output from this example is shown in Using the CONTENTS Procedure.

Using the CONTENTS Procedure

CONTENTS PROCEDURE Data Set Name: AIRLINE.DELAY Observations: . Member Type: DATA Variables: 7 Engine: DB2 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 DATE9. DATE9. 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 mydb2lib db2 ssid=db2;

options obs=20;

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

Output for this example is shown in Using the RANK Procedure.

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 myoralib oracle user=antonio password=porsche
  path='airhrdept' schema=hrdept;

title "Number of Employees by Jobcode";

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

Output for this example is shown in Using the TABULATE Procedure.

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 mydb2lib db2 ssid=db2;

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

proc print data=mydb2lib.payroll;
  title 'PAYROLL After Appending 
         PAYROLL2';
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, you must use the CONNECTION=SHARED LIBNAME option to prevent a deadlock. See SAS/ACCESS LIBNAME Statement for more information on SAS/ACCESS LIBNAME options. [cautionend]
Partial output for this example is shown in Using the APPEND Procedure.

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

Chapter Contents
Previous
Next
Top of Page