SAS/ACCESS Software for Relational Databases: Reference

Creating SAS Data Sets from DBMS Data

Once you have associated 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 refer to DBMS data.

Using the PRINT Procedure with DBMS Data

In the following example, the libref MYDB2LIB is assigned with the DB2 engine to associate the libref with tables and views that reside on DB2. The PRINT procedure prints a phone list containing information for staff in New Jersey from the DB2 table STAFF. Information for staff from states other than New Jersey is not printed. The DB2 table STAFF is not modified.

Note that you can specify a libref that references DBMS data in the DATA= option.

libname mydb2lib db2 ssid=db2;

proc print data=mydb2lib.staff
  (keep=lname fname hphone state);
  where state = 'NJ';
  title 'New Jersey Phone List';
run;

Output for this example is shown in Output Listing from the PRINT Procedure.

Output Listing from the PRINT Procedure

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.

This 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 mydb2lib db2 ssid=db2;     
                              
data highwage;                                            
  set mydb2lib.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;

Partial output for this example is shown in Combining DBMS Data and SAS Data.

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, in this case, two DBMS tables. This example merges data from the two ORACLE tables STAFF and SUPERV in the SAS data set WORK.COMBINED. Notice that the PATH= statement includes an alias to the database, as required by ORACLE SQL*NET software.

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

data combined;
  merge myoralib.staff myoralib.superv(in=super 
    rename=(supid=idnum));
  by idnum;
  if super;
run;

proc print data=combined;
  title "Supervisor Information";
run;

Note: The PRESERVE_COL_NAMES=YES LIBNAME option retains the lowercased column names from ORACLE when creating the corresponding SAS variable names. For information on additional new LIBNAME and data set options, see SAS/ACCESS LIBNAME Statement and SAS/ACCESS Data Set Options. [cautionend]

Output for this example is shown in Reading Data from Multiple DBMS Tables.

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.

Note that the columns in the two ORACLE tables must match; however, PAYROLL2 may have additional columns. Any additional columns in PAYROLL2 are added to the PAYROLL data set. Also, the UPDATE statement requires unique values for IDNUM to correctly merge the data from PAYROLL2.

libname myoralib oracle user=scott password=tiger path='myorapath';

data payroll;
  update myoralib.payroll
         myoralib.payroll2;
  by idnum;

proc print data=payroll;
  format birth datetime9. hired datetime9.;
  title 'Updated Payroll Data';
run;

Partial output from this example is shown in Creating a SAS Data Set with DBMS Data by Using the UPDATE Statement.

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

Chapter Contents
Previous
Next
Top of Page