Options for Other Functions

BYSORT=

Perform an implicit automatic sort when SPD Server encounters a BY clause for a given table.
Syntax
BYSORT=YES | NO  
Arguments
YES
sorts the data based on the BY columns and returns the sorted data to the SAS client. This powerful capability means that the user does not have to sort data using a PROC SORT statement before using a BY clause.
NO
does not sort the data based on the BY columns. This might be desirable if a DATA step BY clause has a GROUPFORMAT option or if a PROC step reports grouped and formatted data.
Description
The default is YES. The NO argument means the table must have been previously sorted by the requested BY columns. The NO argument allows grouped data to maintain their precise order in the table. A YES argument groups the data correctly but possibly in a different order from the order in the table.
Example 1 - Group Formatting with BYSORT=
LIBNAME sport sasspds 'mylib'
   host='samson'
   user='user19'
   passwd='dummy2';

PROC FORMAT;
   value dollars
     0-99.99="low"
     100-199.99="medium"
     200-1000="high";
run;

data sport.racquets;
   input
     raqname $20.
     @22 weight
     @28 balance $2.
     @32 flex
     @36 gripsize
     @42 string $3.
     @47 price
     @55 instock;

   datalines;
Solo Junior          10.1   N   2  3.75  syn   50.00   6
Solo Lobber          11.3   N  10  5.5   syn  160.00   1
Solo Queensize       10.9  HH   6  5.0   syn  130.00   3
Solo Kingsize        13.1  HH   5  5.6   syn  140.00   3
;

PROC PRINT data=sport.racquets (bysort=yes);
   var raqname instock;
   by price;
   format price dollars.;
title 'Solo Brand Racquets by Price Level';
run;

Report Output with BYSORT=
                Solo Brand Racquets by Price Level

---------------------------- Price=low ---------------------------

OBS                           RAQNAME                      INSTOCK

 1                          Solo Junior                       6

-------------------------- Price=medium --------------------------

OBS                           RAQNAME                      INSTOCK

 3                         Solo Queensize                     3

 4                         Solo Kingsize                      3

 2                          Solo Lobber                       1
Example 2 - Group Formatting without BYSORT=
PROC PRINT data=sport.racquets (bysort=no);
   var raqname instock;
   by price;
   format price dollars.;
title 'Solo Brand Racquets by Price Level';
run;  
Report Output without BYSORT=
                Solo Brand Racquets by Price Level

---------------------------- Price=low ---------------------------

OBS                           RAQNAME                      INSTOCK

 1                          Solo Junior                       6

-------------------------- Price=medium --------------------------

OBS                           RAQNAME                      INSTOCK

 2                          Solo Lobber                       1

 3                         Solo Queensize                     3

 4                         Solo Kingsize                      3

ENDOBS=

Specifies the end row (observation) number in a user-defined range for the processing of a given table.
Syntax
ENDOBS=n
Arguments
n
is the number of the end row.
Description
By default, SPD Server processes the entire table unless the user specifies a range of rows with the STARTOBS= and ENDOBS= options. If the STARTOBS= option is used without the ENDOBS= option, the implied value of ENDOBS= is the end of the table. When both options are used together, the value of ENDOBS= must be greater than STARTOBS=.
In contrast to the Base SAS software options FIRSTOBS= and OBS=, the STARTOBS= and ENDOBS= SPD Server options can be used for WHERE clause processing in addition to table input operations.
Example
Print only rows 2-4 of the table INVENTORY.OLD_AUTOS.
LIBNAME inventory sasspds 'conversion_area'
   server=husky.5105
   user='siteusr1'
   prompt=yes;

data inventory.old_autos;
   input
     year $4.
     @6 manufacturer $12.
     model $10.
     body_style $5.
     engine_liters
     @39 transmission_type $1.
     @41 exterior_color $10.
     options $10.
     mileage conditon;

   datalines;
1971 Buick       Skylark   conv  5.8  A  yellow    00000001 143000 2
1982 Ford        Fiesta    hatch 1.2  M  silver    00000001  70000 3
1975 Lancia      Beta      2door 1.8  M  dk blue   00000010  80000 4
1966 Oldsmobile  Toronado  2door 7.0  A  black     11000010 110000 3
1969 Ford        Mustang   sptrf 7.1  M  red       00000111 125000 3
;

PROC PRINT data=inventory.old_autos (startobs=2 endobs=4);
run;

Data in the Printed Output
1982   Ford         Fiesta        hatch   1.2  M   silver    00000001   70000    3

1975   Lancia       Beta          2door   1.3  M   dk blue   00000010   80000    4

1966   Oldsmobile   Toronado      2door   7.0  A   black     11000010  110000    3

STARTOBS=

Specifies the start row (observation) number in a user-defined range for the processing of a given table.
Syntax
STARTOBS=n
Arguments
n
is the number of the start row.
Description
By default, SPD Server processes the entire table unless the user specifies a range of rows with the STARTOBS= and ENDOBS= options. If the ENDOBS= option is used without the STARTOBS= option, the implied value of STARTOBS= is 1. When both options are used together, the value of STARTOBS= must be less than ENDOBS=.
In contrast to the Base SAS software options FIRSTOBS= and OBS=, the STARTOBS= and ENDOBS= SPD Server options can be used for WHERE clause processing in addition to table input operations.
Example
Print only rows 2-4 of the table INVENTORY.OLD_AUTOS.
LIBNAME inventory sasspds 'conversion_area'
   server=husky.5105
   user='siteusr1'
   prompt=yes;

data inventory.old_autos;
   input
     year $4.
     @6 manufacturer $12.
     model $10.
     body_style $5.
     engine_liters
     @39 transmission_type $1.
     @41 exterior_color $10.
     options $10.
     mileage conditon;

   datalines;
1971 Buick       Skylark   conv  5.8  A  yellow    00000001 143000 2
1982 Ford        Fiesta    hatch 1.2  M  silver    00000001  70000 3
1975 Lancia      Beta      2door 1.8  M  dk blue   00000010  80000 4
1966 Oldsmobile  Toronado  2door 7.0  A  black     11000010 110000 3
1969 Ford        Mustang   sptrf 7.1  M  red       00000111 125000 3
;

proc print data=inventory.old_autos (startobs=2 endobs=4);
run;

UNIQUESAVE=

Specifies to save rows with nonunique key values (the rejected rows) to a separate table when appending data to tables with unique indexes.
Syntax
UNIQUESAVE=YES|NO|REP
Default: NO
Complements the Table Option
SYNCADD=
Used in Conjunction with Macro Variable
SPDSUSDS=
Corresponding Macro Variable:
SPDSUSAV=
Arguments
YES
writes rejected rows to a separate, system-created table file which can be accessed by a reference to the macro variable SPDSUSDS=.
NO
does not write rejected rows to a separate table, that is, ignores nonunique key values.
REP
when updating a master table from a transaction table, where the two tables share identical variable structures, the UNIQUESAVE=REP option replaces the row updated row in the master table instead of appending a row to the master table. The REP option only functions in the presence of a /UNIQUE index on the MASTER table. Otherwise, the REP setting is ignored..
Description
SYNCADD= is defaulted to NO. When NO, table appends are 'pipelined', meaning that the server data is sent in a stream a block at a time. (See table option NETPACKSIZE=.) Pipelining is faster than a synchronous append, but SAS reports the results of the Append operation differently for these two modes.
When applying only a single row (SYNCADD=NO), SAS returns a status code for each ADD operation. The application can determine the next action based on the status value. If a row is rejected due to containing a nonunique value for a unique index, the user receives a status message. In contrast, when data is pipelined (SYNCADD=YES), SAS returns a status code only after all the rows are applied to a table. As a consequence, the user does not know which rows have been rejected.
To enjoy the performance of data pipelining but still retain the rejected rows, use the UNIQUESAVE= option. When set to YES, SPD Server will save any rows that are rejected to a hidden SAS table.
When using this option, SAS returns the name of the hidden table containing the rejected rows in the macro variable SPDSUSDS. If you want to report the contents of the table, see SPDSUSDS=.
Note: If SYNCADD= YES is set, data pipelining is overridden and the data is processed synchronously. In this situation, the UNIQUESAVE= option is not relevant and, if set, is ignored.
Example 1
We want to append two tables, NAMES2 and NAMES3, which contain employees' names, to the NAMES1 table. Before performing our append, we create an index on the NAME column in NAMES1, declaring the index unique.
Specify for SPD Server, during the Append operation, to store rows found with duplicate employee names to a separate table file generated by the macro variable SPDSUSDS=.
Use a %PUT statement to display the table name for SPDSUSDS=. Then request a printout of the duplicate rows to review later.
data employee.names1;
input name $ exten;
datalines;
Jill 4344
Jack 5589
Jim  8888
Sam  3334
;
run;

data employee.names2;
input name $ exten;
datalines;
Jack  4443
Ann   8438
Sam   3334
Susan 5321
Donna 3332
;
run;

data employee.names3;
input name $ exten;
datalines;
Donna 3332
Jerry 3268
Mike  2213
;
run;

PROC DATASETS lib=employee nolist;
  modify names1;
  index create name/unique;
quit;

PROC APPEND data=employee.names2
            out=employee.names1(uniquesave=yes); run;

title 'The NAMES1 table with unique names
       from NAMES2';

PROC PRINT data=employee.names1;
run;

%put Set the macro variable spdsusds to &spdsusds;

title 'Duplicate (nonunique) name rows found in
       NAMES2';

PROC PRINT data=&spdsusds;
run;

PROC APPEND data=employee.names3
   out=employee.names1(uniquesave=yes);
run;
The SAS log provides the messages:
WARNING: Duplicate values not allowed on index NAME for
         file EMPLOYEE.NAMES1. (Occurred 2 times.)
NOTE: Duplicate records have been stored in file
      EMPLOYEE._30E3FD5.
And, an extract from our PROC PRINT shows:
The NAMES1 table with unique names from NAMES2

         OBS    NAME     EXTENs

          1     Jill      4344
          2     Jack      5589
          3     Jim       8888
          4     Sam       3334
          5     Ann       8438
          6     Susan     5321
          7     Donna     3332

Duplicate (nonunique) name rows found in NAMES2

        OBS    NAME     EXTEN    XXX00000

          1     Jack      4443      NAME
          2     Sam       3334      NAME
Example 2
Use the UNIQUESAVE=REP option to perform an update / append case using PROC APPEND instead of a DATA step:
* A MASTER table to update. ID */
/* will get a UNIQUE index      */

  DATA SPDS.MASTER;
    INPUT ID VALUE $;
    CARDS;
     1 one
     2 two
     3 three
  ;

  PROC DATASETS LIB=SPDS;
    MODIFY MASTER;
    INDEX CREATE ID/UNIQUE;
  QUIT;

  /* A transaction table TRANS to use to   */
  /* drive update/appends to MASTER        */

  DATA SPDS.TRANS;
    INPUT ID VALUE $;
     1 ONE
     3 THREE
     4 FOUR
     4 FOUR*
  ;

  /* Use of UNIQUESAVE=REP to update/append  */
  /* TRANS rows to MASTER based on whether   */
  /* TRANS records have an ID column that    */
  /* matches an existing row from the MASTER */
  /* table. Update MASTER rows with a match, */
  /* otherwise append TRANS row to MASTER    */

  PROC APPEND DATA=SPDS.TRANS
    OUT=SPDS.MASTER(UNIQUESAVE=REP);
  run;

Output of the resulting MASTER table would look like:
  Obs       ID      VALUE

    1         1      ONE
    2         2      two
    3         2      THREE
    4         4      FOUR*

VERBOSE=

Provides details of all indexes and ACL information associated with an SPD Server table.
Syntax
VERBOSE= YES | NO
Arguments
YES
requests detail information for the indexes, ACLs, and other SPD Server table values. This argument must be used with the CONTENTS procedure.
NO
suppresses detail information for the indexes, ACLs, and other SPD Server table values. This is the default.
Example
Request details of all the indexes for the table TEMP1 in the domain SPDS45.
PROC CONTENTS data=SPDS45 (verbose=yes);
run;
Output 14.4: Details of Table TEMP1 Indexes in Domain SPDS45
                          The CONTENTS Procedure

Data Set Name           SPDS45.TEMP1                            Observations            1000
Member Type             DATA                                    Variables               2
Engine                  SASSPDS                                 Indexes                 2
Created                 Tuesday, May 10, 2005 10:00:02 AM       Observation Length      16
Last Modified           Tuesday, May 10, 2005 11:01:36 AM       Deleted Observations    0
Protection                                                      Compressed              NO
Data Set Type                                                   Sorted                  NO
Label
Data Representation     Default
Encoding                Default


                        Engine / Host Dependent Information

                        Blocking Factor (obs/block)     2047
                        ACL Entry                       NO
                        ACL User Access(R,W,A,C)        (Y,Y,Y,Y)
                        ACL User Name                   ANONYMOU
                        ACL Owner Name                  ANONYMOU
                        Data Set is Ranged              NO
                        Alphabetic List of Index Info   .
                        Bitmap index (No Global Index ) i
                        Keyvalue (Min)                  1
                        Keyvalue (Max)                  100
                        # of Discrete values            100
                        Bitmap index (No Global Index ) j
                        Keyvalue (Min)                  1
                        Keyvalue (Max)                  10
                        # of Discrete values            10
                        Data Partsize                   16777216


                        Alphabetic List of Variables and Attributes

                        *       Variable        Type    Len

                        1       i               Num     8
                        2       j               Num     9


                        Alphabetic List of Indexes and Attributes

                                                # of
                                                Unique
                        *       Index           Values

                        1       i               100
                        2       j               10