
VisualPQL Procedures		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 which 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 which 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 which 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 SIR2002 tabfiles.
SIR SAVE FILE Creates a sequential copy of a SIR2002 database.

SPREAD SHEET Displays data in a spreadsheet style format (only available on Windows).

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.

Syntax

Each of the procedures is specified with a command which has keywords to specify options. Some keywords are common to all the procedure commands.

Keywords and associated options are continuations of the command, each separated from other keywords with a slash ( / ). 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 multiple procedures of the same type are specified in one program, 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
VARIABLES = variable list | ALL
SORT      = [ (n) ] variable [(A)|(D)], ...
BOOLEAN   = (logical_expression )
SAMPLE    = fraction [ ,seed ]

`FILENAME`	Specifies the filename created by the procedure. The special name `CONSOL` can be used to display the result of a procedure in the scrolled output buffer. This is a required clause except where noted.
`VARIABLES`	Use on procedures which output an interface file. Specifies the procedure variables that are written to the output file. The order in which variables are specified is the order in which they appear in the output file. If this option is not specified, the default variables are output. (See `INCLUDE` and `EXCLUDE`.)
`SORT`	Specifies the order in which the procedure table records and therefore the output are sorted. 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`	Specifies which procedure table records are used by the procedure. The procedure table records for which the logical expression is true are used by the procedure. If this option is not specified, all procedure table records are used.
`SAMPLE`	Specifies that a random sample of the procedure table records are used 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). .25, for example specifies that a 25% sample be used. The optional seed is an integer that is used to initialise the random number generator.

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 variable schema map which 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 map for that local variable. The schema map for other variables is created from the variable declaration and definition commands in the program. The VisualPQL Procedures use these schema maps 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 which are not required by the procedures. This command is also used to include data from arrays or from

EXTERNAL VARIABLE
BLOCK

s in the procedure table.

Conditional Use of Procedure Table Records

The BOOLEAN option specifies which records in the procedure table are included 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 SIR2002 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

`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 which 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 SIR2002 tabfiles.
`SIR SAVE FILE`	Creates a sequential copy of a SIR2002 database.
`SPREAD SHEET`	Displays data in a spreadsheet style format (only available on Windows).
`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.