UNIQUESAVE= Data Set Option

Specifies to save observations with non-unique key values (the rejected observations) to a separate data set when appending or inserting observations to data sets with unique indexes.

Valid in:	PROC APPEND and PROC SQL
Used by:	SPDSUSDS automatic macro variable
Default:	NO
Interaction:	SYNCADD= Data Set Option
Engine:	SPD Engine only

Syntax

Required Arguments

Details

Example

Syntax

UNIQUESAVE=YES | NO

Required Arguments

YES

if SYNCADD=NO, writes rejected observations to a separate, system-created data set, which can be accessed by a reference to the macro variable SPDSUSDS.

NO

does not write rejected observations to a separate data set.

Details

Use UNIQUESAVE=YES when you are adding observations to a data set with unique indexes and the data set option SYNCADD=NO is set.

SYNCADD=NO specifies that an Append or Insert operation should process observations in blocks (pipelining), instead of one at a time. Duplicate index values are detected only after all the observations are applied to a data set. With UNIQUESAVE=YES, the rejected observations are saved to a separate data set whose name is stored in the SPD Engine macro variable SPDSUSDS. You can specify the macro variable in place of the data set name to identify the rejected observations.

Note: When SYNCADD=YES, the UNIQUESAVE= option is ignored. For more information see the SYNCADD= data set option.

Example: Using the UNIQUESAVE= Option with the APPEND Procedure

In the following example, a data set with two unique indexes is created and another data set with duplicate values is then appended to the first one. Because the UNIQUESAVE=YES option is specified, a data set containing the rejected observations is created. That data set includes a variable identifying the variable that had the duplicate value. The SAS log is shown.

UNIQUESAVE= Option

1    libname employee spde 'c:\temp';
NOTE: Libref EMPLOYEE was successfully assigned as follows:
      Engine:        SPDE
      Physical Name: c:\temp\
2
3    data employee.emp1 (index=(phone/unique room/unique));
4       input name $ phone room;
5       list;
6       datalines;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
7          Jill 4344 456
8          Jack 5589 789
9          Jim  8888 345
10         Sam  3334 657
NOTE: The data set EMPLOYEE.EMP1 has 4 observations and 3 variables.

11   run;
12
13   data employee.emp2;
14      input name $ phone room;
15      list;
16      datalines;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
17         Jack  8443 679
18         Ann   3334 987
19         Sam   8756 346
20         Susan 5321 456
NOTE: The data set EMPLOYEE.EMP2 has 4 observations and 3 variables.

21   run;
22
23   proc append base=employee.emp1(syncadd=no uniquesave=yes)
NOTE: Writing HTML Body file: sashtml.htm
24               data=employee.emp2;
25   run;

NOTE: Appending EMPLOYEE.EMP2 to EMPLOYEE.EMP1.
NOTE: There were 4 observations read from the data set EMPLOYEE.EMP2.
NOTE: 2 observations added.
NOTE: The data set EMPLOYEE.EMP1 has 6 observations and 3 variables.
WARNING: Duplicate values not allowed on index phone for file EMP1, 1 observations rejected.
WARNING: Duplicate values not allowed on index room for file EMP1, 1 observations rejected.
NOTE: Duplicate records have been stored in file EMPLOYEE._SPDEDUP048604700067A9F340C7E3E6.

26
27   proc print data=employee.emp1;
28      title 'Listing of Final Data Set';
29   run;

NOTE: There were 6 observations read from the data set EMPLOYEE.EMP1.

30
31   proc print data=&spdsusds
32      title 'Listing of Rejected observations';
33   run;

NOTE: There were 2 observations read from the data set
      EMPLOYEE._SPDEDUP048604700067A9F340C7E3E6.

UNIQUESAVE=YES

Rejected Observations