Option for Compatibility with Base SAS Software

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;