SAS/ACCESS Software for PC File Formats: Reference

Appending Data with the APPEND Procedure

You can append data from any data set to a SAS data file or view descriptor. Specifically, you can append PC files data described by one view descriptor to another, or you can append a SAS data file. Because the SAS/ACCESS interface to DIF, WKn, and XLS files is read-only, you cannot append data to those files. You can however, append data from them to a DBF file or to a SAS data file.

The following example uses the APPEND procedure's FORCE option to append a SAS data file with extra variables to the data file referenced by the view descriptor VLIB.SQLEMPS. You must have DBF INSERT privileges in order to add rows to the EMPLOYEES.DBF file.

You can append data to a table that is referenced by a view descriptor even if the view descriptor contains a subset of columns and a subset of rows. If a PC files column is defined as NOT NULL, some restrictions apply when appending data. For more information, see the APPEND procedure in SAS Procedures Guide.

The FORCE option forces PROC APPEND to concatenate two data sets even though they may have some different variables or variable attributes. The SAS data file, DLIB.TEMPEMPS, has DEPT, FAMILYID, and GENDER variables that have not been selected in the view descriptor, VLIB.SQLEMPS. The extra variables are dropped from DLIB.TEMPEMPS when it and the BASE= data set, VLIB.SQLEMPS, are concatenated. A message is displayed in the SAS log indicating that the variables are dropped.

proc access dbms=dbf;             
  /* create access descriptor    */
  create adlib.employ.access;
  path='c:\sasdemo\employee.dbf';
  assign=no;
  drop salary;
  list all;

  create vlib.sqlemps.view;       
  /* create view descriptor    */
  select empid hiredate lastname
         firstname middlename;
  format empid 6.0
         hiredate date9.;
run;

proc print data=vlib.sqlemps;       
  /* examples                  */
  title 'Data Described by VLIB.SQLEMPS';
run;

proc print data=dlib.tempemps;
  title 'Data in DLIB.TEMPEMPS Data File';
run;

The view descriptor VLIB.SQLEMPS is displayed in Data Described by VLIB.SQLEMPS, and the SAS data file DLIB.TEMPEMPS is displayed in Data in DLIB.TEMPEMPS.

Data Described by VLIB.SQLEMPS

Data Described by VLIB.SQLEMPS OBS EMPID HIREDATE LASTNAME FIRSTNAM MIDDLENA 1 119012 01JUL1968 WOLF-PROVENZA G. ANDREA 2 120591 05DEC1980 HAMMERSTEIN S. RACHAEL 3 123456 04APR1989 VARGAS CHRIS J. 4 127845 16JAN1967 MEDER VLADIMIR JORAN 5 129540 01AUG1982 CHOULAI CLARA JANE 6 135673 15JUL1984 HEMESLY STEPHANIE J. 7 212916 15FEB1951 WACHBERGER MARIE-LOUISE TERESA 8 216382 15JUN1985 PURINTON PRUDENCE VALENTINE 9 234967 19DEC1988 SMITH GILBERT IRVINE 10 237642 01NOV1976 BATTERSBY R. STEPHEN 11 239185 07MAY1981 DOS REMEDIOS LEONARD WESLEY 12 254896 04APR1985 TAYLOR-HUNYADI ITO MISHIMA 13 321783 10SEP1967 GONZALES GUILLERMO RICARDO 14 328140 10JAN1975 MEDINA-SIDONIA MARGARET ROSE 15 346917 02MAR1987 SHIEKELESLAM SHALA Y. 16 356134 14JUN1985 DUNNETT CHRISTINE MARIE 17 423286 19DEC1988 MIFUNE YUKIO TOSHIRO 18 456910 14JUN1978 ARDIS RICHARD BINGHAM 19 456921 19AUG1987 KRAUSE KARL-HEINZ G. 20 457232 15JUL1985 LOVELL WILLIAM SINCLAIR 21 459287 02NOV1964 RODRIGUES JUAN M. 22 677890 12DEC1988 NISHIMATSU-LYNCH CAROL ANNE 23 346917 02MAR1987 SHIEKELESLAM SHALA Y.

Data in DLIB.TEMPEMPS

Data in DLIB.TEMPEMPS Data File OBS EMPID HIREDATE DEPT GENDER LASTNAME FIRSTNAM MIDDLENA FAMILYID 1 765111 04MAY1998 CSR011 M NISHIMATSU-LYNCH RICHARD ITO 677890 2 765112 04MAY1998 CSR010 M SMITH ROBERT MICHAEL 234967 3 219776 15APR1998 ACC024 F PASTORELLI ZORA . 4 245233 10APR1998 ACC013 ALI SADIQ H. . 5 245234 10APR1998 ACC024 F MEHAILESCU NADIA P. . 6 326721 01MAY1998 SHP002 M CALHOUN WILLIS BEAUREGARD .

The APPEND procedure also accepts a WHERE= data set option or a SAS WHERE statement to retrieve a subset of the observations. In this example, a subset of the observations from DLIB.TEMPEMPS is added to VLIB.SQLEMPS by using a SAS WHERE statement; the WHERE statement applies only to the DATA= data set.

proc append base=vlib.sqlemps 
            data=dlib.tempemps force;
   where hiredate >= '01JAN1998'd;
run;

proc print data=vlib.sqlemps;
  title 'Subset of SAS Data Appended 
         to a View Descriptor';
run;

Subset of Data Appended with the FORCE Option shows VLIB.SQLEMPS with three rows from DLIB.TEMPEMPS appended to it.

Subset of Data Appended with the FORCE Option

Subset of SAS Data Appended to a View Descriptor OBS EMPID HIREDATE LASTNAME FIRSTNAM MIDDLENA 1 119012 01JUL1968 WOLF-PROVENZA G. ANDREA 2 120591 05DEC1980 HAMMERSTEIN S. RACHAEL 3 123456 04APR1989 VARGAS CHRIS J. 4 127845 16JAN1967 MEDER VLADIMIR JORAN 5 129540 01AUG1982 CHOULAI CLARA JANE 6 135673 15JUL1984 HEMESLY STEPHANIE J. 7 212916 15FEB1951 WACHBERGER MARIE-LOUISE TERESA 8 216382 15JUN1985 PURINTON PRUDENCE VALENTINE 9 234967 19DEC1988 SMITH GILBERT IRVINE 10 237642 01NOV1976 BATTERSBY R. STEPHEN 11 239185 07MAY1981 DOS REMEDIOS LEONARD WESLEY 12 254896 04APR1985 TAYLOR-HUNYADI ITO MISHIMA 13 321783 10SEP1967 GONZALES GUILLERMO RICARDO 14 328140 10JAN1975 MEDINA-SIDONIA MARGARET ROSE 15 346917 02MAR1987 SHIEKELESLAM SHALA Y. 16 356134 14JUN1985 DUNNETT CHRISTINE MARIE 17 423286 19DEC1988 MIFUNE YUKIO TOSHIRO 18 456910 14JUN1978 ARDIS RICHARD BINGHAM 19 456921 19AUG1987 KRAUSE KARL-HEINZ G. 20 457232 15JUL1985 LOVELL WILLIAM SINCLAIR 21 459287 02NOV1964 RODRIGUES JUAN M. 22 677890 12DEC1988 NISHIMATSU-LYNCH CAROL ANNE 23 346917 02MAR1987 SHIEKELESLAM SHALA Y. 24 765111 04MAY1994 NISHIMATSU-LYNCH RICHARD ITO 25 765112 04MAY1998 SMITH ROBERT MICHAEL 26 219776 15APR1998 PASTORELLI ZORA 27 245233 10APR1998 ALI SADIQ H. 28 245234 10APR1998 MEHAILESCU NADIA P. 29 326721 01MAY1998 CALHOUN WILLIS BEAUREGARD

See SAS Log with Messages about the FORCE Option for a copy of the SAS log screen and the messages about the FORCE option.

SAS Log with Messages about the FORCE Option

10504 10505 10506 10507 10508 10509 proc append base=vlib.sqlemps 10510 data=dlib.tempemps force; 10511 where hiredate <= '30APR1998'd; 10512 run; NOTE: Appending DLIB.TEMPEMPS to VLIB.SQLEMPS. WARNING: Variable DEPT was not found on BASE file. WARNING: Variable GENDER was not found on BASE file. WARNING: Variable FAMILYID was not found on BASE file. NOTE: FORCE is specified, so dropping/ truncating will occur. NOTE: 3 observations added. NOTE: The data set VLIB.SQLEMPS has . observations and 5 variables.

Because the BASE= data set is a view descriptor in this example, PROC APPEND generates a SQL INSERT statement for the rows to be appended to the DBF file.

The number of observations in the EMPLOYEES.DBF file is not displayed in the SAS log because when the view descriptor is opened by the DBF engine, the number of rows in the underlying file is not known.

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

Chapter Contents
Previous
Next
Top of Page