SYNCADD= Data Set Option

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

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

Syntax

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

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

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

When SYNCADD=NO, observations are added in blocks (pipelining), which is usually faster. If you are adding observations to a data set with a unique index and the SPD Engine encounters an observation with a duplicate index value, 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: Creating a Unique Composite Index Using the SQL Procedure

In the following example, two data sets, UQ01A and UQ01B, are created. On UQ01A, PROC SQL creates a unique composite index, and then inserts new values into the data set with SYNCADD=NO (inserting blocks of data). Duplicate values are stored in a separate file because UNIQUESAVE= is set to YES.

Then, PROC SQL creates a unique composite index on UQ01B and inserts new values with SYNCADD=YES. PROC SQL stops when duplicate values are encountered and restores the data set. Even though UNIQUESAVE=YES, it is ignored. The SAS log is shown:

1097  libname userfile spde 'c:\temp';
NOTE: Libref SPDS USERFILE was successfully assigned as follows:
      Engine:        SPD Engine
      Physical Name: d3727.na.sas.com:528c:\temp\
1098
1099  data uq01a uq01b;
1100    input z $ 1-20 x y;
1101    list;
1102    datalines;
RULE:----+----1----+----2----+----3----+----4----+----5----+----6----+----7
1103        one                  1 10
1104        two                  2 20
1105        three                3 30
1106        four                 4 40
1107        five                 5 50
NOTE: The data set USER.UQ01A has 5 observations and 3 variables.
NOTE: The data set USER.UQ01B has 5 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.51 seconds
      cpu time            0.06 seconds
1108  ;
1109
1110
1111  proc sql    sortseq=ascii exec noerrorstop;
1112  create unique index comp
1113    on uq01a  (x, y);
NOTE: Composite index comp has been defined.
1114  insert into uq01a(syncadd=no,uniquesave=yes)
1115    values('rollback1', -80, -80)
1116    values('rollback2',-90, -90)
1117    values('nonunique', 2, 20)
1118   ;
NOTE: 3 observations were inserted into USER.UQ01A.
WARNING: Duplicate values not allowed on index comp for file USER.UQ01A.
         (Occurred 1 times.)
NOTE: Duplicate records have been stored in file USER._D2DAAF7.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.99 seconds
      cpu time            0.05 seconds
1119  proc sql    sortseq=ascii exec noerrorstop;
1120  create unique index comp
1121    on uq01b  (x, y);
NOTE: Composite index comp has been defined.
1122  insert into uq01b(syncadd=yes,uniquesave=yes)
1123    set z='rollback3', x=-60, y=-60
1124    set z='rollback4', x=-70, y=-70
1125    set z='nonunique', x=2, y=20;
ERROR: Duplicate values not allowed on index comp for file UQ01B.
NOTE: Deleting the successful inserts before error noted above to restore
      data set to a consistent state.
1126
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.26 seconds
      cpu time            0.17 seconds
1127  proc compare data=uq01a compare=uq01b;run;
NOTE: There were 7 observations read from the data set USER.UQ01A.
NOTE: There were 5 observations read from the data set USER.UQ01B.
NOTE: PROCEDURE COMPARE used (Total process time):
      real time           0.51 seconds
      cpu time            0.05 seconds