Accessing DBMS Data with the LIBNAME Statement Using the DATA Step’s UPDATE Statment with DBMS Data 245
Output 13.3 Reading Data from Multiple DBMS Tables
Supervisor Information 1
Obs IDNUM LNAME FNAME CITY STATE HPHONE JOBCAT
1 1106 MARSHBURN JASPER STAMFORD CT 203/781-1457 PT
2 1118 DENNIS ROGER NEW YORK NY 718/383-1122 PT
3 1126 KIMANI ANNE NEW YORK NY 212/586-1229 TA
4 1352 RIVERS SIMON NEW YORK NY 718/383-3345 NA
5 1385 RAYNOR MILTON BRIDGEPORT CT 203/675-2846 ME
6 1401 ALVAREZ CARLOS PATERSON NJ 201/732-8787 TA
7 1405 DACKO JASON PATERSON NJ 201/732-2323 SC
8 1417 NEWKIRK WILLIAM PATERSON NJ 201/732-6611 NA
9 1420 ROUSE JEREMY PATERSON NJ 201/732-9834 ME
10 1431 YOUNG DEBORAH STAMFORD CT 203/781-2987 FA
11 1433 YANCEY ROBIN PRINCETON NJ 201/812-1874 FA
12 1442 NEWKIRK SANDRA PRINCETON NJ 201/812-3331 PT
13 1564 WALTERS ANNE NEW YORK NY 212/587-3257 SC
14 1639 CARTER-COHEN KAREN STAMFORD CT 203/781-8839 TA
15 1677 KRAMER JACKSON BRIDGEPORT CT 203/675-7432 BC
16 1834 LEBLANC RUSSELL NEW YORK NY 718/384-0040 BC
17 1882 TUCKER ALAN NEW YORK NY 718/384-0216 ME
18 1935 FERNANDEZ KATRINA BRIDGEPORT CT 203/675-2962 NA
19 1983 DEAN SHARON NEW YORK NY 718/384-1647 FA
Using the DATA Step’s UPDATE Statment with DBMS Data
You can also use the DATA step’s UPDATE statement to create a SAS data set with
DBMS data. This example creates the SAS data set Work.Payroll with data from the
Oracle tables Payroll and Payroll2. The Oracle tables are not modified.
The columns in the two Oracle tables must match; however, Payroll2 can have
additional columns. Any additional columns in Payroll2 are added to the Payroll data
set. The UPDATE statement requires unique values for IdNum to correctly merge the
data from Payroll2.
libname mydblib oracle user=testuser password=testpass;
data payroll;
update mydblib.payroll
mydblib.payroll2;
by idnum;
proc print data=payroll;
format birth datetime9. hired datetime9.;
title ’Updated Payroll Data’;
run;
Partial output from this example is shown here.
