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 when 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;