 |
|
| Accessing DBMS Data with the LIBNAME Statement |
Using the SQL Procedure with DBMS Data
| Overview |
Rather than performing operations on your data in SAS, you can perform operations on data directly in your DBMS by using the LIBNAME statement and 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 mydblib oracle user=testuser password=testpass; title 'Total Salary by Jobcode'; proc sql; select jobcode label='Jobcode', sum(salary) as total label='Total for Group' format=dollar11.2 from mydblib.payroll group by jobcode; quit;
Total Salary by Jobcode
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 mydblib oracle user=testuser password=testpass; title 'Flights to London and Frankfurt'; proc sql; select dates format=datetime9., dest from mydblib.delay where (dest eq "FRA") or (dest eq "LON") order by dest; quit;
Note: By default, the DBMS processes both
the WHERE clause and the ORDER BY clause for optimized performance. See Overview of Optimizing Your SQL Usage for more
information. ![[cautionend]](../../../../common/63294/HTML/default/images/cautend.gif)
Querying a DBMS Table with a WHERE clause
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 about international flights with over 200 passengers. Note that the output is sorted by using a PROC SQL query and that the TITLE, LABEL, and FORMAT keywords are not ANSI standard SQL; they are SAS extensions that you can use in PROC SQL.
libname mydblib 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 datetime9., dest label="Destination", boarded label="Number Boarded" from mydblib.internat where boarded > 200 order by flight; quit;
Querying a DBMS Table with SAS Extensions
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
| 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 who earn more than $40,000.
libname mydblib oracle user=testuser password=testpass; title 'Employees with salary greater than $40,000'; options obs=20; proc sql; select a.lname, a.fname, b.salary format=dollar10.2 from mydblib.staff a, mydblib.payroll b where (a.idnum eq b.idnum) and (b.salary gt 40000); quit;
Note: By default, SAS/ACCESS passes
the entire join to the DBMS for processing in order to optimize performance.
See Passing Joins to the DBMS for more information.
Querying Multiple Oracle Tables
Employees with salary greater than $40,000
LNAME FNAME SALARY
--------------------------------------------
WELCH DARIUS $40,858.00
VENTER RANDALL $66,558.00
THOMPSON WAYNE $89,977.00
RHODES JEREMY $40,586.00
DENNIS ROGER $111379.00
KIMANI ANNE $40,899.00
O'NEAL BRYAN $40,079.00
RIVERS SIMON $53,798.00
COHEN LEE $91,376.00
GREGORSKI DANIEL $68,096.00
NEWKIRK WILLIAM $52,279.00
ROUSE JEREMY $43,071.00
The next example uses the SQL procedure to join and query the DB2 tables March, Delay, and Flight. The query retrieves information about delayed international flights during the month of March.
libname mydblib db2 ssid=db2; title "Delayed International Flights in March"; proc sql; select distinct march.flight, march.dates format datetime9., delay format=2.0 from mydblib.march, mydblib.delay, mydblib.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: By default, SAS/ACCESS passes
the entire join to the DBMS for processing in order to optimize performance.
See Passing Joins to the DBMS for more information.
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 mydblib oracle user=testuser password=testpass; title "Payrolls 1 & 2"; proc sql; select idnum, sex, jobcode, salary, birth format datetime9., hired format datetime9. from mydblib.payroll outer union corr select * from mydblib.payroll2 order by idnum, jobcode, salary; quit;
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 mydblib db2 ssid=db2; proc sql; insert into mydblib.superv values('1588','NY','FA'); quit; proc print data=mydblib.superv; title "New Row in AIRLINE.SUPERV"; run;
Note: Depending on how your DBMS processes
insert, the new row might not be added as the last physical row of the table.
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 mydblib db2 ssid=db2; proc sql; delete from mydblib.staff where state='CT'; quit; options obs=20; proc print data=mydblib.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.
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 mydblib oracle user=testuser password=testpass; proc sql; create table mydblib.gtforty as select lname as lastname, fname as firstname, salary as Salary format=dollar10.2 from mydblib.staff a, mydblib.payroll b where (a.idnum eq b.idnum) and (salary gt 40000); quit; options obs=20; proc print data=mydblib.gtforty noobs; title 'Employees with salaries over $40,000'; format salary dollar10.2; run;
Employees with salaries over $40,000
LASTNAME FIRSTNAME SALARY
WELCH DARIUS $40,858.00
VENTER RANDALL $66,558.00
MARSHBURN JASPER $89,632.00
THOMPSON WAYNE $89,977.00
RHODES JEREMY $40,586.00
KIMANI ANNE $40,899.00
CASTON FRANKLIN $41,690.00
STEPHENSON ADAM $42,178.00
BANADYGA JUSTIN $88,606.00
O'NEAL BRYAN $40,079.00
RIVERS SIMON $53,798.00
MORGAN ALFRED $42,264.00
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.