Browsing and Updating CA-Datacom/DB Data

Appending CA-Datacom/DB Data with the APPEND Procedure

You can append data that is described by SAS/ACCESS view descriptors and PROC SQL views to SAS data files and vice versa. You can also append data that is described by view descriptors to each other.

The input file and base file do not have to match column for column. If they do not match, use the FORCE option in the APPEND procedure. This will include all columns in the base file. Values for columns that are not shared by the base and input files are set to missing.

In the following example, two personnel managers have kept separate employee records. One manager has kept records in the CA-Datacom/DB table EMPLOYEES, that is described by the view descriptor VLIB.DCMEMPS. The other manager has kept records in the SAS data file, MYDATA.SASEMPS. Due to a corporate reorganization, the two sources of data must be combined so that all employee data is stored in the CA-Datacom/DB table EMPLOYEES. The APPEND procedure can perform this task.

The data that is described by the view descriptor VLIB.DCMEMPS and the data in the SAS data file MYDATA.SASEMPS are printed with the following statements and displayed in Data Described by VLIB.DCMEMPS and Data in MYDATA.SASEMPS.

proc print data=vlib.dcmemps;
   title 'Data Described by VLIB.DCMEMPS';
run;

proc print data=mydata.sasemps;
   format birthdat date7.;
   title 'Data in MYDATA.SASEMPS Data File';
run;

Data Described by VLIB.DCMEMPS

                         Data Described by VLIB.DCMEMPS                        1
OBS    EMPID   BIRTHDAT   LASTNAME             FIRSTNAM          MIDDLENA

  1   119012   05JAN46    WOLF-PROVENZA        G.                ANDREA
  2   120591   12FEB46    HAMMERSTEIN          S.                RACHAEL
  3   123456        .     VARGAS               PAUL              JESUS
  4   127845   25DEC43    MEDER                VLADIMIR          JORAN
  5   129540   31JUL60    CHOULAI              CLARA             JANE
  6   135673   21MAR61    HEMESLY              STEPHANIE         J.
  7   212916   29MAY28    WACHBERGER           MARIE-LOUISE      TERESA
  8   216382   24JUL63    PURINTON             PRUDENCE          VALENTINE
  9   234967   21DEC67    SMITH                GILBERT           IRVINE
 10   237642   13MAR54    BATTERSBY            R.                STEPHEN
 11   239185   28AUG59    DOS REMEDIOS         LEONARD           WESLEY
 12   254896   06APR49    TAYLOR-HUNYADI       ITO               MISHIMA
 13   321783   03JUN35    GONZALES             GUILLERMO         RICARDO
 14   328140   02JUN51    MEDINA-SIDONIA       MARGARET          ROSE
 15   346917   15MAR50    SHIEKELESLAM         SHALA             Y.
 16   356134   25OCT60    DUNNETT              CHRISTINE         MARIE
 17   423286   31OCT64    MIFUNE               YUKIO             TOSHIRO
 18   456910   24SEP53    ARDIS                RICHARD           BINGHAM
 19   456921   12MAY62    KRAUSE               KARL-HEINZ        G.
 20   457232   15OCT63    LOVELL               WILLIAM           SINCLAIR
 21   459287   15JAN34    RODRIGUES            JUAN              M.
 22   677890   24APR65    NISHIMATSU-LYNCH     CAROL             ANNE

Data in MYDATA.SASEMPS

                        Data in MYDATA.SASEMPS Data File                       1
         OBS     EMPID    BIRTHDAT    LASTNAME     FIRSTNAM    MIDDLENA

          1     245962    30AUG64     BEDORTHA     KATHY        MARTHA
          2     765432    01MAR59     POWELL       FRANK        X.
          3     219223    13JUN47     HANSINGER    BENJAMIN     HAROLD
          4     326745    21FEB52     RAWN         BEATRICE     MAY

Submitting the following APPEND procedure combines data from these two sources:

proc append base=vlib.dcmemps data=mydata.sasemps;
run;

proc print data=vlib.dcmemps;
   title 'Appended Data';
run;

The following output displays the appended data that is described by the view descriptor VLIB.DCMEMPS. Notice that the data is inserted in the order of Native Key values.

Result of Appending Data

                                 Appended Data                                 1
OBS    EMPID   BIRTHDAT   LASTNAME             FIRSTNAM          MIDDLENA

  1   119012   05JAN46    WOLF-PROVENZA        G.                ANDREA
  2   120591   12FEB46    HAMMERSTEIN          S.                RACHAEL
  3   123456         .    VARGAS               PAUL              JESUS
  4   127845   25DEC43    MEDER                VLADIMIR          JORAN
  5   129540   31JUL60    CHOULAI              CLARA             JANE
  6   135673   21MAR61    HEMESLY              STEPHANIE         J.
  7   212916   29MAY28    WACHBERGER           MARIE-LOUISE      TERESA
  8   216382   24JUL63    PURINTON             PRUDENCE          VALENTINE
  9   219223   13JUN47    HANSINGER            BENJAMIN          HAROLD
 10   234967   21DEC67    SMITH                GILBERT           IRVINE
 11   237642   13MAR54    BATTERSBY            R.                STEPHEN
 12   239185   28AUG59    DOS REMEDIOS         LEONARD           WESLEY
 13   245962   30AUG64    BEDORTHA             KATHY             MARTHA
 14   254896   06APR49    TAYLOR-HUNYADI       ITO               MISHIMA
 15   321783   03JUN35    GONZALES             GUILLERMO         RICARDO
 16   326745   21FEB52    RAWN                 BEATRICE          MAY
 17   328140   02JUN51    MEDINA-SIDONIA       MARGARET          ROSE
 18   346917   15MAR50    SHIEKELESLAM         SHALA             Y.
 19   356134   25OCT60    DUNNETT              CHRISTINE         MARIE
 20   423286   31OCT64    MIFUNE               YUKIO             TOSHIRO
 21   456910   24SEP53    ARDIS                RICHARD           BINGHAM
 22   456921   12MAY62    KRAUSE               KARL-HEINZ        G.
 23   457232   15OCT63    LOVELL               WILLIAM           SINCLAIR
 24   459287   05JAN34    RODRIGUES            JUAN              M.
 25   677890   24APR65    NISHIMATSU-LYNCH     CAROL             ANNE
 26   765432   01MAR59    POWELL               FRANK             X.

The APPEND procedure also accepts a WHERE= data set option or a WHERE statement to retrieve a subset of data. In the following example, a subset of observations from MYDATA.SASEMPS is added to VLIB.DCEMPS. The results are displayed in Results of Appending Data with a WHERE= Data Set Option.

proc append base=vlib.dcmemps data=mydata.sasemps
   (where=(lastname like 'B%' or lastname like 'H%'));
run;

proc print data=vlib.dcmemps;
   title 'Appended Data';
run;

The following output displays the data when the observations appended to the BASE= data set are subset by the WHERE= data set option. In this case, the WHERE= data set option specifies that only the employees with last names beginning with B or H should be added to the BASE= data set.

Results of Appending Data with a WHERE= Data Set Option

                                 Appended Data                                 1
OBS   EMPID    BIRTHDAT   LASTNAME             FIRSTNAM          MIDDLENA

  1  119012    05JAN46    WOLF-PROVENZA        G.                ANDREA
  2  120591    12FEB46    HAMMERSTEIN          S.                RACHAEL
  3  123456          .    VARGAS               PAUL              JESUS
  4  127845    25DEC43    MEDER                VLADIMIR          JORAN
  5  129540    31JUL60    CHOULAI              CLARA             JANE
  6  135673    21MAR61    HEMESLY              STEPHANIE         J.
  7  212916    29MAY28    WACHBERGER           MARIE-LOUISE      TERESA
  8  216382    24JUL63    PURINTON             PRUDENCE          VALENTINE
  9  219223    13JUN46    HANSINGER            BENJAMIN          HAROLD
 10  234967    21DEC67    SMITH                GILBERT           IRVINE
 11  237642    13MAR54    BATTERSBY            R.                STEPHEN
 12  239185    28AUG59    DOS REMEDIOS         LEONARD           WESLEY
 13  245962    30AUG64    BEDORTHA             KATHY             MARTHA
 14  254896    06APR49    TAYLOR-HUNYADI       ITO               MISHIMA
 15  321783    03JUN35    GONZALES             GUILLERMO         RICARDO
 16  328140    02JUN51    MEDINA-SIDONIA       MARGARET          ROSE
 17  346917    15MAR50    SHIEKELESLAM         SHALA             Y.
 18  356134    25OCT60    DUNNETT              CHRISTINE         MARIE
 19  423286    31OCT64    MIFUNE               YUKIO             TOSHIRO
 20  456910    24SEP53    ARDIS                RICHARD           BINGHAM
 21  456921    12MAY62    KRAUSE               KARL-HEINZ        G.
 22  457232    15OCT63    LOVELL               WILLIAM           SINCLAIR
 23  459287    05JAN34    RODRIGUES            JUAN              M.
 24  677890    24APR65    NISHIMATSU-LYNCH     CAROL             ANNE

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

Note that when the FORCE option is used to append columns whose names do not match, any column names that are longer than 8 characters will be truncated at 8 characters.

Top of Page