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 from one
view descriptor to the data from another.
In the following example, two branch managers have kept separate records on customers'
checking accounts. One manager has kept records in the CUSTOMER and CHCKACCT segments
of the IMS
database AcctDBD, described by the view descriptor Vlib.ChckAcct. The other manager has kept
records in a Version 7
SAS data set, MyData.Checks. Due to a corporate reorganization, the two sources of data must
be combined so that all customer data is stored in the IMS database AcctDBD. A branch
manager can use the APPEND procedure to perform this task, as the following example
demonstrates.
options linesize=120;
proc print data=vlib.chckacct;
title2 'Data Described by VLIB.CHCKACCT';
run;
proc print data=mydata.checks;
title2 'Data in MyData.Checks Data Set';
run;
The data that is described by the Vlib.ChckAcct view descriptor and the data in the
SAS data set MyData.Checks are displayed in the following two outputs.
Data That Is Described by the Vlib.ChckAcct View Descriptor
The SAS System
Data Described by VLIB.CHCKACCT
SOC_SEC_ CHECK_ACCOUNT_ CHECK_ CHECK_
OBS NUMBER CUSTOMER_NAME NUMBER DATE BALANCE
1 667-73-8275 WALLS, HOOPER J. 345620145345 15MAR95 1266.34
2 667-73-8275 WALLS, HOOPER J. 345620154633 28MAR95 1298.04
3 434-62-1234 SUMMERS, MARY T. 345620104732 27MAR95 825.45
4 436-42-6394 BOOKER, APRIL M. 345620135872 26MAR95 234.89
5 434-62-1224 SMITH, JAMES MARTIN 345620134564 16MAR95 2645.34
6 434-62-1224 SMITH, JAMES MARTIN 345620134663 24MAR95 143.78
7 178-42-6534 PATTILLO, RODRIGUES 745920057114 10JUN95 1502.78
8 156-45-5672 O'CONNOR, JOSEPH 345620123456 27MAR95 463.23
9 657-34-3245 BARNHARDT, PAMELA S. 345620131455 29MAR95 1243.25
10 667-82-8275 COHEN, ABRAHAM 382957492811 03APR95 7302.06
11 456-45-3462 LITTLE, NANCY M. 345620134522 25MAR95 831.65
Data in the MyData.Checks Data Set
The SAS System
Data in MyData.Checks Data Set
check_
soc_sec_ account_ check_ check_
OBS customer_name number number balance date
1 COWPER, KEITH 241-98-4542 183352795865 862.31 25MAR95
2 OLSZEWSKI, STUART 309-22-4573 382654397566 486.00 02APR95
3 NAPOLITANO, BARBARA 250-36-8831 284522378774 104.20 10APR95
4 MCCALL, ROBERT 367-34-1543 644721295973 571.92 05APR95
Note: To use PROC APPEND, you must
use a view descriptor that describes the entire path of data from
the root segment down to the level where you want to append data.
If a parent segment already exists with a key value equal to that
specified in the input data set, the IMS engine inserts the remaining
path of data under the parent segment.
You can combine the
data from these two sources using the APPEND procedure, as shown in
the following example:
proc append base=vlib.chckacct data=mydata.checks;
run;
proc print data=vlib.chckacct;
title2 'Appended Data';
run;
proc sql;
delete from vlib.account
where soc_sec_number in( '241–98–4542'
'250–36–8831'
'309–22–4573'
'367–34–1543' )
run;
The database
type determines where the segments are inserted. In this case, the database type is not
an indexed database type, so the data in MyData.Checks is intermixed with the data
that is described by Vlib.ChckAcct. The following output displays the updated data
that is described by the view descriptor, Vlib.ChckAcct.
Results of Appending Data with the APPEND Procedure
The SAS System
Appended Data
SOC_SEC_ CHECK_ACCOUNT_ CHECK_ CHECK_
OBS NUMBER CUSTOMER_NAME NUMBER DATE BALANCE
1 667-73-8275 WALLS, HOOPER J. 345620145345 15MAR95 1266.34
2 667-73-8275 WALLS, HOOPER J. 345620154633 28MAR95 1298.04
3 434-62-1234 SUMMERS, MARY T. 345620104732 27MAR95 825.45
4 250-36-8831 NAPOLITANO, BARBARA 284522378774 10APR95 104.20
5 241-98-4542 COWPER, KEITH 183352795865 25MAR95 862.31
6 436-42-6394 BOOKER, APRIL M. 345620135872 26MAR95 234.89
7 434-62-1224 SMITH, JAMES MARTIN 345620134564 16MAR95 2645.34
8 434-62-1224 SMITH, JAMES MARTIN 345620134663 24MAR95 143.78
9 178-42-6534 PATTILLO, RODRIGUES 745920057114 10JUN95 1502.78
10 367-34-1543 MCCALL, ROBERT 644721295973 05APR95 571.92
11 156-45-5672 O'CONNOR, JOSEPH 345620123456 27MAR95 463.23
12 657-34-3245 BARNHARDT, PAMELA S. 345620131455 29MAR95 1243.25
13 667-82-8275 COHEN, ABRAHAM 382957492811 03APR95 7302.06
14 456-45-3462 LITTLE, NANCY M. 345620134522 25MAR95 831.65
15 309-22-4573 OLSZEWSKI, STUART 382654397566 02APR95 486.00
Note: The APPEND procedure issues
a warning message when a variable in the view descriptor does not
have a corresponding variable in the input data set.
The PROC SQL code deletes the appended data so that the next PROC APPEND example works
without
reinitializing the database.
You can use the APPEND procedure's FORCE option to force PROC APPEND to concatenate
two data sets that have different variables or
variable attributes.
The APPEND procedure also accepts a SAS WHERE statement to retrieve a subset of the
data. In the following example, a subset of the observations from the DATA=
data set is added to the BASE= data set.
proc append base=vlib.chckacct data=mydata.checks
(where=(check_date >='26MAR95'd));
run;
proc print data=vlib.chckacct;
title2 'Appended Data with a WHERE Data Set Option';
run;
Note that the WHERE= data set option applies only to the MyData.Checks data set. The
following output displays
the results.
Results of Appending Data with a WHERE= Data Set Option
The SAS System
Appended Data with a WHERE= Data Set Option
SOC_SEC_ CHECK_ACCOUNT_ CHECK_ CHECK_
OBS NUMBER CUSTOMER_NAME NUMBER DATE BALANCE
1 667-73-8275 WALLS, HOOPER J. 345620145345 15MAR95 1266.34
2 667-73-8275 WALLS, HOOPER J. 345620154633 28MAR95 1298.04
3 434-62-1234 SUMMERS, MARY T. 345620104732 27MAR95 825.45
4 250-36-8831 NAPOLITANO, BARBARA 284522378774 10APR95 104.20
5 436-42-6394 BOOKER, APRIL M. 345620135872 26MAR95 234.89
6 434-62-1224 SMITH, JAMES MARTIN 345620134564 16MAR95 2645.34
7 434-62-1224 SMITH, JAMES MARTIN 345620134663 24MAR95 143.78
8 178-42-6534 PATTILLO, RODRIGUES 745920057114 10JUN95 1502.78
9 367-34-1543 MCCALL, ROBERT 644721295973 05APR95 571.92
10 156-45-5672 O'CONNOR, JOSEPH 345620123456 27MAR95 463.23
11 657-34-3245 BARNHARDT, PAMELA S. 345620131455 29MAR95 1243.25
12 667-82-8275 COHEN, ABRAHAM 382957492811 03APR95 7302.06
13 456-45-3462 LITTLE, NANCY M. 345620134522 25MAR95 831.65
14 309-22-4573 OLSZEWSKI, STUART 382654397566 02APR95 486.00
Note that the IMS
engine has no way to determine how large a database is. Therefore, if you use the APPEND
procedure to add a database to itself, a loop
can result. For more information about the APPEND procedure in the
Base SAS Procedures Guide.