Option for Compatibility with Base SAS Software

SYNCADD=

Specifies when appending to a table whether to apply a single or multiple rows at a time.
Syntax
SYNCADD=YES|NO
Default: NO
Corresponding Macro Variable
SPDSSADD
Related Table Option
UNIQUESAVE=
Arguments
YES
imitates the behavior of the Base SAS engine, applying a single row at a time (synchronously).
NO
appends multiple rows at a time (asynchronously).
Description
When SYNCADD= is set to YES, processing performance becomes slower. Use this setting only in order to force the server's append processing to be compatible with Base SAS software processing. That is, when the server encounters a row with a nonunique value, to cancel the Append operation, back out the transactions just added, and leave the original table on disk.
Example
In this example, when executing the first INSERT statement, PROC SQL permits insertion of the values 'rollback1' and 'rollback2' because the row additions to table A are performed asynchronously. PROC SQL does not get the true completion status at the time it adds a row.
When executing the second INSERT statement, PROC SQL performs a rollback on the INSERT, upon encountering the Add error on 'nonunique', and deletes the rows 'rollback3' and 'rollback4'.
data a;
  input z $ 1-20 x y;
  list;

  datalines;
one                 1 10
two                 2 20
three               3 30
four                4 40
five                5 50
;

PROC SQL sortseq=ascii exec noerrorstop;
create unique index comp on a (x, y);
insert into a
  values('rollback1', -80, -80)
  values('rollback2',-90, -90)
  values('nonunique', 2, 20);

insert into a(syncadd=yes)
  set z='rollback3', x=-60, y=-60
  set z='rollback4', x=-70, y=-70
  set z='nonunique', x=2, y=20;
 quit;