SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference

Appending Data with the APPEND Procedure

With the APPEND procedure, you can append data described by SAS/ACCESS view descriptors and PROC SQL views to SAS data files. You can also update the data described by a view descriptor by appending to it the data from another SAS data set.

For an append operation to be successful, the variables in the BASE=, or target, data set and the variables in DATA=, or source, data set must match, or you must use the FORCE= option to concatenate the data sets. The FORCE= option causes the APPEND procedure to drop the extra variables and issues a warning.

You can append the data described by a view descriptor to a Version 6 or Version 7 data file and vice versa. For variables that use the longer Version 7 naming conventions or otherwise do not match, use the RENAME= data set option in the APPEND procedure to rename the variables.

Example of Appending Data to a SAS Data File

In the following example, two managers have kept separate employee telephone lists. The Marketing manager kept records in the SYSTEM 2000 EMPLOYEE database described by the view descriptor VLIB.EMPPHON. The Corporation manager kept records for the executive telephone list in a Version 6 SAS data file, MYDATA.CORPHON. The two sources must be combined to create a telephone list of both departments.

The data described by the view descriptor VLIB.EMPPHON and the data in the SAS data file MYDATA.CORPHON are displayed in Data Described by VLIB.EMPPHON and Data in MYDATA. CORPHON.

    proc print data=vlib.empphon;
       title 'Marketing Phone List';
    run; 
 
    proc print data=mydata.corphon;
       title 'Corporation Phone List';
    run;

Data Described by VLIB.EMPPHON

Marketing Phone List 1 OBS LASTNAME FIRSTNME PHONE 1 AMEER DAVID 545 XT495 2 BROOKS RUBEN R. 581 XT347 3 BROWN VIRGINA P. 218 XT258 4 CHAN TAI 292 XT331 5 GARRETT OLAN M. 212 XT208 6 GIBSON GEORGE J. 327 XT703 7 GOODSON ALAN F. 323 XT512 8 JUAREZ ARMANDO 506 XT987 9 LITTLEJOHN FANNIE 219 XT653 10 RICHARDSON TRAVIS Z. 243 XT325 11 RODRIGUEZ ROMUALDO R 243 XT874 12 SCHOLL MADISON A. 318 XT419 13 SHROPSHIRE LELAND G. 327 XT616 14 SMITH JERRY LEE 327 XT169 15 VAN HOTTEN GWENDOLYN 212 XT311 16 WAGGONNER MERRILEE D 244 XT914 17 WILLIAMSON JANICE L. 218 XT802

Data in MYDATA. CORPHON

Corporation Phone List 1 OBS LASTNAME FIRSTNME PHONE 1 BOWMAN HUGH E. 109 XT901 2 FAULKNER CARRIE ANN 132 XT417 3 GARRETT OLAN M. 212 XT208 4 KNAPP PATRICE R. 222 XT 12 5 KNIGHT ALTHEA 213 XT218 6 MILLSAP JOEL B. 131 XT224 7 MUELLER PATSY 223 XT822 8 NATHANIEL DARRYL 118 XT544 9 SALAZAR YOLANDA 111 XT169 10 WATERHOUSE CLIFTON P. 101 XT109

You can combine the data described by these two sources using the APPEND procedure as follows:

    proc append base=mydata.corphon data=vlib.empphon;
    run; 
 
    proc sort data=mydata.corphon;
    by lastname;
 
    proc print data=mydata.corphon;
       title 'Corporation and Marketing Phone List';
    run;

Appended Data displays the data in the updated data file MYDATA.CORPHON. Notice that the combined data is sorted by last name. Also, since PROC PRINT was used to display the data, the variable names are used (for example, FIRSTNME), not the variable labels, which are the item names (for example, FORENAME), as with the SQL procedure.

Appended Data

Corporation and Marketing Phone List 1 OBS LASTNAME FIRSTNME PHONE 1 AMEER DAVID 545 XT495 2 BOWMAN HUGH E. 109 XT901 3 BROOKS RUBEN R. 581 XT347 4 BROWN VIRGINA P. 218 XT258 5 CHAN TAI 292 XT331 6 FAULKNER CARRIE ANN 132 XT417 7 GARRETT OLAN M. 212 XT208 8 GARRETT OLAN M. 212 XT208 9 GIBSON GEORGE J. 327 XT703 10 GOODSON ALAN F. 323 XT512 11 JUAREZ ARMANDO 506 XT987 12 KNAPP PATRICE R. 222 XT 12 13 KNIGHT ALTHEA 213 XT218 14 LITTLEJOHN FANNIE 219 XT653 15 MILLSAP JOEL B. 131 XT224 16 MUELLER PATSY 223 XT822 17 NATHANIEL DARRYL 118 XT544 18 RICHARDSON TRAVIS Z. 243 XT325 19 RODRIGUEZ ROMUALDO R 243 XT874 20 SALAZAR YOLANDA 111 XT169 21 SCHOLL MADISON A. 318 XT419 22 SHROPSHIRE LELAND G. 327 XT616 23 SMITH JERRY LEE 327 XT169 24 VANHOTTEN GWENDOLYN 212 XT311 25 WAGGONNER MERRILEE D 244 XT914 26 WATERHOUSE CLIFTON P. 101 XT109 27 WILLIAMSON JANICE L. 218 XT802

The APPEND procedure also accepts a WHERE= data set option or a WHERE statement to subset which observations from the DATA= data set are added to the BASE= data set, as shown in the following example. (It is assumed that the MYDATA.CORPHON data file is in its original state before issuing the previous APPEND procedure.)

proc append base=mydata.corphon
  data=vlib.empphon(where=(lastname='AMEER'));
run; 
 
proc print data=mydata.corphon;
  title2 'Appended Data with a WHERE= Data Set Option';
run;

Appended Data with a WHERE= Data Set Option displays the data when the observations appended to the BASE= data set are subset by the WHERE= data set option.

Appended Data with a WHERE= Data Set Option

Appended Data With a WHERE= Data Set Option 1 OBS LASTNAME FIRSTNME PHONE 1 BOWMAN HUGH E. 109 XT901 2 FAULKNER CARRIE ANN 132 XT417 3 GARRETT OLAN M. 212 XT208 4 KNAPP PATRICE R. 222 XT 12 5 KNIGHT ALTHEA 213 XT218 6 MILLSAP JOEL B. 131 XT224 7 MUELLER PATSY 223 XT822 8 NATHANIEL DARRYL 118 XT544 9 SALAZAR YOLANDA 111 XT169 10 WATERHOUSE CLIFTON P. 101 XT109 11 AMEER DAVID 545 XT495

Appending Data to a Version 7 Data File

Had the Corporation manager in the previous example kept his records in a Version 7 SAS data file named V7.CORPHON (see Data in V7.CORPHON) and used variable names longer than 8 characters, the data in VLIB.EMPPHON could be appended with the following code:

  proc append base=v7.corphon
     (rename (firstname=firstnme))
     data=vlib.empphon;
  run;

  proc sort data=v7.corphon;
     by lastname;

  proc print data=v7.corphon;
    title2 'Corporation and Marketing Phone List';
  run;

Data in V7.CORPHON

Corporation Phone List Obs lastname firstname phone 1 BOWMAN HUGH E. 109 XT901 2 FAULKNER CARRIE ANN 132 XT417 3 GARRETT OLAN M. 212 XT208 4 KNAPP PATRICE M. 222 XT 12 5 KNIGHT ALTHEA 213 XT218 6 MILLSAP JOEL B. 131 XT224 7 MUELLER PATSY 223 XT822 8 NATHANIEL DARRYL 118 XT544 9 SALAZAR YOLANDA 111 XT169 10 WATERHOUSE CLIFTON P. 101 XT109

In this example, the RENAME= data set option is used to reconcile a character-length discrepancy between the firstname variable in the V7 data file and the firstnme variable in the view descriptor.Data in V7.CORPHON with Data from VLIB.EMPPHON Appended to It shows a portion of the data in the updated data file V7.CORPHON.

Data in V7.CORPHON with Data from VLIB.EMPPHON Appended to It

Corporation and Marketing Phone List Obs lastname firstnme phone 1 2 AMEER DAVID 545 XT495 3 BOWMAN HUGH E. 109 XT901 4 BOWMAN HUGH E. 109 XT901 5 BROOKS RUBEN R. 581 XT347 6 BROWN VIRGINA P. 218 XT258 7 CAHILL JACOB 435 XT426 8 CANADY FRANK A. 153 XT406 9 CHAN TAI 292 XT331 10 COLLINS LILLIAN 166 XT616 11 FAULKNER CARRIE ANN 132 XT417 12 FAULKNER CARRIE ANN 132 XT417 13 FERNANDEZ SOPHIA 414 XT847 14 FREEMAN LEOPOLD 436 XT604 15 GARCIA FRANCISCO 414 XT348 16 GARRETT OLAN M. 212 XT208 17 GARRETT OLAN M. 212 XT208 18 GIBSON GEORGE J. 327 XT703 19 GIBSON MOLLY I. 323 XT227 20 GOODSON ALAN F. 323 XT512 21 HERNANDEZ JESSE L. 444 XT448 22 JOHNSON BRADFORD 244 XT446 23 JONES MICHAEL Y. 434 XT713 24 JONES RITA M. 127 XT271 25 JUAREZ ARMANDO 506 XT987 26 KAATZ FREDDIE 243 XT387 27 KNAPP PATRICE M. 222 XT 12 28 KNAPP PATRICE R. 222 XT123 29 KNIGHT ALTHEA 213 XT218 30 KNIGHT ALTHEA 213 XT218 31 LITTLEJOHN FANNIE 219 XT653 32 MILLSAP JOEL B. 131 XT224 33 MILLSAP JOEL B. 131 XT224 34 MUELLER PATSY 223 XT822 35 MUELLER PATSY 223 XT822 36 NATHANIEL DARRYL 118 XT544 37 NATHANIEL DARRYL 118 XT544 38 POLANSKI IVAN L. 506 XT621

Appending SAS Data to a View Descriptor

When appending SAS data to a view descriptor, you will not be able to sort the data unless you specify an output data file. To sort the data in the view descriptor, you would have to sort the SYSTEM 2000 database, which is not recommended.

For more information on the APPEND procedure, see the SAS Procedures Guide.

Chapter Contents
Previous
Next
Top of Page