HomeStartingEnvironmentDBMSVisualPQLProceduresSQLFormsHost/APIIndex
PQL Procedures homecontents start chapter top of pagebottom of pagenext page index Overview

Overview

The VisualPQL Procedures create output in various formats such as reports, cross-tabulations or interfaces to other software such as statistical packages. Reports and other output intended for printing are written as text files. The interface files are in formats that are directly useable by other software packages.

A VisualPQL main routine can be followed by one or more procedures. The main routine specifies the data the procedures use by putting it into a procedure table using the PERFORM PROCS command. This copies local variables from the main routine into the procedure table. The procedures either operate on the procedure record immediately it is put into the procedure table or, if the table has to be sorted, the procedures operate after all the data has been put in the table and sorted.

The procedure specifications describe how the data in the procedure table is treated. Other than Full Report, the procedures are specified as a single VisualPQL command with numerous options. There are some common options that are the same for all procedures.

The general structure of a program that uses procedures is:

RETRIEVAL | PROGRAM
.
.   VisualPQL code that creates variables for the procedure
.   PERFORM PROCS
.
PQL_PROCEDURE command and options.
PQL_PROCEDURE command and options.
END RETRIEVAL | PROGRAM
Following is a listing and brief description of the VisualPQL Procedures available.

BMDP SAVE FILE Generates a file that can be directly accessed by the BMDP® statistical package.

CONDESCRIPTIVE Produces descriptive statistics for specified variables. The statistics available include sum, mean, minimum, maximum, standard deviation, skewness, kurtosis, variance, standard error of the mean, coefficient of variability and confidence interval.

CSV SAVE FILE Generates a file that can be accessed by any package that reads Comma Separated Variable format.

DBASE SAVE FILE Generates a file that can be directly accessed by DBASE ®.

DESCRIPTIVE Produces descriptive statistics and a frequency bar chart for a specified variable. The statistics include sum, mean, minimum, maximum, standard deviation, skewness, kurtosis, variance, standard error of the mean, coefficient of variability and confidence interval. This essentially replaces both CONDESCRIPTIVE and FREQUENCIES. The frequency chart is produced by the SIRGRAPH module. Output can be in HTML format suitable for viewing through a browser.

DIF SAVE FILE Generates a file in the Data Interchange Format accepted by many PC packages.

FREQUENCIES Produces descriptive statistics of variables with counts and percentage distributions for values of the variables. Seventeen statistics are available.

GRAPH Produces file suitable for input to the SIRGRAPH module (only available on Windows). The file is a text file that can be transferred to a Windows based computer if necessary. A wide variety of 2D and 3D graphs can be produced.

MINITAB SAVE FILE Generates a file that can be directly accessed by the MINITAB® statistical package.

PLOT Produces a file for graphical display of line or scatter plots. Linear regression statistics are also produced. The graphic output is produced by the SIRGRAPH module.

REPORT - Quick Produces columnar reports with a minimum of specification using keywords to specify formats, sorting, break-points, totals and subtotals.

REPORT - Full Extends the VisualPQL programming language to handle complex reports with changing report formats, conditional branching, nesting and computations.

SAS SAVE FILE Generates files that can be directly accessed by the SAS® statistical package. The schema information and data are output in SAS text export format.

SAVE TABLE Creates tables on SIR/XS tabfiles.

SIR SAVE FILE Creates a sequential copy of a SIR/XS database.

SPREAD SHEET Displays data in a spreadsheet style format.

SPSS SAVE FILE Generates a file that can be accessed by the SPSS® statistical package.

SYSTAT SAVE FILE Generates a file that can be accessed by the SYSTAT® statistical package.

TABULATE Produces cross tabulations with options for nesting categories and concatenating tables. Cells can contain various statistics in addition to counts, percentages and quantiles. Output can be in HTML format suitable for viewing through a browser.

WRITE RECORDS Generates a fixed format text data file that may be in any sequence.

XML SAVE FILE Generates a file in XML (eXtensible Markup Language), that can be accessed by many other packages and applications.

Syntax

Each of the procedures is specified with a command that has keywords to specify options. Some keywords are common to all the procedure commands. Keywords and associated options are continuations of the command. Continuation lines can be used to continue a command across multiple lines.

Except for the Full Report procedure, the actions of the procedure are completely specified by the keywords and options on the procedure command. Full Report is a programming language and subsequent VisualPQL commands specify the processing performed.

As many procedures as necessary may be specified in a single program. Options specified on one procedure have no effect on subsequent procedures.

The end product of any procedure is a file. Specify a filename on the command. Frequencies, Condescriptive and Tabulate can append the output of one procedure to the end of the previous procedure's output. If there are multiple procedures of the same type, specify the output file on the first such procedure. If subsequent procedures of the same type have no output file specification, the output is appended to the end of the previously specified file.

All procedures can produce sorted output.

Common keywords

The following clauses are standard to all VisualPQL procedures. Where there are any exceptions, these are noted:

FILENAME  = filename | CONSOL | STDOUT
VARIABLES = variable list
SORT      = [ (n) ] variable [(A)|(D)], ...
BOOLEAN   = (logical_expression )
SAMPLE    = fraction
FILENAME Specify the filename to be created by the procedure. Enclose filenames that are not in the same format as standard SIR/XS names in quotes. Specify either STDOUT or CONSOL (Not in quotes) to display the result of a procedure in normal place that output is being written to. In the case of a normal interactive session this is the scrolled output buffer. FILENAME is a required clause except where noted.

VARIABLES Specifies the procedure variables that are used by the procedure. For procedures that allow multiple variables, the order in which variables are specified is the order they appear in the output file. If VARIABLES is not specified, the default variables are output. (See INCLUDE and EXCLUDE.)

A variable list contains variable names or three keywords:
ALL Specifies that the default variables are used (taking regard to INCLUDE/EXCLUDE)
AS Specifies an alternate name for the variable. This can also be achieved simply by following the variable name with a new name in quotes e.g.

  VARIABLES = S(1) AS SALARY or
  VARIABLES = S(1) 'Salary‘
TO Specifies a list of selected variables A to B. The variables included in the list depend on the sequence in which variables are defined and include all variables defined between the two specified variables as well as the specified variables themselves. If summary variables are created with a GET VARS ALL rowm a record type, then the sequence is the same as the record.

The whole list can be enclosed in brackets (recommended) and thus the list starts and stops with brackets (). If the list is not in brackets then one of the following signifies the end of the list:
A new command is read. That is a command that starts in the first column, not simply the next clause on the same command.
A special character is read, in particular a slash /. Other special characters may be invalid.
A name is processed that is not a valid variable. This is taken to be a keyword for the command and is processed as such. If it is not valid keyword, then an error message ‘Error 4 Keyword is invalid' is generated.

Some procedures allow certain non-standard specifications in their variable lists and these are noted in the documentation for those procedures.

SORT Specifies the sequence of the output. n is an integer that specifies the maximum number of records to be sorted. The default for this parameter is either the number of records in the database or the value specified in the sortn parameter and need only be specified if the number of records in the procedure table is greater than the default. The procedure table is sorted by the specified variables in variable list order. A variable name followed by (A) or (D) specifies that for that variable the sort is in Ascending order (the default) or in Descending order.
A sort is implicit when a BREAK clause is specified in Quick Report.

BOOLEAN Selects procedure table records used by the procedure. The procedure table records that match the logical expression are selected. If this option is not specified, all procedure table records are used.

SAMPLE Selects a random sample of the procedure table records for use by the procedure.
The fraction specifies the percent of records used and is specified as a positive decimal number less than or equal to 1 (one). e.g. Specify .25 to use a 25% sample.
If you wish to alter the default seed used to initialise the random number generator, use the SEED option on the retrieval command.

Procedure Table

The procedure table is built during the execution of the main routine and contains a number of data records . Each record in the table contains the values of a number of variables, referred to as the Procedure Variables. The values in these variables are copied into the procedure table whenever the PERFORM PROCS command is executed.

Procedure Variables

Each VisualPQL program, retrieval and subroutine has its own local or program variables. VisualPQL can explicitly declare variables using commands such as INTEGER, REAL, STRING, DATE and TIME. In addition, local variables are implicitly declared when a value is assigned to an undeclared variable through commands such as COMPUTE, SET and GET VARS. See variables for further details.

Each variable and array has a schema entry that includes information about data type, valid values, missing values, variable labels and value labels. The GET VARS command copies schema information from the database or table into the schema for that local variable. The schema for other variables is created from the variable declaration and definition commands in the program. The VisualPQL Procedures use these schema entries as they format their output.

A routine may access database or table data and may reference an external block of variables.

Selecting Variables for the Procedure Table

By default, all of the local variables, excluding arrays, are procedure variables. The DEFINE PROCEDURE VARIABLES command specifies particular variables to use to build the procedure table. Using this command can result in performance improvements if the program has a large number of local variables that are not required by the procedures. This command is also used to include data from arrays or from EXTERNAL VARIABLE BLOCKs in the procedure table.

Conditional Use of Procedure Table Records

The BOOLEAN option specifies selects records from the procedure table to include in the procedure. This feature is useful when specifying multiple procedures in a single VisualPQL program where each procedure is to work with a subset of the procedure table records.

Selecting Variables from the Procedure Table

The procedures allow any of the variables in the procedure table to be accessed by name. Certain procedures output all variables if particular variables are not specified with the VARIABLES keyword on the procedure command. The INCLUDE and EXCLUDE commands alter the default variables used by a procedure; they do not alter the content of the procedure table, merely the default list of variables. These commands are placed in the main routine anywhere before the first procedure command. INCLUDE and EXCLUDE are mutually exclusive; specify either one or the other, not both.

If the DEFINE PROCEDURE VARIABLES command has excluded a particular variable from the procedure table, do not specify that variable on an INCLUDE or EXCLUDE as this has no effect.

INCLUDE 

INCLUDE variable, .....
Specifies the default variables referenced by all procedures. The order of the variables determines the sequence used on the output file.

EXCLUDE 

EXCLUDE variable, ...
Excludes variables from the default variable list.

Disk Space

If a large procedure table has to be sorted, a temporary file is used. Be sure that sufficient disk space is available. Use the DEFINE PROCEDURE VARIABLES command to include only the variables that are needed.

Saved Executables

If large programs are run frequently, consider saving the compiled, executable version of the program. See the SAVE = option on the RETRIEVAL and PROGRAM commands. The NOPROCS option on these commands saves the executable version of the program without the procedure specifications. This executable can be run and procedure commands appended to it. The general structure for this is:

Create the executable: 

RETRIEVAL | PROGRAM   SAVE=member_name  NOPROCS NOEXECUTE
 PQL_COMMAND ...
 PERFORM PROCS ...
 ...
 PQL_PROC_COMMAND
 END RETRIEVAL | PROGRAM
Run the executable: 
 RETRIEVAL | PROGRAM   GET = member_name
 PQL_PROC_COMMAND . . .
 PQL_PROC_COMMAND . . .
 END RETRIEVAL | PROGRAM

Saving the Procedure Table

Use SAVE TABLE to save the Procedure Table as a SIR/XS Tabfile table if necessary. Run any subsequent procedures against this subset of data rather than the full database. For example

Create the table: 

RETRIEVAL | PROGRAM   SAVE=member_name NOPROCS NOEXECUTE
 PQL_COMMAND ...
 PERFORM PROCS ...
 ...
 SAVE TABLE MYTABFILE.MYTABLE
 END RETRIEVAL | PROGRAM
Use the table: 
PROGRAM
.  PROCESS ROWS MYTABFILE.MYTABLE
.    GET VARS ALL
.    PERFORM PROCS
.  END ROW
PQL_PROC_COMMAND option
END PROGRAM

homecontents start chapter top of pagebottom of pagenext page index