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

Using a DATA Step UPDATE Statement

You can update a SAS data file with CA-Datacom/DB data that is 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 is to be added to the original file. Because the SAS/ACCESS interface to CA-Datacom/DB uses the SAS 6 compatibility engine, the transaction data is from a SAS 6 source. The original file can be a SAS 6 data file or a SAS 8 and later data file.

Updating a SAS 6 Data File

You can update a SAS 6 version of a SAS data file with CA-Datacom/DB data the same way you did in SAS 6. Suppose you have a SAS 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 SAS 6 data file. It is stored in the SAS 6 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 SAS in a sorted order so that it 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.

The following output shows 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 That Is 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 SAS 8 and Later Data File

SAS 6, SAS 8, and later versions of SAS 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 SAS 8 and later 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 SAS 8 and later 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 SAS 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 SAS 8 data file that is stored in a SAS 8 library associated with the libref NewData. SAS 8 and later 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.

The following output 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 about the UPDATE statement, see the SAS Statements: Reference.

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