SYNCADD= Data Set Option

Specifies to process one observation at a time or multiple observations at a time when adding observations.

Valid in:	PROC SQL
Default:	NO
Interaction:	UNIQUESAVE=
Engine:	SPD Engine only

Syntax

Required Arguments

Details

Example

Syntax

SYNCADD=YES | NO

Required Arguments

YES

processes a single observation at a time (synchronously).

NO

processes multiple observations at a time (asynchronously).

Details

With SYNCADD=YES, observations are processed one at a time. With PROC SQL, if you are inserting observations into a data set with a unique index, and the SPD Engine encounters an observation with a non-unique value, the following occurs:

the add operation stops
all transactions just added are backed out
the original data set on disk is unchanged

Adding observations with SYNCADD=NO is obviously much faster. However, when inserting a few observations into a data set with a unique index using PROC SQL, the SPD Engine can back out all the observations if one duplicate value is found. Specifically, the following occurs:

the SPD Engine rejects the observation
the SPD Engine continues processing
a status code is issued only at the end of the Append or Insert operation

To save the rejected observations in a separate data set, set the UNIQUESAVE= data set option to YES.

Example: Inserting Observations with Duplicate Values into a Data Set with a Unique Index

In the following example, two identical data sets, WITH_NO and WITH_YES, are created. Both have a unique index.

PROC SQL is used to insert three new observations, one of which has duplicate values. The SYNCADD=YES option is used. PROC SQL stops when the duplicate values are encountered and restores the data set.

PROC SQL is used again to insert these three new observations (as before). In this case, the SYNCADD=NO option is used. The observation with duplicate values is rejected. The SAS log is shown:

Inserting Observations

1    libname addlib spde 'c:\temp';
NOTE: Libref ADDLIB was successfully assigned as follows:
      Engine:        SPDE
      Physical Name: c:\temp\
2
3    data addlib.with_no(index=(x /unique))
4         addlib.with_yes(index=(x /unique)) ;
5      input z $ 1-20 x y;
6      list;
7      datalines;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
8          one                  1 10
9          two                  2 20
10         three                3 30
11         four                 4 40
12         five                 5 50
NOTE: The data set ADDLIB.WITH_NO has 5 observations and 3 variables.
NOTE: The data set ADDLIB.WITH_YES has 5 observations and 3 variables.

13   run;
14
15   proc sql;
16      insert into addlib.with_yes(syncadd=yes)
17       values('six_yes',   6, 60 )
18       values('seven_yes', 2, 70 )
19       values('eight_yes', 8, 80 )
20   ;
ERROR: Duplicate values not allowed on index x for file WITH_YES.
NOTE: This insert failed while attempting to add data from VALUES clause 2 to the data set.
NOTE: Deleting the successful inserts before error noted above to restore table to a consistent
      state.
21   quit;
NOTE: The SAS System stopped processing this step because of errors.
22


23   proc sql;
24      insert into addlib.with_no(syncadd=no)
25       values('six_no',   6, 60 )
26       values('seven_no', 2, 70 )
27       values('eight_no', 8, 80 )
28   ;
NOTE: 3 rows were inserted into ADDLIB.WITH_NO.

WARNING: Duplicate values not allowed on index x for file WITH_NO, 1 observations rejected.
29   quit;

30
31   proc compare data=addlib.with_no compare=addlib.with_yes;
32   run;

NOTE: There were 7 observations read from the data set ADDLIB.WITH_NO.
NOTE: There were 5 observations read from the data set ADDLIB.WITH_YES.