Updating SAS Data Files with SYSTEM 2000 Data

Using the UPDATE Statement

You can update a SAS data file with SYSTEM 2000 data that is described by a view descriptor just as you can update a SAS data file by using another data file, that is, by using an UPDATE statement in a DATA step. In this section, the term transaction data refers to the new data that is added to the original file. Because the SAS/ACCESS interface to SYSTEM 2000 uses the SAS 6 compatibility engine, the transaction data is from a SAS 6 source. However, the original file can be a SAS 6 or later data file.

Suppose you have the SAS 6 data file V6.Birthdy that contains the names and birthdays of the employees in Marketing. The file is out-of-date, and you want to update it with data described by Vlib.EmpBd. To perform the update, submit the following program:

    proc sort data=v6.birthdy;
       by lastname;
    run; 
 
    data mydata.newbday;
       update v6.birthdy vlib.empbd;
       by lastname firstnme;
    run;

In this example, when the UPDATE statement references the view descriptor Vlib.EmpBd and uses a BY statement in the DATA step, the BY statement causes the interface view engine to automatically generate a SYSTEM 2000 ordering-clause for the variable LASTNAME. The ordering-clause causes the SYSTEM 2000 data to be presented to SAS already sorted so that the SYSTEM 2000 DATA can be used to update the data file MyData.NewBday. The data file V6.Birthdy had to be sorted before the update because the UPDATE statement needs the data sorted by the BY variable.

The following three outputs show the results of PROC PRINT on the original data file, the transaction data, and the updated data file.

Data File to Be Updated, V6.Birthdy

                     V6.BIRTHDY Data File                        1
 
           OBS    LASTNAME     FIRSTNME     BIRTHDAY
 
             1    JONES        FRANK        22MAY53
             2    MCVADE       CURTIS       25DEC54
             3    SMITH        VIRGINIA     14NOV49
             4    TURNER       BECKY        26APR50

Data Described by the View Descriptor Vlib.EmpBd

                   Data Described by VLIB.EMPBD                     1
 
           OBS    LASTNAME       FIRSTNME      BIRTHDAY
 
             1    AMEER          DAVID         10OCT51
             2    BROOKS         RUBEN R.      25FEB52
             3    BROWN          VIRGINA P.    24MAY46
             4    CHAN           TAI           04JUL46
             5    GARRETT        OLAN M.       23JAN35
             6    GIBSON         GEORGE J.     23APR46
             7    GOODSON        ALAN F.       21JUN50
             8    JUAREZ         ARMANDO       28MAY47
             9    LITTLEJOHN     FANNIE        17MAY54
            10    RICHARDSON     TRAVIS Z.     30NOV37
            11    RODRIGUEZ      ROMUALDO R    09FEB29
            12    SCHOLL         MADISON A.    19MAR45
            13    SHROPSHIRE     LELAND G.     04SEP49
            14    SMITH          JERRY LEE     13SEP42
            15    VAN HOTTEN     GWENDOLYN     13SEP42
            16    WAGGONNER      MERRILEE D    27APR36
            17    WILLIAMSON     JANICE L.     19MAY52

Updated Data File, MyData.NewBday

                       MYDATA.NEWBDAY Data File                     1
 
           OBS    LASTNAME      FIRSTNME      BIRTHDAY
 
             1    AMEER         DAVID         10OCT51
             2    BROOKS        RUBEN R.      25FEB52
             3    BROWN         VIRGINA P.    24MAY46
             4    CHAN          TAI           04JUL46
             5    GARRETT       OLAN M.       23JAN35
             6    GIBSON        GEORGE J.     23APR46
             7    GOODSON       ALAN F.       21JUN50
             8    JONES         FRANK         22MAY53
             9    JUAREZ        ARMANDO       28MAY47
            10    LITTLEJOHN    FANNIE        17MAY54
            11    MCVADE        CURTIS        25DEC54
            12    RICHARDSON    TRAVIS Z.     30NOV37
            13    RODRIGUEZ     ROMUALDO R    09FEB29
            14    SCHOLL        MADISON A.    19MAR45
            15    SHROPSHIRE    LELAND G.     04SEP49
            16    SMITH         JERRY LEE     13SEP42
            17    SMITH         VIRGINIA      14NOV49
            18    TURNER        BECKY         26APR50
            19    VAN HOTTEN    GWENDOLYN     13SEP42
            20    WAGGONNER     MERRILEE D    27APR36
            21    WILLIAMSON    JANICE L.     19MAY52

Updating Data Files in SAS 7 and Later

Beginning with SAS 7, SAS supports different naming conventions than those used in SAS 6. Therefore, there might be character-length discrepancies between the variables in an original data file and the transaction data. You have two choices when updating a SAS 7 and later data file with the data described by a view descriptor:

let the compatibility engine truncate names that exceed eight characters. The truncated variables are added to the updated data file as new variables.
rename the variables in the data file in SAS 7 and later to match the variable names in the descriptor file.

The following program resolves character-length discrepancies by using the RENAME option in the UPDATE statement in the DATA step. The SAS 7 data file V7.Consulting_Birthdays, which contains Consulting names and birthdays, is updated with data described by Vlib.EmpBd. In this program, the updated SAS data file NewData.New_Birthdays is a SAS 7 data file stored in the SAS 7 SAS library associated with the libref NewData. The RENAME= option in the DATA step is used in the UPDATE statement to rename the variables before the updated data file NewData.New_Birthdays is created. The two outputs that follow the program show the results of PROC PRINT on the original data file and the updated data file.

    proc sort data=v7.consulting_birthdays;
       by last_name;
    run; 
 
    data newdata.new_birthdays;
       update v7.consulting_birthdays
       (rename=(last_name=lastname 
                first_name=firstnme
                birthdate=birthday)) vlib.empbd;
       by lastname firstnme;
    run;

Data File to Be Updated, V7.Consulting_Birthdays,

               V7.Consulting_Birthdays Data File                1
 
           obs    last_name   first_name    birthdate
 
             1    JOHNSON      ED           30JAN65
             2    LEWIS        THOMAS       25MAY54
             3    SMITH        AMANDA       02DEC60
             4    WILSON       REBECCA      13APR58

Updated Data File, V7.New_Birthdays

                     V7.NEW_BIRTHDAYS Data File                  1
 
           obs    lastname      firstnme       birthday
 
             1    AMEER          DAVID         10OCT51
             2    BROOKS         RUBEN R.      25FEB52
             3    BROWN          VIRGINA P.    24MAY46
             4    CHAN           TAI           04JUL46
             5    GARRETT        OLAN M.       23JAN35
             6    GIBSON         GEORGE J.     23APR46
             7    GOODSON        ALAN F.       21JUN50
             8    JOHNSON        ED            30JAN65
             9    JUAREZ         ARMANDO       28MAY47
            10    LEWIS          THOMAS        25MAY54
            11    LITTLEJOHN     FANNIE        17MAY54
            12    RICHARDSON     TRAVIS Z.     30NOV37
            13    RODRIGUEZ      ROMUALDO R    09FEB29
            14    SCHOLL         MADISON A.    19MAR45
            15    SHROPSHIRE     LELAND G.     04SEP49
            16    SMITH          AMANDA        02DEC60
            17    SMITH          JERRY LEE     13SEP42
            18    VAN HOTTEN     GWENDOLYN     13SEP42
            19    WAGGONNER      MERRILEE D    27APR36
            20    WILLIAMSON     JANICE L.     19MAY52
            21    WILSON         REBBECA       13APR58

For more information about the UPDATE statement, see SAS Statements: Reference.

Note: You cannot update a SYSTEM 2000 database directly by using the DATA step, but you can update a SYSTEM 2000 database by using the following procedures: APPEND, FSEDIT, FSVIEW, QUEST, and SQL. For more information, see Browsing and Updating SYSTEM 2000 Data.