Examples: Create SAS Data Sets from DBMS Data :: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

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. These examples show basic uses of the DATA step with librefs that reference DBMS data.

Example 1: Use the PRINT Procedure with DBMS Data

In this example, the DB2 interface creates the MyDbLib libref and associates it 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 Staff DB2 table. Information for staff from states other than New Jersey is not printed, and the Staff DB2 table 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;

Use the PRINT Procedure with DBMS Data

                     New Jersey Phone List

     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

Example 2: Combine DBMS Data and SAS Data

This example shows how to read DBMS data into SAS and create additional variables to perform calculations or subsetting operations on the data. It creates the Work.HighWage SAS data set from the Payroll DB2 table, and it adds a new variable, Category. This variable is based on the value of the salary column in the Payroll DB2 table. 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;

Combine DBMS Data and SAS Data

                     Salary Analysis

        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

Example 3: Read Data from Multiple DBMS Tables

You can use the DATA step to read data from multiple data sets. This example merges data from the Staff and SuperV Oracle tables in the Work.Combined SAS data set.

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;

Read Data from Multiple DBMS Tables

                               Supervisor Information

     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

Example 4: Use 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 Work.Payroll SAS data set with data from the Payroll and Payroll2 Oracle tables. The Oracle tables are not modified.

The columns in the two Oracle tables must match, but 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;

Create a SAS Data Set with DBMS Data By Using the UPDATE Statement

                                Updated Payroll Data

             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