SAS/ACCESS Software for Relational Databases: Reference

Using the SQL Procedure with DBMS Data

The previous examples showed how to read DBMS data and perform operations on the data in SAS. You can also perform operations on data directly in your DBMS by using the SQL procedure.

The following examples use the SQL procedure to query, update, and create DBMS tables.

Querying a DBMS Table

This example uses the SQL procedure to query the ORACLE table PAYROLL. The PROC SQL query retrieves all job codes and provides a total salary amount for each job code.

libname myoralib oracle user=karin password=haggis
  path='airhrdata' schema=airport;

proc sql;
  select jobcode label='Jobcode',
         sum(salary) as total 
         label='Total for Group' 
         format=dollar11.2
  from myoralib.payroll
  group by jobcode;
quit;

Output for this example is shown in Querying a DBMS Table.

Querying a DBMS Table

The SAS System 1 Total for Jobcode Group BCK $232,148.00 FA1 $253,433.00 FA2 $447,790.00 FA3 $230,537.00 ME1 $228,002.00 ME2 $498,076.00 ME3 $296,875.00 NA1 $210,161.00 NA2 $157,149.00 PT1 $543,264.00 PT2 $879,252.00 PT3 $21,009.00 SCP $128,162.00 TA1 $249,492.00 TA2 $671,499.00 TA3 $476,155.00

The next example uses the SQL procedure to query flight information from the ORACLE table DELAY. The WHERE clause specifies that only flights to London and Frankfurt are retrieved.

libname myoralib oracle user=kurt password=freude
  path='fltdata' schema=airport;

 title 'Flights to London and Frankfurt';

 proc sql;
   select dates format=date9., 
     dest from myoralib.delay
   where (dest eq "FRA") or
     (dest eq "LON")
   order by dest;
quit;

Note: Interaction between the SQL procedure and the SAS/ACCESS engine ensures that both the WHERE clause and the ORDER BY clause are processed by the DBMS for optimized performance. [cautionend]
Output for this example is shown in Querying a DBMS Table.

Querying a DBMS Table

Flights to London and Frankfurt DATES DEST 01MAR1998 FRA 04MAR1998 FRA 07MAR1998 FRA 03MAR1998 FRA 05MAR1998 FRA 02MAR1998 FRA 04MAR1998 LON 07MAR1998 LON 02MAR1998 LON 06MAR1998 LON 05MAR1998 LON 03MAR1998 LON 01MAR1998 LON

The next example uses the SQL procedure to query the DB2 table INTERNAT for information on international flights with over 200 passengers. Note that output can be sorted by using a PROC SQL query and that the TITLE, LABEL, and FORMAT key words are not ANSI standard SQL; they are SAS extensions that you can use in PROC SQL.

libname mydb2lib db2 ssid=db2;

proc sql;
   title  'International Flights by Flight Number';
   title2 'with Over 200 Passengers';
   select flight   label="Flight Number",
          dates    label="Departure Date" 
                   format DATE9.,
          dest     label="Destination",
          boarded  label="Number Boarded"
     from mydb2lib.internat
    where boarded > 200
    order by flight;
quit;

Output for this example is shown in Querying a DBMS Table.

Querying a DBMS Table

International Flights by Flight Number with Over 200 Passengers Flight Departure Number Number Date Destination Boarded ---------------------------------------- 219 04MAR1998 LON 232 219 07MAR1998 LON 241 622 07MAR1998 FRA 210 622 01MAR1998 FRA 207

The next example uses the SQL procedure to query the DB2 table PAYROLL for information on all flight attendants, ordered by JOBCODE and SERVICE.

libname mydb2lib db2 ssid=db2;

proc sql;
   title  'Service Years and Salary';
   title2 'for Flight Attendants';
   select idnum     label='ID Number',
          jobcode   label='Job Code',
          salary    label='Salary' 
                    format dollar7.,
          (today()-HIRED)/365.25 as service
                    label='Years Service' 
                    format 4.1,
          hired     label='Hire Date'
                    format date9.
    from mydb2lib.payroll
    where jobcode like 'FA%'
    order by jobcode, service;
quit;

Partial output for this example is shown in Querying a DBMS Table.

Querying a DBMS Table

Service Years and Salary for Flight Attendants ID Job Years Number Code Salary Service Hire Date ----------------------------------------- 1132 FA1 $22,413 3.9 22OCT1993 1425 FA1 $23,979 4.6 28FEB1993 1103 FA1 $23,738 5.2 23JUL1992 1130 FA1 $23,916 5.3 05JUN1992 1414 FA1 $23,644 5.4 12APR1992 1113 FA1 $22,367 5.9 17OCT1991 1094 FA1 $22,268 6.4 17APR1991 1422 FA1 $22,454 6.5 06APR1991 1116 FA1 $22,862 6.5 21MAR1991 1970 FA1 $22,615 6.5 12MAR1991

Querying Multiple DBMS Tables

You can also retrieve data from multiple DBMS tables in a single query by using the SQL procedure. This example joins the ORACLE tables STAFF and PAYROLL to query salary information for employees earning more than $40,000.

libname myoralib oracle user=michelle password=toys
  path='airhrdata' schema=hrdept;

title 'Employees with salary greater than $40,000';

options obs=20;

proc sql;
  select a.lname, a.fname, b.salary 
    format=dollar10.2
  from myoralib.staff a, myoralib.payroll b
  where (a.idnum eq b.idnum) and 
    (b.salary gt 40000);
quit;

Note: For optimized performance, the SAS/ACCESS engine passes the entire join to the DBMS for processing. [cautionend]
Output for this example is shown in Querying Multiple DBMS Tables.

Querying Multiple DBMS Tables

Employees with salary greater than $40,000 LNAME FNAME SALARY BAREFOOT JOSEPH $43,025.00 BANADYGA JUSTIN $88,606.00 BRANCACCIO JOSEPH $66,517.00 BRADY CHRISTINE $68,767.00 COHEN LEE $91,376.00 CARTER-COHEN KAREN $40,260.00 CASTON FRANKLIN $41,690.00 FERNANDEZ KATRINA $51,081.00 GRAHAM ALVIN $65,111.00 GREGORSKI DANIEL $68,096.00 HARRIS CHARLES $84,685.00 HASENHAUER CHRISTINA $70,736.00 HAVELKA RAYMOND $41,551.00 HERRERO CLYDE $66,130.00 KIMANI ANNE $40,899.00 MARSHBURN JASPER $89,632.00 MORGAN ALFRED $42,264.00 NEWKIRK SANDRA $84,536.00 NEWKIRK WILLIAM $52,270.00 NEWTON JAMES $84,203.00

The next example uses the SQL procedure to join and query the DB2 tables MARCH, DELAY, and FLIGHT. The query retrieves information on delayed international flights during the month of March.

libname mydb2lib db2 ssid=db2;

title "Delayed International Flights in March";                
                                                               
proc sql;                                                      
  select distinct march.flight, march.dates, 
      delay format=2.0  
    from mydb2lib.march, mydb2lib.delay, 
      mydb2lib.internat     
  where march.flight=delay.flight and                          
      march.dates=delay.dates and                                
      march.flight=internat.flight and                           
      delay>0                                                    
  order by delay descending; 
quit;

Note: For optimized performance, the SAS/ACCESS engine passes the entire join to the DBMS for processing. [cautionend]
Output for this example is shown in Querying Multiple DBMS Tables.

Querying Multiple DBMS Tables

Delayed International Flights in March FLIGHT DATES DELAY ------------------------ 622 04MAR1998 30 219 06MAR1998 27 622 07MAR1998 21 219 01MAR1998 18 219 02MAR1998 18 219 07MAR1998 15 132 01MAR1998 14 132 06MAR1998 7 132 03MAR1998 6 271 01MAR1998 5 132 02MAR1998 5 271 04MAR1998 5 271 05MAR1998 5 271 02MAR1998 4 219 03MAR1998 4 271 07MAR1998 4 219 04MAR1998 3 132 05MAR1998 3 219 05MAR1998 3 271 03MAR1998 2

The next example uses the SQL procedure to retrieve the combined results of two queries to the ORACLE tables PAYROLL and PAYROLL2. An OUTER UNION in PROC SQL concatenates the data.

libname myoralib oracle user=charles password=mazyar
  path='airhrdept' schema=hrdept;

title "Payrolls 1 & 2";

proc sql;
  select *
     from myoralib.payroll
  outer union corr
     select *
        from myoralib.payroll2
        order by idnum, jobcode, salary;
quit;

Partial output for this example is shown in Querying Multiple DBMS Tables.

Querying Multiple DBMS Tables

Payrolls 1 & 2 1 IDNUM SEX JOBCODE SALARY BIRTH HIRED --------------------------------------------------- 1009 M TA1 28880 02MAR1959 26MAR1992 1017 M TA3 40858 28DEC1957 16OCT1981 1036 F TA3 39392 19MAY1965 23OCT1984 1036 F TA3 42465 19MAY1965 23OCT1984 1037 F TA1 28558 10APR1964 13SEP1992 1038 F TA1 26533 09NOV1969 23NOV1991 1050 M ME2 35167 14JUL1963 24AUG1986 1065 M ME2 35090 26JAN1944 07JAN1987 1065 M ME3 38090 26JAN1944 07JAN1987 1076 M PT1 66558 14OCT1955 03OCT1991 1076 M PT1 69742 14OCT1955 03OCT1991 1094 M FA1 22268 02APR1970 17APR1991 1100 M BCK 25004 01DEC1960 07MAY1988 1101 M SCP 18723 06JUN1962 01OCT1990 1102 M TA2 34542 01OCT1959 15APR1991 1103 F FA1 23738 16FEB1968 23JUL1992 1104 M SCP 17946 25APR1963 10JUN1991 1105 M ME2 34805 01MAR1962 13AUG1990

Updating DBMS Data

In addition to querying data, you can also update data directly in your DBMS. You can update rows, columns, and tables by using the SQL procedure.

The following example adds a new row to the DB2 table SUPERV.

libname mydb2lib db2 ssid=db2;

proc sql;
insert into mydb2lib.superv
  values('1588','NY','FA');
quit;

proc print data=mydb2lib.superv;
  title "New Row in AIRLINE.SUPERV";
run;

Note: Depending on how your DBMS processes inserts, the new row might not be added as the last physical row of the table. [cautionend]

Output for this example is shown in Updating DBMS Data.

Updating DBMS Data

New Row in AIRLINE.SUPERV 1 OBS SUPID STATE JOBCAT 1 1677 CT BC 2 1834 NY BC 3 1431 CT FA 4 1433 NJ FA 5 1983 NY FA 6 1385 CT ME 7 1420 NJ ME 8 1882 NY ME 9 1935 CT NA 10 1417 NJ NA 11 1352 NY NA 12 1106 CT PT 13 1442 NJ PT 14 1118 NY PT 15 1405 NJ SC 16 1564 NY SC 17 1639 CT TA 18 1401 NJ TA 19 1126 NY TA 20 1588 NY FA

The next example deletes all employees who work in Connecticut from the DB2 table STAFF.

libname mydb2lib db2 ssid=db2;

proc sql;
  delete from mydb2lib.staff
    where state='CT';
quit;

options obs=20;

proc print data=mydb2lib.staff;
   title "AIRLINE.STAFF After Deleting 
       Connecticut Employees";
run;

Note: If you omit a WHERE clause when you delete rows from a table, all rows in the table are deleted. [cautionend]

Output for this example is shown in Updating DBMS Data.

Updating DBMS Data

AIRLINE.STAFF After Deleting Connecticut Employees 1 OBS IDNUM LNAME FNAME CITY STATE HPHONE 1 1400 ALHERTANI ABDULLAH NEW YORK NY 212/586-0808 2 1350 ALVAREZ MERCEDES NEW YORK NY 718/383-1549 3 1401 ALVAREZ CARLOS PATERSON NJ 201/732-8787 4 1499 BAREFOOT JOSEPH PRINCETON NJ 201/812-5665 5 1101 BAUCOM WALTER NEW YORK NY 212/586-8060 6 1402 BLALOCK RALPH NEW YORK NY 718/384-2849 7 1479 BALLETTI MARIE NEW YORK NY 718/384-8816 8 1739 BRANCACCIO JOSEPH NEW YORK NY 212/587-1247 9 1658 BREUHAUS JEREMY NEW YORK NY 212/587-3622 10 1244 BUCCI ANTHONY NEW YORK NY 718/383-3334 11 1383 BURNETTE THOMAS NEW YORK NY 718/384-3569 12 1574 CAHILL MARSHALL NEW YORK NY 718/383-2338 13 1789 CARAWAY DAVIS NEW YORK NY 212/587-9000 14 1404 COHEN LEE NEW YORK NY 718/384-2946 15 1065 COPAS FREDERICO NEW YORK NY 718/384-5618 16 1876 CHIN JACK NEW YORK NY 212/588-5634 17 1129 COUNIHAN BRENDA NEW YORK NY 718/383-2313 18 1988 COOPER ANTHONY NEW YORK NY 212/587-1228 19 1405 DACKO JASON PATERSON NJ 201/732-2323 20 1983 DEAN SHARON NEW YORK NY 718/384-1647

Creating a DBMS Table

You can create new tables in your DBMS by using the SQL procedure.

This example uses the SQL procedure to create the ORACLE table GTFORTY by using data from the ORACLE STAFF and PAYROLL tables.

libname myoralib oracle user=charles password=mazyar
  path='airhrdept' schema=hrdept;

proc sql;
  create table myoralib.gtforty as
  select lname as lastname,
         fname as firstname,
         salary as salary 
           format=dollar10.2
  from myoralib.staff a, 
       myoralib.payroll b
  where (a.idnum eq b.idnum) and 
        (salary gt 40000);

options obs=20;

proc print data=myoralib.gtforty noobs;
  title 'Employees with salaries over $40,000';
run;

Output for this example is shown in Creating a DBMS Table.

Creating a DBMS Table

Employees with salaries over $40,000 1 LASTNAME FIRSTNAME SALARY BAREFOOT JOSEPH $43,025.00 BANADYGA JUSTIN $88,606.00 BRANCACCIO JOSEPH $66,517.00 BRADY CHRISTINE $68,767.00 COHEN LEE $91,376.00 CARTER-COHEN KAREN $40,260.00 CASTON FRANKLIN $41,690.00 FERNANDEZ KATRINA $51,081.00 GRAHAM ALVIN $65,111.00 GREGORSKI DANIEL $68,096.00 HARRIS CHARLES $84,685.00 HASENHAUER CHRISTINA $70,736.00 HAVELKA RAYMOND $41,551.00 HERRERO CLYDE $66,130.00 KIMANI ANNE $40,899.00 MARSHBURN JASPER $89,632.00 MORGAN ALFRED $42,264.00 NEWKIRK SANDRA $84,536.00 NEWKIRK WILLIAM $52,270.00 NEWTON JAMES $84,203.00

Chapter Contents
Previous
Next
Top of Page