SAS/ACCESS Software for PC File Formats: Reference

Updating a SAS Data File with PC Files Data

You can update a SAS data file with DBF file data that are 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 EMPLOYEE.DBF file. To perform this update, enter the following SAS statements:

options linesize=80;

proc access dbms=dbf;
   create adlib.employ.access;     
   /* create access descriptor   */
   path="c:\sasdemo\employee.dbf";
   assign=yes;
   format empid 6.
          salary dollar12.2
          jobcode 5.
          hiredate date9.
          birthdate date9.;
   list all;

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

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

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

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

proc sort data=vlib.empbday out=work.empbirth;
   by lastname;
run;

data dlib.newbday;
   update dlib.birthday work.empbirth;
   by lastname;
run;

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

In this example, a PROC SORT statement with the OUT= option extracts DBF file data, places them in the SAS data file WORK.EMPBIRTH, and sorts the data by the LASTNAME variable. (When using a DATA step, PC files data must be extracted before you can sort them.) When the UPDATE statement references the SAS data file WORK.EMPBIRTH and you use a BY statement 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 DBF 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, DBF File 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 254196 06APR1949 CHANG 2 459288 05JAN1934 JOHNSON 3 127815 25DEC1943 WOLOSCHUK

DBF File Data Described by the View Descriptor VLIB.EMPBDAY

Data Described by VLIB.EMPBDAY OBS EMPID BIRTHDAT LASTNAME FIRSTNAM PHONE 1 119012 05JAN1946 WOLF-PROVENZA G. 3467 2 120591 12FEB1946 HAMMERSTEIN S. 3287 3 123456 28AUG1959 VARGAS CHRIS 4 127845 25DEC1943 MEDER VLADIMIR 6231 5 129540 31JUL1960 CHOULAI CLARA 3921 6 135673 21MAR1961 HEMESLY STEPHANIE 6329 7 212916 29MAY1928 WACHBERGER MARIE-LOUISE 8562 8 216382 24JUL1963 PURINTON PRUDENCE 3852 9 234967 21DEC1967 SMITH GILBERT 7274 10 237642 13MAR1954 BATTERSBY R. 8342 11 239185 28AUG1959 DOS REMEDIOS LEONARD 4892 12 254896 06APR1949 TAYLOR-HUNYADI ITO 0231 13 321783 03JUN1935 GONZALES GUILLERMO 3642 14 328140 02JUN1951 MEDINA-SIDONIA MARGARET 5901 15 346917 15MAR1950 SHIEKELESLAM SHALA 8745 16 356134 25OCT1960 DUNNETT CHRISTINE 4213 17 423286 31OCT1964 MIFUNE YUKIO 3278 18 456910 24SEP1953 ARDIS RICHARD 4351 19 456921 12MAY1962 KRAUSE KARL-HEINZ 7452 20 457232 15OCT1963 LOVELL WILLIAM 6321 21 459287 05JAN1934 RODRIGUES JUAN 5879 22 677890 24APR1965 NISHIMATSU-LYNCH CAROL 6245 23 346917 15MAR1950 SHIEKELESLAM SHALA

Data in the Updated Data File DLIB.NEWBDAY

DLIB.NEWBDAY Data File OBS EMPID BIRTHDAT LASTNAME FIRSTNAM PHONE 1 456910 24SEP1953 ARDIS RICHARD 4351 2 237642 13MAR1954 BATTERSBY R. 8342 3 254196 06APR1949 CHANG 4 129540 31JUL1960 CHOULAI CLARA 3921 5 239185 28AUG1959 DOS REMEDIOS LEONARD 4892 6 356134 25OCT1960 DUNNETT CHRISTINE 4213 7 321783 03JUN1935 GONZALES GUILLERMO 3642 8 120591 12FEB1946 HAMMERSTEIN S. 3287 9 135673 21MAR1961 HEMESLY STEPHANIE 6329 10 459288 05JAN1934 JOHNSON 11 456921 12MAY1962 KRAUSE KARL-HEINZ 7452 12 457232 15OCT1963 LOVELL WILLIAM 6321 13 127845 25DEC1943 MEDER VLADIMIR 6231 14 328140 02JUN1951 MEDINA-SIDONIA MARGARET 5901 15 423286 31OCT1964 MIFUNE YUKIO 3278 16 677890 24APR1965 NISHIMATSU-LYNCH CAROL 6245 17 216382 24JUL1963 PURINTON PRUDENCE 3852 18 459287 05JAN1934 RODRIGUES JUAN 5879 19 346917 15MAR1950 SHIEKELESLAM SHALA 8745 20 234967 21DEC1967 SMITH GILBERT 7274 21 254896 06APR1949 TAYLOR-HUNYADI ITO 0231 22 123456 28AUG1959 VARGAS CHRIS 23 212916 29MAY1928 WACHBERGER MARIE-LOUISE 8562 24 119012 05JAN1946 WOLF-PROVENZA G. 3467 25 127815 25DEC1943 WOLOSCHUK

Chapter Contents
Previous
Next
Top of Page