Specifies when appending
to a table whether to apply a single or multiple rows at a time.
Corresponding
Macro Variable
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).
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.
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;