UNIQUESAVE= Table Option
Specifies to save rows that contain duplicate values (which would be rejected) when appending or inserting data in tables with unique indexes.
| Valid in: | SPD Server |
| Default: | NO |
| Interactions: | Corresponding macro variable is SPDSUSAV. |
| Use in conjunction with the SPDSUSDS reserved macro variable. | |
| Note: | UNIQUESAVE= has no effect when the SYNCADD= table option (or SPDSSADD macro variable) is set to YES. |
Syntax
Required Arguments
YES
writes rejected rows to a separate, system-created table. This table can be accessed by a reference to the macro variable SPDSUSDS.
NO
ignores duplicate rows rejected by an append or insert operation.
REP
replaces the current row in the master table with the duplicate row from the insert or append operation, instead of saving the rows to a separate table. This setting is useful when updating a master table from a transaction table, where the two tables share identical column structures.
Details
Examples
Example 1: Using UNIQUESAVE=YES
libname employee sasspds "conversion_area" server=husky.5105
user="siteusr1" prompt=yes;
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;
proc print data=&spdsusds;
run;
proc append data=employee.names3
out=employee.names1(uniquesave=yes);
run;
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.
Example 2: Using UNIQUESAVE=REP
libname tempdata sasspds "conversion_area" server=husky.5105
user="siteusr1" prompt=yes;
/* Create a Master table to update. */
/* ID will get a UNIQUE index */
data tempdata.master;
input ID value $;
cards;
1 one
2 two
3 three
;
proc datasets lib=tempdata;
modify master;
index create ID/unique;
quit;
/* Create transaction table Trans to use to */
/* drive update/appends to Master */
data tempdata.trans;
input ID value $;
cards;
1 ONE
3 THREE
4 FOUR
4 FOUR*
;
/* Use 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=tempdata.trans
out=tempdata.master(uniquesave=rep);
run;
proc print data=tempdata.master; run;NOTE: Appending TEMPDATA.TRANS to TEMPDATA.MASTER. NOTE: There were 4 observations read from the data set TEMPDATA.TRANS. NOTE: 1 observations added. NOTE: The data set TEMPDATA.MASTER has 4 observations and 2 variables.
