Chapter Contents
Previous
Next
SAS/ACCESS Software for Relational Databases: 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 DBMS data described by one view descriptor to another, or you can append a SAS data file to a view descriptor (and therefore to the DBMS table).

The following example uses the APPEND procedure's FORCE option to append a SAS data file with extra variables to the view descriptor VLIB.SQLEMPS. You must be granted DBMS-specific INSERT privileges to add rows to the table SASDEMO.EMPLOYEES.

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 DBMS column is defined as NOT NULL, some restrictions apply when appending data.

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. 

/* create access descriptor    */
proc access dbms=oracle;
    create adlib.employ.access;
    user=scott;  orapw=tiger;
    path='myorapath';
    table=sasdemo.employees;
    assign=no;
    drop salary;
    list all;

/* create view descriptor    */
    create vlib.sqlemps.view;
    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   356134   14JUN1985    DUNNETT              CHRISTINE         MARIE
 16   423286   19DEC1988    MIFUNE               YUKIO             TOSHIRO
 17   456910   14JUN1978    ARDIS                RICHARD           BINGHAM
 18   456921   19AUG1987    KRAUSE               KARL-HEINZ        G.
 19   457232   15JUL1985    LOVELL               WILLIAM           SINCLAIR
 20   459287   02NOV1964    RODRIGUES            JUAN              M.
 21   677890   12DEC1988    NISHIMATSU-LYNCH     CAROL             ANNE
 22   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 <= '30APR1998'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   356134   14JUN1985    DUNNETT              CHRISTINE         MARIE
 16   423286   19DEC1988    MIFUNE               YUKIO             TOSHIRO
 17   456910   14JUN1978    ARDIS                RICHARD           BINGHAM
 18   456921   19AUG1987    KRAUSE               KARL-HEINZ        G.
 19   457232   15JUL1985    LOVELL               WILLIAM           SINCLAIR
 20   459287   02NOV1964    RODRIGUES            JUAN              M.
 21   677890   12DEC1988    NISHIMATSU-LYNCH     CAROL             ANNE
 22   346917   02MAR1987    SHIEKELESLAM         SHALA             Y.
 23   219776   15APR1998    PASTORELLI           ZORA
 24   245233   10APR1998    ALI                  SADIQ             H.
 25   245234   10APR1998    MEHAILESCU           NADIA             P.

When you use PROC APPEND with a view descriptor as the BASE= file, the DBMS issues DBMS-specific INSERT statements and places the new rows into the DBMS table wherever the free space exists. The Screen Control Language (SCL) APPEND function behaves in the same way, that is, the DBMS determines where the rows are inserted into the table. This approach is contrary to how SAS usually performs an append. When the BASE= file is a SAS data file, the data is appended to the end of the data file.

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   /* Example for Output  */
10508   /*==========================*/
10509  proc append base=vlib.sqlemps data=dlib.tempemps force;
10510     where hiredate <= '30APR98'd;
10511  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 DBMS-specific SQL INSERT statement for the rows to be appended to the DBMS table.

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


Chapter Contents
Previous
Next
Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.