Specifies to save rows
with nonunique key values (the rejected rows) to a separate table
when appending data to tables with unique indexes.
Complements
the Table Option
Used in
Conjunction with Macro Variable
Corresponding
Macro Variable:
Arguments
writes rejected rows
to a separate, system-created table file which can be accessed by
a reference to the macro variable SPDSUSDS=.
does not write rejected
rows to a separate table, that is, ignores nonunique key values.
when updating a master
table from a transaction table, where the two tables share identical
variable structures, the UNIQUESAVE=REP option replaces the row updated
row in the master table instead of appending a row to the master table.
The REP option only functions in the presence of a /UNIQUE index on
the MASTER table. Otherwise the REP setting is ignored..
SYNCADD= is defaulted
to NO. When NO, table appends are 'pipelined', meaning that the server
data is sent in a stream a block at a time. (See table option NETPACKSIZE=.)
While pipelining is faster than a synchronous append, SAS reports
the results of the append operation differently for these two modes.
When applying only a
single row (SYNCADD=NO), SAS returns a status code for each ADD operation.
The application can determine the next action based on the status
value. If a row is rejected due to containing a nonunique value for
a unique index, the user receives a status message. In contrast, when
data is pipelined (SYNCADD=YES), SAS returns a status code only after
all the rows are applied to a table. As a consequence,
the user does not know which rows have been rejected.
To enjoy the performance
of data pipelining but still retain the rejected rows, use the UNIQUESAVE=
option. When set to YES, SPD Server will save any rows that are rejected
to a hidden SAS table.
When using this option,
SAS returns the name of the hidden table containing the rejected rows
in the macro variable SPDSUSDS. If you want to report the contents
of the table, see
SPDSUSDS=.
Note: If SYNCADD= YES is set, data
pipelining is overridden and the data is processed synchronously.
In this situation, the UNIQUESAVE= option is not relevant and, if
set, is ignored.
We want to append two
tables, NAMES2 and NAMES3, which contain employees' names, to the
NAMES1 table. Before performing our append, we create an index on
the NAME column in NAMES1, declaring the index unique.
Specify for SPD Server,
during the append operation, to store rows found with duplicate employee
names to a separate table file generated by the macro variable SPDSUSDS=.
Use a %PUT statement
to display the table name for SPDSUSDS=. Then request a printout of
the duplicate rows to review later.
data employee.names1;
input name $ exten;
datalines;
Jill 4344
Jack 5589
Jim 8888
Sam 3334
;
run;
data employee.names2;
input name $ exten;
datalines;
Jack 4443
Ann 8438
Sam 3334
Susan 5321
Donna 3332
;
run;
data employee.names3;
input name $ exten;
datalines;
Donna 3332
Jerry 3268
Mike 2213
;
run;
PROC DATASETS lib=employee nolist;
modify names1;
index create name/unique;
quit;
PROC APPEND data=employee.names2
out=employee.names1(uniquesave=yes); run;
title 'The NAMES1 table with unique names
from NAMES2';
PROC PRINT data=employee.names1;
run;
%put Set the macro variable spdsusds to &spdsusds;
title 'Duplicate (nonunique) name rows found in
NAMES2';
PROC PRINT data=&spdsusds;
run;
PROC APPEND data=employee.names3
out=employee.names1(uniquesave=yes);
run;
The SAS log provides
the messages:
WARNING: Duplicate values not allowed on index NAME for
file EMPLOYEE.NAMES1. (Occurred 2 times.)
NOTE: Duplicate records have been stored in file
EMPLOYEE._30E3FD5.
And, an extract from
our PROC PRINT shows:
The NAMES1 table with unique names from NAMES2
OBS NAME EXTENs
1 Jill 4344
2 Jack 5589
3 Jim 8888
4 Sam 3334
5 Ann 8438
6 Susan 5321
7 Donna 3332
Duplicate (nonunique) name rows found in NAMES2
OBS NAME EXTEN XXX00000
1 Jack 4443 NAME
2 Sam 3334 NAME
Use the UNIQUESAVE=REP
option to perform an update / append case using PROC APPEND instead
of a DATA step:
* A MASTER table to update. ID */
/* will get a UNIQUE index */
DATA SPDS.MASTER;
INPUT ID VALUE $;
CARDS;
1 one
2 two
3 three
;
PROC DATASETS LIB=SPDS;
MODIFY MASTER;
INDEX CREATE ID/UNIQUE;
QUIT;
/* A transaction table TRANS to use to */
/* drive update/appends to MASTER */
DATA SPDS.TRANS;
INPUT ID VALUE $;
1 ONE
3 THREE
4 FOUR
4 FOUR*
;
/* Use of UNIQUESAVE=REP to update/append */
/* TRANS rows to MASTER based on whether */
/* TRANS records have an ID column that */
/* matches an existing row from the MASTER */
/* table. Update MASTER rows with a match, */
/* otherwise append TRANS row to MASTER */
PROC APPEND DATA=SPDS.TRANS
OUT=SPDS.MASTER(UNIQUESAVE=REP);
run;
Output of the resulting
MASTER table would look like:
Obs ID VALUE
1 1 ONE
2 2 two
3 2 THREE
4 4 FOUR*