 |
|
| Accessing DBMS Data with the LIBNAME Statement |
Creating SAS Data Sets from DBMS Data
| Overview |
After you associate a SAS/ACCESS libref with your DBMS data, you can use the libref just as you would use any SAS libref. The following examples illustrate basic uses of the DATA step with librefs that reference DBMS data.
| Using the PRINT Procedure with DBMS Data |
In the following example, the interface to DB2 creates the libref MyDbLib and associates the libref with tables and views that reside on DB2. The DATA= option specifies a libref that references DB2 data. The PRINT procedure prints a New Jersey staff phone list from the DB2 table Staff. Information for staff from states other than New Jersey is not printed. The DB2 table Staff is not modified.
libname mydblib db2 ssid=db2; proc print data=mydblib.staff (keep=lname fname state hphone); where state = 'NJ'; title 'New Jersey Phone List'; run;
Using the PRINT Procedure with DBMS Data
New Jersey Phone List 1
Obs LNAME FNAME STATE HPHONE
1 ALVAREZ CARLOS NJ 201/732-8787
2 BAREFOOT JOSEPH NJ 201/812-5665
3 DACKO JASON NJ 201/732-2323
4 FUJIHARA KYOKO NJ 201/812-0902
5 HENDERSON WILLIAM NJ 201/812-4789
6 JOHNSON JACKSON NJ 201/732-3678
7 LAWRENCE KATHY NJ 201/812-3337
8 MURPHEY JOHN NJ 201/812-4414
9 NEWKIRK SANDRA NJ 201/812-3331
10 NEWKIRK WILLIAM NJ 201/732-6611
11 PETERS RANDALL NJ 201/812-2478
12 RHODES JEREMY NJ 201/812-1837
13 ROUSE JEREMY NJ 201/732-9834
14 VICK THERESA NJ 201/812-2424
15 YANCEY ROBIN NJ 201/812-1874
| Combining DBMS Data and SAS Data |
The following example shows how to read DBMS data into SAS and create additional variables to perform calculations or subsetting operations on the data. The example creates the SAS data set Work.HighWage from the DB2 table Payroll and adds a new variable, Category. The Category variable is based on the value of the salary column in the DB2 table Payroll. The Payroll table is not modified.
libname mydblib db2 ssid=db2; data highwage; set mydblib.payroll(drop=sex birth hired); if salary>60000 then CATEGORY="High"; else if salary<30000 then CATEGORY="Low"; else CATEGORY="Avg"; run; options obs=20; proc print data=highwage; title "Salary Analysis"; format salary dollar10.2; run;
Combining DBMS Data and SAS Data
Salary Analysis 1
OBS IDNUM JOBCODE SALARY CATEGORY
1 1919 TA2 $34,376.00 Avg
2 1653 ME2 $35,108.00 Avg
3 1400 ME1 $29,769.00 Low
4 1350 FA3 $32,886.00 Avg
5 1401 TA3 $38,822.00 Avg
6 1499 ME3 $43,025.00 Avg
7 1101 SCP $18,723.00 Low
8 1333 PT2 $88,606.00 High
9 1402 TA2 $32,615.00 Avg
10 1479 TA3 $38,785.00 Avg
11 1403 ME1 $28,072.00 Low
12 1739 PT1 $66,517.00 High
13 1658 SCP $17,943.00 Low
14 1428 PT1 $68,767.00 High
15 1782 ME2 $35,345.00 Avg
16 1244 ME2 $36,925.00 Avg
17 1383 BCK $25,823.00 Low
18 1574 FA2 $28,572.00 Low
19 1789 SCP $18,326.00 Low
20 1404 PT2 $91,376.00 High
| Reading Data from Multiple DBMS Tables |
You can use the DATA step to read data from multiple data sets. This example merges data from the two Oracle tables Staff and SuperV in the SAS data set Work.Combined.
libname mydblib oracle user=testuser password=testpass path='@alias'; data combined; merge mydblib.staff mydblib.superv(in=super rename=(supid=idnum)); by idnum; if super; run; proc print data=combined; title "Supervisor Information"; run;
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 UPDATE Statement with DBMS Data |
You can also use the DATA step 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;
Creating a SAS Data Set with DBMS Data by Using the UPDATE Statement
Updated Payroll Data 1
Obs IDNUM SEX JOBCODE SALARY BIRTH HIRED
1 1009 M TA1 28880 02MAR1959 26MAR1992
2 1017 M TA3 40858 28DEC1957 16OCT1981
3 1036 F TA3 42465 19MAY1965 23OCT1984
4 1037 F TA1 28558 10APR1964 13SEP1992
5 1038 F TA1 26533 09NOV1969 23NOV1991
6 1050 M ME2 35167 14JUL1963 24AUG1986
7 1065 M ME3 38090 26JAN1944 07JAN1987
8 1076 M PT1 69742 14OCT1955 03OCT1991
9 1094 M FA1 22268 02APR1970 17APR1991
10 1100 M BCK 25004 01DEC1960 07MAY1988
11 1101 M SCP 18723 06JUN1962 01OCT1990
12 1102 M TA2 34542 01OCT1959 15APR1991
13 1103 F FA1 23738 16FEB1968 23JUL1992
14 1104 M SCP 17946 25APR1963 10JUN1991
15 1105 M ME2 34805 01MAR1962 13AUG1990
16 1106 M PT3 94039 06NOV1957 16AUG1984
17 1107 M PT2 89977 09JUN1954 10FEB1979
18 1111 M NA1 40586 14JUL1973 31OCT1992
19 1112 M TA1 26905 29NOV1964 07DEC1992
20 1113 F FA1 22367 15JAN1968 17OCT1991
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.