SAS/ACCESS Interface to CA-DATACOM/DB: Reference

Updating a SAS Data File with CA-DATACOM/DB Data

You can update a SAS data file with CA-DATACOM/DB data described by a view descriptor the same way you update a SAS data file with data from another data file: by using a DATA step UPDATE statement. In this section, the term transaction data refers to the new data that are to be added to the original file. Because the SAS/ACCESS interface to CA-DATACOM/DB uses the Version 6 compatibility engine, the transaction data are from a Version 6 source. The original file can be a Version 6 data file or a Version 8 data file.

Updating a Version 6 Data File

You can update a Version 6 SAS data file with CA-DATACOM/DB data the same way you did in Version 6 of the SAS System. Suppose you have a Version 6 data file, LIB6.BIRTHDAY, that contains employee ID numbers, last names, and birthdays. You want to update this data file with data described by VLIB.EMPS, a view descriptor based on the CA-DATACOM/DB table EMPLOYEES. To perform the update, enter the following SAS code:

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

proc print data=lib6.birthday;
   format birthdat date7.;
   title 'LIB6.BIRTHDAY Data File';
run;

proc print data=vlib.emps;
   title 'Data Described by VLIB.EMPS';
run;

data mydata.newbday;
   update lib6.birthday vlib.emps;
   by lastname;
run;

proc print;
   title 'MYDATA.NEWBDAY Data File';
run;

In this example, the updated SAS data file, MYDATA.NEWBDAY, is a Version 6 data file. It is stored in the Version 6 SAS data library associated with the libref MYDATA.

When the UPDATE statement references the view descriptor VLIB.EMPS and uses a BY statement in the DATA step, the BY statement causes the interface view engine to automatically generate a SORT clause for the column LASTNAME. Thus, the SORT clause causes the CA-DATACOM/DB data to be presented to the SAS System in a sorted order so they can be used to update the MYDATA.NEWBDAY data file. The data file LIB6.BIRTHDAY had to be sorted (by the SAS SORT procedure) before the update, because the UPDATE statement expects the data to be sorted by the BY column.

Data File To Be Updated, LIB6.BIRTHDAY, Data Described by VLIB.EMPS, and Updated Data File, MYDATA. NEWBDAY show the results of the PRINT procedure on the original data file, the transaction data, and the updated data file.

Data File To Be Updated, LIB6.BIRTHDAY

LIB6.BIRTHDAY Data File 1 OBS EMPID BIRTHDAT LASTNAME 1 129540 31JUL60 CHOULAI 2 356134 25OCT60 DUNNETT 3 127845 25DEC43 MEDER 4 677890 24APR65 NISHIMATSU-LYNCH 5 459287 05JAN34 RODRIGUES 6 346917 15MAR50 SHIEKELESLAN 7 254896 06APR49 TAYLOR-HUNYADI

Data Described by VLIB.EMPS

Data Described by VLIB.EMPS 1 OBS EMPID JOBCODE BIRTHDAT LASTNAME 1 456910 602 24SEP53 ARDIS 2 237642 602 13MAR54 BATTERSBY 3 239185 602 28AUG59 DOS REMEDIOS 4 321783 602 03JUN35 GONZALES 5 120591 602 12FEB46 HAMMERSTEIN 6 135673 602 21MAR61 HEMESLY 7 456921 602 12MAY62 KRAUSE 8 457232 602 15OCT63 LOVELL 9 423286 602 31OCT64 MIFUNE 10 216382 602 24JUL63 PURINTON 11 234967 602 21DEC67 SMITH 12 212916 602 29MAY28 WACHBERGER 13 119012 602 05JAN46 WOLF-PROVENZA

Updated Data File, MYDATA. NEWBDAY

MYDATA.NEWBDAY Data File 1 OBS EMPID BIRTHDAT LASTNAME JOBCODE 1 456910 24SEP53 ARDIS 602 2 237642 13MAR54 BATTERSBY 602 3 129540 31JUL60 CHOULAI . 4 239185 28AUG59 DOS REMEDIOS 602 5 356134 25OCT60 DUNNETT . 6 321783 03JUN35 GONZALES 602 7 120591 12FEB46 HAMMERSTEIN 602 8 135673 21MAR61 HEMESLY 602 9 456921 12MAY62 KRAUSE 602 10 457232 15OCT63 LOVELL 602 11 127845 25DEC43 MEDER . 12 423286 31OCT64 MIFUNE 602 13 677890 24APR65 NISHIMATSU-LYNCH . 14 216382 24JUL63 PURINTON 602 15 459287 05JAN34 RODRIGUES . 16 346917 15MAR50 SHIEKELESLAN . 17 234967 21DEC67 SMITH 602 18 254896 06APR49 TAYLOR-HUNYADI . 19 212916 29MAY28 WACHBERGER 602 20 119012 05JAN46 WOLF-PROVENZA 602

Updating a Version 8 Data File

Versions 6 and 8 of the SAS System support different naming conventions, therefore, there could be character-length discrepancies between the columns in the original data file and the transaction data. You have two choices when updating a Version 8 data file:

let the compatibility engine truncate names exceeding 8 characters. The truncated names will be added to the updated data file as new columns.
rename the columns in the Version 8 data file to match the columns in the descriptor file.

The following example resolves character-length discrepancies by using the RENAME DATA step option with the UPDATE statement. A Version 8 data file, LIB8.BIRTHDAYS, is updated with data described by VLIB.EMPS.

proc sort data=lib8.birthdays;
  by last_name;
run;

proc print data=lib8.birthdays;   
    format birthdate date7.; 
    title 'LIB8.BIRTHDAYS Data File';
run;

data newdata.v8_birthdays;
   update lib8.birthday
   (rename= (last_name=lastname
             firstname=firstnme
             birthdate=birthdat)) vlib.emps;
by lastname firstnme;
run;

proc print data=newdata.v8_birthdays;
   title 'NEWDATA.V8_BIRTHDAYS Data File';
run;

In this example, the updated data file NEWDATA.V8_BIRTHDAYS is a Version 8 data file that is stored in a Version 8 data library associated with the libref NEWDATA. Version 8 supports member and column names of up to 32 characters. However, the RENAME= DATA step option is used with the UPDATE statement to change the longer column names in LIB8.BIRTHDAYS to match the 8-character column names in VLIB.EMPS. The columns are renamed before the updated data file is created.

Data File to be Updated, LIB8.BIRTHDAYS shows the results of the PRINT procedure on the original data file. The updated file looks like Updated Data File, MYDATA. NEWBDAY.

Data File to be Updated, LIB8.BIRTHDAYS

LIB8.BIRTHDAYS Data File 1 OBS EMPLOYEE_ID BIRTHDATE LAST_NAME 1 129540 31JUL60 CHOULAI 2 356134 25OCT60 DUNNETT 3 127845 25DEC43 MEDER 4 677890 24APR65 NISHIMATSU-LYNCH 5 459287 05JAN34 RODRIGUES 6 346917 15MAR50 SHIEKELESLAN 7 254896 06APR49 TAYLOR-HUNYADI

For more information on the UPDATE statement, see the SAS Language Reference: Dictionary.

You cannot update a CA-DATACOM/DB table directly using the DATA step, but you can update a CA-DATACOM/DB table using the following procedures: APPEND, FSEDIT, FSVIEW, SQL, and SAS/AF applications. See Browsing and Updating CA-DATACOM/DB Data for more information on updating CA-DATACOM/DB data.

Chapter Contents
Previous
Next
Top of Page