SYNCADD= Table Option

Specifies to process one row at a time or multiple rows at a time when adding rows.

Valid in:	SPD Server
Default:	NO
Interaction:	Corresponding macro variable is SPDSSADD.
Tip:	Use the UNIQUESAVE table option (or SPDSUSAV macro variable) to save rejected rows when SYNCADD=NO.

Syntax

Required Arguments

Details

Example

Syntax

SYNCADD=YES | NO

Required Arguments

YES

processes a single row at a time (synchronously).

NO

processes multiple rows at a time (asynchronously).

Details

When SYNCADD= is set to YES, rows are processed one at a time. With PROC SQL, if you are inserting rows into a table with a unique index, and SPD Server encounters a row with a non-unique value, the following occurs:

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

SYNCADD=NO is faster. However, SYNCADD=NO also handles unique indexes differently. If a non-unique value is found when inserting rows into a table that has a unique index, the following occurs:

SPD Server rejects the row
SPD Server continues processing
a status code is issued only at the end of the insert operation.

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

Example

In the following example, two identical tables, WITH_NO and WITH_YES, are created. Both have a unique index. PROC SQL is used to insert three new rows, one of which has duplicate values. The SYNCADD=YES option is used. PROC SQL stops when the duplicate values are encountered and restores the table. PROC SQL is used again to insert these three new rows (as before). In this case, the SYNCADD=NO option is used. The row with duplicate values is rejected. The SAS log is shown:

301  libname tempdata sasspds "conversion_area" server=husky.5105
302      user="siteusr1" password=XXXXXXXXX;
NOTE: User siteusr1(ACL Group CORE) connected to SPD(LAX) 5.3 server at 10.24.7.79.
NOTE: Libref TEMPDATA was successfully assigned as follows:
      Engine:        SASSPDS
      Physical Name: :3030/bigdisk/lax/pubs_d4spds53/test_domains/husky/conversion_area/
303
304  data tempdata.with_no(index=(x /unique))
305       tempdata.with_yes(index=(x /unique));
306     input z $ 1-20 x y;
307  list;
308  datalines;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
309          one                 1 10
310          two                 2 20
311          three               3 30
312          four                4 40
313          five                5 50
NOTE: The data set TEMPDATA.WITH_NO has 5 observations and 3 variables.
NOTE: The data set TEMPDATA.WITH_YES has 5 observations and 3 variables.
314  ;
315  run;
316
317
318  proc sql;
319    insert into tempdata.with_yes(syncadd=yes)
320       values('six_yes', 6, 60)
321       values('seven_yes', 2, 70)
322       values('eight_yes', 8, 80)
323
324  ;
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.
325  quit;
326
327  proc sql;
328    insert into tempdata.with_no(syncadd=no)
329       values('six_no', 6, 60)
330       values('seven_no', 2, 70)
331       values('eight_no', 8, 80)
332  ;
NOTE: 3 rows were inserted into TEMPDATA.WITH_NO.

WARNING: Duplicate values not allowed on index x for file TEMPDATA.WITH_NO. (Occurred 1 times.)
333  quit;
334
335  proc compare data=tempdata.with_no compare=tempdata.with_yes;
336  run;

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

SYNCADD= Table Option

Syntax

Required Arguments

YES

NO

Details

Example

See Also