Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

Data

Batch Data Input Utilities

There are five batch data input utilities that provide a quick and efficient way to add or update data in the database from external text files.

The normal way to run these interactively is from the Data - File Input menu. These can also be run as commands. The batch data input utilities are :

ADD REC that adds new records if they do not exist;
EVICT REC that deletes existing records;
REPLACE REC that replaces existing records;
UPDATE REC that replaces selected variables on existing records;
INPUT DATA that adds or replaces records.

The batch data input utilities use any COMPUTE,IF,RECODE,ACCEPT or REJECT RECORD IF clauses defined in the schema for a given record type. While you can use the utilities to input data directly into the CIR (record type zero), these clauses can only be specified at the normal record level even if they refer to common variables.

The FILE DUMP utility writes data from a database to a text file in a format that can be used by the data input utilities.

The FILE LIST utility writes a report showing the data from a database. Naturally this can be fairly voluminous.

You can also display and edit data through the SIR/XS SPREADSHEET utility that selects a set of data, displays it in a familiar spreadsheet style manner and allows you to update the data if necessary.

Batch Data Input Specifications

The utilities all have a very similar specification.
ADD REC, READ INPUT DATA and REPLACE REC have identical specifications.
EVICT REC has fewer options plus one particular keyword.
UPDATE REC has all the standard options plus four additional parameters.

They all have the same possible five files:

Input data

Contains the input data. The format of the data file is specified in the description of the record schema. A file in this format can be produced from an existing database by the SIR FILE DUMP utility or by the VisualPQL procedure WRITE RECORDS.
If variables on the record schema do not have input/output columns specified, these utilities automatically assign default columns at the end of any manually specified columns. If the input file is produced by SIR FILE DUMP, this allows database maintenance without having to assign columns manually. If you are processing a specific input file and want only to process variables with assigned input/output columns, the NOAUTO keyword suppresses this automatic assignment.
If the CSV keyword is specified, then the input file is in Comma Separated Variable (CSV) format. The input file is a text file with values for each record in a valid CSV format. The fields must be in the correct sequence that matches the sequence of fields on the database record. A file may either contain records for a single record type, in which case the record type is specified on the utility command or may contain multiple record types, in which case the first field on each input record is the record type.
A file in this format can be produced from an existing database by the SIR FILE DUMP utility with the CSV keyword or by the VisualPQL procedure CSV SAVE FILE.

Error Listing

A report of any errors.

Error Records

A copy of any data records with errors in the same format as the input file. (This could be reinput with an ACCEPT or other option.)

Summary

Update summary report.

Log

A fixed format that describes any errors. Each record contains the following:

COL    DESCRIPTION
-------------------------------
1-4    record number
5-6    message number
       1 record number error
       2 variable format error
       3 variable/compute error
       4 variable/recode error
       5 ACCEPT REC failed
       6 REJECT REC failed
       7 record accepted with errors
       8 record rejected
7-18   date of run
19-26  time of run
27-30  ordinal of record on this file
31-34  DBMS error number
35-42  variable name, if variable error
43-46  ACCEPT/REJECT REC num, line num
47-50  field starting column
51-54  field ending column

ADD REC

ADD REC adds new records to the database. The keys of incoming records are matched against those already in the database. If an input record matches an existing record, the incoming record is rejected with an error message.

ADD REC INPUT   =  filename
  [LISTFILE     =  filename]
  [ERRFILE      =  filename]
  [LOGFILE      =  filename]
  [SUMFILE      =  filename]
  [ACCEPT]
  [ALL]
  [BLANKUND]
  [CSV]
  [LOGALL]
  [NOAUTO]
  [NONEW]
  [NOSEQ]
  [ALIMIT       =  n]
  [BLIP         =  n]
  [LOADING      =  n]
  [RECTYPE      =  rectype]
  [RLIMIT       =  n]
  [SKIP         =  n]
  [STOP         =  n]

There are three groups of parameters. The first group specifies files, the next group specifies keywords and the last group specifies limits or other conditions. Optionally separate multiple parameters on a command with a slash "/".

FILES

INPUT: Specifies the input data file and must be specified.
LISTFILE: Specifies the file where error messages are written. If not specified, the current output file is used for error messages.
ERRFILE: Specifies the file where data records with errors are written. If not specified, no error file is produced, however errors messages are produced on the LISTFILE.
LOGFILE: Specifies the log file. A record is written to the log file in fixed format that describes each error.
SUMFILE: Specifies the file where the update summary report is written. If this is not specified the current output file is used. SUMFILE and LISTFILE can be the same file in which case the summary report is written after any error listing.

Keywords - Use these to specify the particular processing option(s) required:

ACCEPT: Specifies that records with erroneous values are accepted and variables with erroneous values are set to undefined. If not specified, records with erroneous values are rejected.
ALL: Specifies that input records with errors are written to the ERRFILE regardless of whether or not they are accepted into the database.
BLANKUND: Specifies that blank numeric fields on the input file result in UNDEFINED on the record. If this option is not specified, then blanks on input for a numeric field either result in a missing value, if a BLANK missing value is defined in the schema, or in a zero value.
CSV: Specifies that the input file is in CSV format.
LOGALL: Specifies that input records with errors are written with the log record to the LOGFILE.
NOAUTO: Specifies that only variables with specified input/output columns are processed.
NONEW: Specifies that no new cases are created.

Limits and Settings - Specify the keyword followed by an equals sign, "=" , followed by the value for these limits and settings.

ALIMIT = n: Sets a limit on the number of input records with errors. Terminates the procedure when "n" number of records have been processed with undefined values replacing errors.
BLIP = n: Specifies that an indication of progress is required and the approximate number of records expected on the input file. Progress is shown as a percentage of this number. Statistics of records added are displayed.
LOADING = .n: Specifies how data blocks are split as they become full. "N" is a number between 0.01 and 0.99. When data is reloaded or imported, blocks are filled. The normal value is 0.5, that means that a full data block is split in half. A value of .99 splits a data block with n records into one data block containing n-1 records and one data block containing 1 record. This is useful if records are added in keyfield order to keep the database file as compact as possible.
RECTYPE = n | name: Specifies that all the records in this run are of the given type. Rectype may be a record name or number. This is used when the data record does not contain a record type number or to override the number on the input record. Only one RECTYPE= keyword may be specified. If omitted, data records are identified by the record number in the columns specified in the schema (or by the first field on a CSV file).
RLIMIT = n: Specifies that the run stops if "n" number of records are rejected due to errors.
SKIP = n: Specifies that the first "n" lines on the data input file are skipped before starting to process the data. Processing begins at line "n" + 1.
STOP = n: Stops the run after processing "n" lines from the data input file. If the data is in multi-line records, the entire record is always processed.

Example:

ADD REC INPUT    = 'INPUT.DAT'
        ERRFILE  = 'ERR.DAT'
        LOGFILE  = 'LOG.LST'
        LISTFILE = 'OUT.LST'
        SUMFILE  = 'SUM.LST'
        ACCEPT
        RECTYPE  = 1

EVICT REC

EVICT REC INPUT =  filename
  [LISTFILE     =  filename]
  [ERRFILE      =  filename]
  [LOGFILE      =  filename]
  [SUMFILE      =  filename]
  [CSV]
  [EVICTCIR]
  [LOGALL]
  [NOAUTO]
  [BLIP         =  n]
  [RECTYPE      =  rectype]
  [RLIMIT       =  n]
  [SKIP         =  n]
  [STOP         =  n]

Deletes records. The keys of input records are matched against those already in the database. If an input record matches an existing record, the existing record is deleted. If an input record does not match an existing record, an error message is written.

There are three groups of parameters. The first group specifies files, the next group specifies keywords and the last group set limits or other conditions. Optionally separate multiple parameters on a command with a slash "/".

FILES

INPUT: Specifies the input data file. Must be specified.
LISTFILE: Specifies the file where error messages are written. If not specified, the current output file is used for error messages.
ERRFILE: Specifies the file where data records with errors are written. If not specified, no error file is produced, however errors messages are produced on the LISTFILE.
LOGFILE: Specifies the log file. A record is written to the log file in fixed format that describes each error.
SUMFILE: Specifies the file where the update summary report is written. If this is not specified the current output file is used. SUMFILE and LISTFILE can be the same file in which case the summary report is written after any error listing.

Keywords - Use these to specify the particular processing option(s) required.

CSV: Specifies that the input file is in CSV format.
EVICTCIR: Specifies that cases are deleted if all records in the case are deleted. This only applies to case structured databases.
LOGALL: Specifies that input records with errors are written with the log record to the LOGFILE.
NOAUTO: Specifies that only variables with specified input/output columns are processed.

Limits and Settings - Specify the keyword followed by an equals sign, "=" , followed by the value for these limits and settings.

BLIP: Specifies that an indication of progress is required and the approximate number of records expected on the input file. Progress is shown as a percentage of this number. Statistics of records added are displayed.
RECTYPE: Specifies that all the records in this run are of the given type. Rectype may be a record name or number. This is used when the data record does not contain a record type number or to override the number on the input record. Only one RECTYPE= can be specified. If omitted, data records are identified by the record number in the columns specified in the schema (or by the first field on a CSV file).
RLIMIT: Specifies that the run stops if "n" number of records are rejected due to errors.
SKIP: Specifies that the first "n" lines on the data input file are skipped before starting to process the data. Processing begins at line "n" + 1.
STOP: Stops the run after processing "n" lines from the data input file. If the data is in multi-line records, the entire record is always processed.

Example:

EVICT REC  INPUT     = 'INPUT.DAT'
           ERRFILE   = 'ERR.DAT'
           LOGFILE   = 'LOG.LST'
           LISTFILE  = 'OUT.LST'
           SUMFILE   = 'SUM.LST'
           RECTYPE   = 1

READ INPUT DATA

READ INPUT DATA INPUT  =  filename
    [LISTFILE          =  filename]
    [ERRFILE           =  filename]
    [LOGFILE           =  filename]
    [SUMFILE           =  filename]
    [ACCEPT]
    [ALL]
    [CSV]
    [LOGALL]
    [NOAUTO]
    [NONEW]
    [NOSEQ]
    [ALIMIT           =  n]
    [BLIP             =  n]
    [LOADING          =  n]
    [RECTYPE          =  rectype]
    [RLIMIT           =  n]
    [SKIP             =  n]
    [STOP             =  n]

Adds new records and replaces existing records. The keys of incoming records are matched against those already in the database. If an input record matches an existing record, the existing record is replaced; if the keys do not match, a new record is added.

There are three groups of parameters. The first group specifies files, the second group specifies keywords and the last group set limits or other conditions. Optionally separate multiple parameters on a command with a slash "/".

FILES

INPUT: Specifies the input data file. Must be specified.
LISTFILE: Specifies the file where error messages are written. If not specified, the current output file is used for error messages.
ERRFILE: Specifies the file where data records with errors are written. If not specified, no error file is produced, however error messages are produced on the LISTFILE.
LOGFILE: Specifies the log file. A record is written to the log file in fixed format that describes each error.
SUMFILE: Specifies the file where the update summary report is written. If this is not specified the current output file is used. SUMFILE and LISTFILE can be the same file in which case the summary report is written after any error listing.

Keywords - Use these to specify the particular processing option(s) required.

ACCEPT: Specifies that records with erroneous values are accepted and variables with erroneous values are set to undefined. If not specified, records with erroneous values are rejected.
ALL: Specifies that input records with errors are written to the ERRFILE regardless of whether or not they are accepted into the database.

Limits and Settings - Specify the keyword followed by an equals sign, "=" , followed by the value for these limits and settings.

ALIMIT: Sets a limit on the number of input records with errors. Terminates the procedure when "n" number of records have been processed with undefined values replacing errors.
BLIP: Specifies that an indication of progress is required and the approximate number of records expected on the input file. Progress is shown as a percentage of this number. Statistics of records added are displayed.
LOADING: Specifies when data blocks are split as they become full.
"N" is a number between 0.01 and 0.99. When data is reloaded or imported, blocks are filled. On subsequent update runs, the normal value is 0.5, that means that a full data block is split in half. A value of .99 splits a data block with n records into one data block containing n-1 records and one data block containing 1 record. This is useful if records are added in keyfield order to keep the database file as compact as possible.
RECTYPE: Specifies that all the records in this run are of the given type. Rectype may be a record name or number. This is used when the data record does not contain a record type number or to override the number on the input record. Only one RECTYPE= keyword may be specified. If omitted, data records are identified by the record number in the columns specified in the schema (or by the first field on a CSV file).
RLIMIT: Specifies that the run stops if "n" number of records are rejected due to errors.
SKIP: Specifies that the first "n" lines on the data input file are skipped before starting to process the data. Processing begins at line "n" + 1.
STOP: Stops the run after processing "n" lines from the data input file. If the data is in multi-line records, the entire record is always processed.

Example:

READ INPUT DATA INPUT = 'INPUT.DAT'
      ERRFILE = 'ERR.DAT'
      LOGFILE = 'LOG.LST'
      LISTFILE = 'OUT.LST'
      SUMFILE  = 'SUM.LST'
      ACCEPT
      RECTYPE = 1

REPLACE REC

REPLACE REC INPUT   =  filename
    [LISTFILE       =  filename]
    [ERRFILE        =  filename]
    [LOGFILE        =  filename]
    [SUMFILE        =  filename]
    [ACCEPT]
    [ALL]
    [CSV]
    [LOGALL]
    [NOAUTO]
    [NONEW]
    [NOSEQ]
    [ALIMIT         =  n]
    [BLIP           =  n]
    [LOADING        =  n]
    [RECTYPE        =  rectype]
    [RLIMIT         =  n]
    [SKIP           =  n]
    [STOP           =  n]

Replaces existing records. The keys of input records are matched against those already in the database. If an input record does not match an existing record, it is rejected with an error message. If a match is found, the existing record is replaced by the input record.

There are three groups of parameters. The first group specifies files, the next group specifies keywords and the last group sets limits or other conditions. Optionally separate multiple parameters on a command with a slash "/".

FILES

INPUT: Specifies the input data file. Must be specified.
LISTFILE: Specifies the file where error messages are written. If not specified, the current output file is used for error messages.
ERRFILE: Specifies the file where data records with errors are written. If not specified, no error file is produced, however errors messages are produced on the LISTFILE.
LOGFILE: Specifies the log file. A record is written to the log file in fixed format that describes each error.
SUMFILE: Specifies the file where the update summary report is written. If this is not specified the current output file is used. SUMFILE and LISTFILE can be the same file in which case the summary report is written after any error listing.

Keywords - Use these to specify the particular processing option(s) required.

ACCEPT: Specifies that records with erroneous values are accepted and variables with erroneous values are set to undefined. If not specified, records with erroneous values are rejected.
ALL: Specifies that input records with errors are written to the ERRFILE regardless of whether or not they are accepted into the database.

Limits and Settings - Specify the keyword followed by an equals sign, "=" , followed by the value for these limits and settings.

ALIMIT: Sets a limit on the number of input records with errors. Terminates the procedure when "n" number of records have been processed with undefined values replacing errors.
BLIP: Specifies that an indication of progress is required and the approximate number of records expected on the input file. Progress is shown as a percentage of this number. Statistics of records added are displayed.
LOADING: Specifies how data blocks are split as they become full. "N" is a number between 0.01 and 0.99. When data is reloaded or imported, blocks are filled. The normal value is 0.5, that means that a full data block is split in half. A value of .99 splits a data block with n records into one data block containing n-1 records and one data block containing 1 record. This is useful if records are added in keyfield order to keep the data as compact as possible.
RECTYPE: Specifies that all the records in this run are of the given type. Rectype may be a record name or number. This is used when the data record does not contain a record type number or to override the number on the input record. Only one RECTYPE= keyword may be specified. If omitted, data records are identified by the record number in the columns specified in the schema (or by the first field on a CSV file).
RLIMIT: Specifies that the run stops if "n" number of records are rejected due to errors.
SKIP: Specifies that the first "n" lines on the data input file are skipped before starting to process the data. Processing begins at line "n" + 1.
STOP: Stops the run after processing "n" lines from the data input file. If the data is in multi-line records, the entire record is always processed.

Example:

REPLACE REC INPUT    = 'INPUT.DAT'
            ERRFILE  = 'ERR.DAT'
            LOGFILE  = 'LOG.LST'
            LISTFILE = 'OUT.LST'
            SUMFILE  = 'SUM.LST'
            ACCEPT
            RECTYPE = 1

UPDATE REC

UPDATE REC INPUT    =  filename
   [LISTFILE        =  filename]
   [ERRFILE         =  filename]
   [LOGFILE         =  filename]
   [SUMFILE         =  filename]
   [ACCEPT]
   [ADD]
   [ALL]
   [COMPUTE]
   [CSV]
   [LOGALL]
   [NOAUTO]
   [NOBOOL]
   [NONEW]
   [NOSEQ]
   [ALIMIT    =  n]
   [BLIP      =  n]
   [LOADING   =  n]
   [MISSCHAR  =  a]
   [RECTYPE   =  rectype]
   [RLIMIT    =  n]
   [SKIP      =  n]
   [STOP      =  n]

Replaces individual variables in existing records. The keys of input records are matched against those already in the database. If a match is found, the variables in the existing record are replaced by non-blank fields in the input. If a match is not found, the input record is rejected with an error message, or, if the ADD keyword is specified, a new record is created.

There are four additional parameters for UPDATE RECORD:

ADD: Specify to add new records. By default, input records must match existing records in the database.
COMPUTE: Specify to re-execute schema COMPUTE statements. By default, COMPUTE statements from the Schema are not re-executed.
NOBOOL: Specify to stop the re-execution of consistency checks from the Schema. By default, consistency checks (ACCEPT REC IF and REJECT REC IF) are performed. Any temporary variables referenced in the consistency check must be respecified on the input record to assure that the intent of the check is satisfied.
MISSCHAR: Specify a single character to indicate that an existing variable is set to undefined. In order to set an existing value to UNDEFINED, include this character on the input record in the leftmost column of the variable. A blank does not indicate a missing value and may not be used as the character. There is no default.

There are three groups of parameters. The first group specifies files, the next group comprises keywords and the last group sets limits or other conditions. Optionally separate multiple parameters with a slash "/".

FILES

INPUT: Specifies the input data file. Must be specified.
LISTFILE: Specifies the file where error messages are written. If not specified, the current output file is used for error messages.
ERRFILE: Specifies the file where data records with errors are written. If not specified, no error file is produced, however errors messages are produced on the LISTFILE.
LOGFILE: Specifies the log file. A record is written to the log file in fixed format that describes each error.
SUMFILE: Specifies the file where the update summary report is written. If this is not specified the current output file is used. SUMFILE and LISTFILE can be the same file in which case the summary report is written after any error listing.

Keywords - Use these to specify the particular processing option(s) required.

ACCEPT: Specifies that records with erroneous values are accepted and variables with erroneous values are set to undefined. If not specified, records with erroneous values are rejected.
ADD: Specifies that input records that do not match existing records are added to the database. Schema defined consistency checks and compute specifications are applied to the added records.
ALL: Specifies that input records with errors are written to the ERRFILE regardless of whether or not they are accepted into the database.
COMPUTE: Specifies that any COMPUTE specifications in the schema are re-executed.
CSV: Specifies that the input file is in CSV format.
LOGALL: Specifies that input records with errors are written with the log record to the LOGFILE.
NOAUTO: Specifies that only variables with specified input/output columns are processed.
NOBOOL: Specifies that any ACCEPT REC IF or REJECT REC IF specifications in the schema are bypassed.
NONEW: Specifies that no new cases are created.

Limits and Settings - Specify the keyword followed by an equals sign, "=" , followed by the value for these limits and settings.

ALIMIT: Sets a limit on the number of input records with errors. Terminates the procedure when "n" number of records have been processed with undefined values replacing errors.
BLIP: Specifies that an indication of progress is required and the approximate number of records expected on the input file. Progress is shown as a percentage of this number. Statistics of records added are displayed.
LOADING: Specifies how data blocks are split as they become full. "N" is a number between 0.01 and 0.99. When data is reloaded or imported, blocks are filled. The normal value is 0.5, that means that a full data block is split in half. A value of .99 splits a data block with n records into one data block containing n-1 records and one data block containing 1 record. This is useful if records are added in keyfield order to keep the database file as compact as possible.
MISSCHAR: Specifies a character to indicate that the field is set to UNDEFINED. When this character is in the leftmost position of a variable on input, the variable on the database is set to undefined. Specify a single character, do not enclose it in quotes.
RECTYPE: Specifies that all the records in this run are of the given type. Rectype may be a record name or number. This is used when the data record does not contain a record type number or to override the number on the input record. Only one RECTYPE= keyword may be specified. If omitted, data records are identified by the record number in the columns specified in the schema (or by the first field on a CSV file).
RLIMIT: Specifies that the run stops if "n" number of records are rejected due to errors.
SKIP: Specifies that the first "n" lines on the data input file are skipped before starting to process the data. Processing begins at line "n" + 1.
STOP: Stops the run after processing "n" lines from the data input file. If the data is in multi-line records, the entire record is always processed.

Example:

UPDATE REC  INPUT     = 'INPUT.DAT'
            ERRFILE   = 'ERR.DAT'
            LOGFILE   = 'LOG.LST'
            LISTFILE  = 'OUT.LST'
            SUMFILE   = 'SUM.LST'
            ACCEPT
            RECTYPE  = 1
            MISSCHAR  = *

SIR FILE DUMP

SIR FILE DUMP  [FILENAME  = fileid]
    RECTYPES  = {ALL | rectype (log_expr),...}
    [BOOLEAN   = (log_expr)]
    [CIR]
    [COUNT     = total [,increment[,start]]]
    [CSV]
    [DPOINT]
    [LIST      = case id list]
    [NOAUTO]
    [SAMPLE    = fraction [,seed]]
    [UNDEFINED = char]

Creates a text file in a form suitable for processing by the batch data input utilities. DBA read security clearance is needed to use this utility.

Optionally separate multiple parameters on the command with slashes.

FILENAME

Specifies the name of the output file. If this clause is not specified, the output is written to the default output file (normally the scrolled output buffer in interactive sessions).

RECTYPES

Specifies the record types to dump. This clause is required. The keyword ALL specifies all record types are dumped. A logical expression can be specified to restrict the data records selected. The expression can reference common variables or variables from the listed record type and can include PQL functions.

BOOLEAN

Defines a logical condition applied to common variables. This clause only applies to case structured databases. If the logical condition is true, records for that case are dumped. BOOLEAN is applied after any SAMPLE, COUNT or LIST.

CIR

Specify CIR to output common variables as a separate record (record type 0).

COUNT

Outputs data from a specified number of cases from the database. This clause only applies to case structured databases and cannot be used with SAMPLE or LIST. Total specifies the number of cases to retrieve. Increment specifies the "skipping factor" for retrieving cases. Start specifies the first case to select. For example, a start value of 3 begins the processing at the third case.

SIR FILE DUMP FILENAME =  'OUTPUT.DAT'
              RECTYPES =  ALL COUNT = 10

CSV

Specifies that the file is written in CSV format.

DPOINT

Specifies that, when writing a fixed format file, any numeric fields that have a decimal component, have the decimal point included. This is automatically done when in CSV format.

LIST

Retrieves the specified cases for case structured databases. Enclose case identifiers that are character strings in quotes. LIST cannot be used with SAMPLE or COUNT. For example:

SIR FILE DUMP FILENAME =  'OUTPUT.DAT'
              RECTYPES =  ALL  LIST = 1,3,5 thru 10

NOAUTO

Specifies that only variables with specified input/output columns are processed.

SAMPLE

Outputs data from a random sample of cases from the database. This clause only applies to case structured databases. Fraction specifies the sample size for selection. Seed specifies the starting seed for the random number generator. If seed is not specified, the default is used.

UNDEFINED

Specifies the character used to fill fields on output that are undefined on the database. Blanks are the default. For example:

SIR FILE DUMP FILENAME  =  'OUTPUT.DAT'
    RECTYPES = 1 (SALARY GT 2000)
    UNDEFINED = *

SIR FILE LIST

SIR FILE LIST FILENAME=filename
    [BOOLEAN=   (log-expr)]
    [LIST=      caseid list]
    [RECTYPES=  rectype [(log-expr)] ...| ALL]
    [ORDER=     ALPHA | VARNUM]
    [SAMPLE=    fraction [,seed]]
    [COUNT=     total [,incr[,start]]]
    [CIR=       varlist | NOCIR]
    [VARIABLES = rectype (var-list)]

Writes all or part of the data to a file for subsequent printing. Use the filename CONSOL to write to the screen or STDOUT to write to the default output file.

DBA read security is required to use this utility.

BOOLEAN

Specifies cases to include in the list. If the test fails, no records for that case are listed. The test may only use common variables. If LIST, COUNT or SAMPLE is used, the BOOLEAN clause is applied after that selection process. For example:

BOOLEAN = (ID GT 5)

LIST

Specifies cases to select. Separate entries with blanks or commas. Use the "TO" format to specify a range. Enclose case ids that are strings in quotes. LIST cannot be used with SAMPLE or COUNT. For example:

LIST= 1,3,5,7 to 10

RECTYPES

Specifies the record types to select. RECTYPES= ALL specifies all record types. If RECTYPES is omitted from the command, only the common variables are listed. A logical expression may be defined to select particular data records within a record type. If the test is TRUE, the record is listed. The expression may use common or record variables from the record type. For example:

RECTYPES= 1 (GENDER=2)

ORDER

Specifies the sequence of the listing of variables. This can be alphabetic order (ALPHA) of the variable name or the order the variables are defined in the record (VARNUM). VARNUM is the default. For example:

SIR FILE LIST  FILENAME='DATA.LIS'  ORDER= ALPHA

SAMPLE

Retrieves a random sample of cases from case-structured databases. Fraction specifies the sample size for selection. Seed specifies the starting seed for the random number generator. If seed is not specified, one is assigned by default. SAMPLE cannot be used with COUNT or LIST. For example:

SIR FILE LIST FILENAME='DATA.LIS'  SAMPLE= .5

COUNT

Retrieves a subset of cases from the database. Total specifies the number of cases to retrieve. Incr is the increment to apply to locate the next case to process. The default is 1 and processes every case. An increment of 2 processes every other case, 3 every third case, etc. Start specifies the ordinal of the first case to process. For example, a start value of 3 and an Incr of 3 starts the processing with the third case, skips cases 4 and 5 and processes 6. COUNT cannot be used with SAMPLE or LIST. For example:

SIR FILE LIST FILENAME='DATA.LIS'  COUNT= 50,3,3

CIR

Specifies the common variables to list. If the CIR clause is omitted, all common variables are listed. For example:

SIR FILE LIST FILENAME='DATA.LIS'  CIR= ID

NOCIR

Suppresses output of CIR variables. For example:

SIR FILE LIST FILENAME='DATA.LIS'   NOCIR

VARIABLES

Specifies the variables to list for a record type. All variables are listed for selected record types as the default. For example:

SIR FILE LIST FILENAME='DATA.LIS' RECTYPE = 3
    VARIABLES = 3 (position,revdate)

Sample Output:

SIR/XS FILE LIST                         Jan 05, 2006      10:53:36    Page  1

***CASE  ID                       1

REVIEW                                              POSITION                 4
REVDATE   04 05 03
POSITION                 4

REVIEW                                              POSITION                 4
REVDATE   06 05 03
POSITION                 4

REVIEW                                              POSITION                 5
REVDATE   12 09 04
POSITION                 5

REVIEW                                              POSITION                 5
REVDATE   02 04 05
POSITION                 5

***CASE  ID                       2

REVIEW                                              POSITION                 6
REVDATE   03 16 03
POSITION                 6

REVIEW                                              POSITION                 6
REVDATE   04 27 03
POSITION                 6

SIR SPREADSHEET

SIR SPREADSHEET
    {RECTYPE = recname [BOOLEAN = (log-expr)] | TABLE=tabfile.table}
    [INDEXED BY indexname]
    [VARIABLES = (var1,var2,... | ALL)]
    [LABELS|UPDATE]

Selects data from a single database record type or from a tabfile table and displays it in a graphical form similar to a spreadsheet display. The user can insert, delete or update if allowed, and can print or export the data in a CSV format for input to other packages.

RECTYPE =: Specifies the record name or number to display.
BOOLEAN = (logical_expression): Specifies the condition to select records. The specified test can use common variables and record variables.
TABLE =

For example:

SIR SPREADSHEET RECTYPE=EMPLOYEE BOOLEAN=(GENDER EQ 2) UPDATE

The record data is displayed as a spreadsheet that looks something like: