SAS/ACCESS Software for Relational Databases: Reference

Updating a SAS Data File with DBMS Data

You can update a SAS data file with DBMS data that is described by a view descriptor just as you can update a SAS data file with data from another SAS data file.

Suppose you have a SAS data set, DLIB.BIRTHDAY, that contains employee ID numbers, last names, and birthdays. (See Sample Data for a description of DLIB.BIRTHDAY.) You want to update this data set with data described by VLIB.EMPBDAY, a view descriptor that is based on the DB2 table SASDEMO.EMPLOYEES. To perform this update, enter the following SAS statements:

options linesize=80;

proc access dbms=db2;
/* create access descriptor   */
   create adlib.employ.access;
   ssid=db2;
   table=sasdemo.employees;
   assign=yes;
   format empid 6.
          salary dollar12.2
          jobcode 5.
          hiredate date9.
          birthdate date9.;
   list all;

/* create view descriptor   */
   create vlib.empbday.view;
   select empid birthdate lastname
          firstname phone;
run;

proc sort data=dlib.birthday;
   by lastname;
run;

/* examples                */
proc print data=dlib.birthday;
   format birthdat date9.;
   title 'DLIB.BIRTHDAY Data File';
run;

proc print data=vlib.empbday;
   format birthdat date9.;
   title 'Data Described by VLIB.EMPBDAY';
run;

data dlib.newbday;
   update dlib.birthday vlib.empbday;
   by lastname;
run;

proc print;
   format birthdat date9.;
   title 'DLIB.NEWBDAY Data File';
run;

When the UPDATE statement references the view descriptor VLIB.EMPBDAY, and a BY statement is used in the DATA step, the BY statement causes the interface view engine to generate an ORDER BY clause for the variable LASTNAME. Thus, the ORDER BY clause causes the DBMS data to be presented to the SAS System in sorted order for use in updating the DLIB.NEWBDAY data file. However, the SAS data file DLIB.BIRTHDAY must be sorted before the update because the UPDATE statement expects both the original file and the transaction file to be sorted by the same BY variable.

Data File to Be Updated, DLIB.BIRTHDAY, AS/400 Data Described by the View Descriptor VLIB.EMPBDAY, and Data in the Updated Data File DLIB.NEWBDAY show the results of the PRINT procedures.

Data File to Be Updated, DLIB.BIRTHDAY

DLIB.BIRTHDAY Data File OBS EMPID BIRTHDAT LASTNAME 1 127845 25DEC1949 MEDER 2 459287 05JUN1939 RODRIGUES 3 254896 06APR1951 TAYLOR-HUNYADI

AS/400 Data Described by the View Descriptor VLIB.EMPBDAY

Data Described by VLIB.EMPBDAY OBS EMPID BIRTHDAT LASTNAME FIRSTNAM PHONE 1 119012 05JAN1951 WOLF-PROVENZA G. 3467 2 120591 12FEB1951 HAMMERSTEIN S. 3287 3 123456 . VARGAS CHRIS 4 127845 25DEC1948 MEDER VLADIMIR 6231 5 129540 31JUL1965 CHOULAI CLARA 3921 6 135673 21MAR1966 HEMESLY STEPHANIE 6329 7 212916 29MAY1933 WACHBERGER MARIE-LOUISE 8562 8 216382 24JUL1968 PURINTON PRUDENCE 3852 9 234967 21DEC1972 SMITH GILBERT 7274 10 237642 13MAR1959 BATTERSBY R. 8342 11 239185 28AUG1964 DOS REMEDIOS LEONARD 4892 12 254896 06APR1954 TAYLOR-HUNYADI ITO 0231 13 321783 03JUN1940 GONZALES GUILLERMO 3642 14 328140 02JUN1956 MEDINA-SIDONIA MARGARET 5901 15 346917 15MAR1955 SHIEKELESLAM SHALA 8745 16 356134 25OCT1965 DUNNETT CHRISTINE 4213 17 423286 31OCT1969 MIFUNE YUKIO 3278 18 456910 24SEP1958 ARDIS RICHARD 4351 19 456921 12MAY1967 KRAUSE KARL-HEINZ 7452 20 457232 15OCT1968 LOVELL WILLIAM 6321 21 459287 05JAN1939 RODRIGUES JUAN 5879 22 677890 24APR1970 NISHIMATSU-LYNCH CAROL 6245

Data in the Updated Data File DLIB.NEWBDAY

DLIB.NEWBDAY Data File OBS EMPID BIRTHDAT LASTNAME FIRSTNAM PHONE 1 456910 24SEP1958 ARDIS RICHARD 4351 2 237642 13MAR1959 BATTERSBY R. 8342 3 129540 31JUL1965 CHOULAI CLARA 3921 4 239185 28AUG1964 DOS REMEDIOS LEONARD 4892 5 356134 25OCT1965 DUNNETT CHRISTINE 4213 6 321783 03JUN1940 GONZALES GUILLERMO 3642 7 120591 12FEB1951 HAMMERSTEIN S. 3287 8 135673 21MAR1966 HEMESLY STEPHANIE 6329 9 456921 12MAY1967 KRAUSE KARL-HEINZ 7452 10 457232 15OCT1968 LOVELL WILLIAM 6321 11 127845 25DEC1948 MEDER VLADIMIR 6231 12 328140 02JUN1956 MEDINA-SIDONIA MARGARET 5901 13 423286 31OCT1969 MIFUNE YUKIO 3278 14 677890 24APR1970 NISHIMATSU-LYNCH CAROL 6245 15 216382 24JUL1968 PURINTON PRUDENCE 3852 16 459287 05JAN1939 RODRIGUES JUAN 5879 17 346917 15MAR1955 SHIEKELESLAM SHALA 8745 18 234967 21DEC1972 SMITH GILBERT 7274 19 254896 06APR1954 TAYLOR-HUNYADI ITO 0231 20 123456 . VARGAS CHRIS 21 212916 29MAY1933 WACHBERGER MARIE-LOUISE 8562 22 119012 05JAN1951 WOLF-PROVENZA G. 3467

Chapter Contents
Previous
Next
Top of Page