Variables

Explicit Variable Declarations

Simple Variables

INTEGER*1 gender
REAL*8 total total1
STRING*25 name
DATE curdate ('DDIMMIYYYY')

Extended Variable Definitions

Variable Lists

INTEGER*1 VAR1 TO VAR10
SET VAR1 TO VAR10(2,4,6,8,10,12,14,16,18,20)

Variables can be referenced in a TO list format. A TO list specifies a beginning and ending variable. The following example declares seven variables and assigns a value to NAME, ADDRESS, CITY, STATE and COUNTRY. REGION and ZIPCODE are not affected by the SET command because they are not part of the implied list of variables.

STRING*25 REGION NAME ADDRESS
STRING*10 CITY STATE COUNTRY ZIPCODE
SET NAME TO COUNTRY ( 'Unknown' )

An individual variable can be referenced by specifying an index value after the TO list that specifies the position of the variable in the list. Specify the index value immediate following the TO list specification, enclosed in parentheses. For example:

INTEGER*1 NUMA NUMB NUMC NUMD    | declare variables
SET NUMA TO NUMD (11,12,13,14)   | assign values to variables
COMPUTE NUMX = NUMA TO NUMD(3)   | put 3rd var into NUMX

Arrays

type [* size] ARRAY array_list (dimension [,...] ) [ (format)]

type	Variable type: INTEGER, REAL, STRING, DATE or TIME
size	Size of the integer, real or string variables.
ARRAY	Keyword specifying that arrays are being declared
array list	A list of the names of the arrays. Do not use VisualPQL function names. Do not use names of other variables referenced in the routine.
dimension	The number of occurrences in a dimension. You can specify a dimension either as a single number that is the number of entries in the array with references starting at 1, or as a start and finish pair of references separated by a colon ':' where the number of entries is the difference between these values plus 1. The number of entries must be a positive integer. e.g. INTEGER*4 ARRAY monthtot (12) INTEGER*4 ARRAY yeartot (1990:2009)

You can declare arrays for any of the basic data types. Following is the syntax for each data type:

INTEGER [* {1 | 2 | 4} ] ARRAY name_list (dimension [,...])
STRING  [* num_le_254  ] ARRAY name_list (dimension [,...])
REAL    [* {4 | 8 }    ] ARRAY name_list (dimension [,...])
DATE                     ARRAY name_list (dimension [,...]) ('date format')
TIME                     ARRAY name_list (dimension [,...]) ('time format')

The REDEFINE ARRAY command alters the dimensions of a locally defined array; arrays defined in EXTERNAL VARIABLE blocks cannot be redefined. The number of dimensions can be altered as well as the value of any dimension. The array can grow or shrink and existing values are mapped to the new dimensions. Any new values are set to missing.

Note that the array name is an expression, that is a string variable, expression or constant. To specify the name of the array to be redefined directly, simply enclose the name in quotation marks.

The VisualPQL compiler checks array subscript references where possible and warns if these do not match the array definition. If arrays are redefined, this checking may result in unwanted warnings. These can be suppressed with the NOARRAYMSG option. For example:

PROGRAM NOARRAYMSG
INTEGER*4 ARRAY NUM1 (50)
FOR I = 1,50
. COMPUTE NUM1 (I) = I
END FOR
WRITE "Before redefine"
WRITE 'NUM1 (1) (50) Should be 1 50      ' NUM1(1)  NUM1(50)
REDEFINE ARRAY 'NUM1' (50,2)
WRITE "After redefine of NUM1 to (50,2)"
WRITE 'NUM1 (1,1)  (50,1) Should be 1 50 ' NUM1(1,1) NUM1(50,1)
WRITE 'NUM1 (1,2)  (50,2) Should be * *  ' NUM1(1,2) NUM1(50,2)
END PROGRAM

SORT

SORT array_name
     [BY key_array_varname]
     [(n)]
     [DESCENDING]
     [ALTERNATE]

The SORT command sorts the entries in an array. By default, all entries are sorted according to their values into ascending sequence. Multiple dimensions are sequenced as a single extended dimension e.g. If an array has two dimensions then entry (1,1) is first, (2,1) is second through to (n,1) that is followed by (1,2) etc. Note that the names of arrays specified in this command are specified directly, they are not expressions.

BY key_array_varname	One array can be sorted according to the values in a second array. The system matches the two arrays positionally and then sorts the original array according to the values in the named key array. If the arrays are different in size, the smaller value is used. When there are tied values in the key array, the original array order is kept for those elements; if the sort is DESCENDING then the reverse order is applied.
(n)	The sort can be restricted to the first N entries.
DESCENDING	The sort can be into descending sequence.
ALTERNATE	The will sort multidimensional arrays using the later dimensions as more significant keys. For example a two dimensional ARRAY N (row,column): 3 8 7 9 4 1 6 2 5 SORT N sorts by column then row: 1 4 7 2 5 8 3 6 9 SORT N ALTERNATE sorts by row then column: 1 2 3 4 5 6 7 8 9

Implicit Variables

Variables are implicitly defined in VisualPQL in two ways:

Declaration by Assignment

COMPUTE A = B

Numeric variables are declared as REAL*8. String variables are STRING*w where w is the current value of STRING LENGTH - default 32. Assigning dates and times creates numeric variables. Variables defined by assignment have no definitions other than the variable name, type and length.

The CRWARN option on the routine definition command issues a warning message during compilation whenever a variable is created by assignment.

CAT VARS

Specifies string variables that are held as categorical integers and defines the set of string values that can be held for the variable. The variables must first be explicitly defined as string variables and these cannot be arrays.

The values in the value list are each enclosed in single quote marks (') and the list for a variable is enclosed in parentheses. Specifications for multiple variables may be separated with a slash (/) for readability.

Internally, categorical variables are held as integers that are the position of the string in the value list. The variable may be treated either as a string or as a number depending on context. If the categorical variable is assigned to an undeclared variable, a numeric variable is created. If it is written without a format specification, the string is written. For example:

program varmap
string*1 str1 str2
cat vars str1 ('a','b','c')
compute str1 = 'a'
compute x = str1
compute str2 = str1
write   str1  str2 x
end program
Variable list for Main Program
 Variable Name                  Proc   Type
 STR1                             Y    CI*3
 STR2                             Y    S*1

AUTOSET Variables
 X                                Y    R*8
Start program execution
a a 1
End program execution

CONTROL VARS

DATE

Date variables are four byte integers. Dates are held as the number of days between the date and the start of the Gregorian calendar where October 15, 1582 is day 1. Dates can be represented as formatted strings and translated according to the date format.

When a date is assigned to another variable either the integer value or the equivalent formatted value is moved. If the assigned variable is numeric or undefined, the integer value is assigned. If the assigned variable is a string variable, the formatted string value is assigned.

INTEGER

The INTEGER command declares the listed variables as integers. Optionally specify the size of the variables as 1, 2 or 4 byte integers. If a size is not specified, the variables are 4 byte integers.

• INTEGER*1 has values from -128 to 123

• INTEGER*2 has values from -32,768 to 32,763

• INTEGER*4 has values from -2,147,483,648 to 2,147,483,643

Example:

PROGRAM
INTEGER*1 SCORE1 TO SCORE5 SEX
INTEGER*2 MONTHSAL
INTEGER*4 YEARSAL
SET SCORE1 TO SCORE5 (0)
SET SEX (1)
SET MONTHSAL (2500)
COMPUTE YEARSAL = MONTHSAL * 12
WRITE MONTHSAL ('99,999') 2X YEARSAL ('999,999')
END PROGRAM

MISSING VALUES

MISSING VALUES specifies up to three values for a variable that are treated as missing. Missing values are excluded from statistical procedures and functions. A missing value is, by definition, a valid value for the variable and need not be re-specified.

The missing values can be constants or the keyword BLANK. If BLANK is not a missing value for a numeric variable, then blanks are stored as 0 (zero).

Missing values can be specified for string variables. Missing values for string, date and time variables are specified as strings. If the specified missing value matches the leftmost input characters, missing values are recorded.

Missing values can be specified for an array. Specify the array name in the command, not specific array elements.

For example, the following declares several variables and defines missing values for them. If the date 01/01/01 is assigned to TESTDATE, the value is treated as missing. If either a blank or the letters ZZ are assigned to STATE, they are considered missing. For the numeric array and numeric variables, the value 9 is treated as missing. If blanks are input with a READ command, they are treated as missing.

DATE TESTDATE ('MMiDDiYY')
STRING*2  STATE
INTEGER*1  ARRAY QUESTION (25)
INTEGER*1 MATHTEST READTEST
MISSING VALUES  STATE (BLANK ,'ZZ') /
                QUESTION MATHTEST READTEST (BLANK , 9) /
                TESTDATE ('01/01/01')

OBSERVATION VARS

REAL

The REAL command declares the listed variables as double precision, real, floating point numbers. REAL*4 (single precision) and REAL*8 (double precision) are also allowed.

When assigning a value to real variables, integers can be used without a decimal point.

SCALED VARS

SCALED declares the integer variables are scaled to power n. N is a positive or negative number representing the power of ten to which the variable is scaled.

If the variable has not been defined previously, this defines an INTEGER*4 variable. To create a different length integer, define the variable before declaring the scaling factor. The full, unscaled number, including any decimal point, is used wherever this number is referenced.

STRING

STRING declares the listed variables as string of maximum length number. The maximum length of a string variable is 4094. If a length is not specified, the default is the current setting of STRING LENGTH, that by default is thirty two characters. If more characters than the declared string length are assigned to a variable, the string is truncated to the declared length.

TIME

Time variables are four byte integers. Times are held as the number of seconds between the time and the previous midnight. Times can be represented as formatted strings and translated according to the time format.

When a time is assigned to another variable either the integer value or the equivalent formatted value is moved. If the assigned variable is numeric or undefined, the integer value is assigned. If the assigned variable is a string variable, the formatted string value is assigned.

VALID VALUES

Specifies the set of specific valid values a numeric variable can assume. If both VAR RANGES and VALID VALUES are defined for a variable, both specifications must be satisfied. Attempting to store a value in the variable that is not either a valid Missing Value or a Valid Value results in undefined. When a variable is updated during the running of a program, data validation takes place in the following order:

1. Missing Values
   
2. Valid Values
   
3. Variable Ranges

Examples:

INTEGER * 1 VAR1 TO VAR5 SCOREA SCOREB SCOREC
VALID VALUES VAR1 TO VAR5     ( 1 , 2 ) /
             SCOREA TO SCOREC ( 1, 2, 3, 88, 99 )

VALUE LABELS

VALUE LABELS varlist (value1 ) 'label text'
                   [ (value2 ) 'label text_2' [...]]
                   [...]

Specify value labels for multiple values of a single variable as one continuous command. If a number of variables have the same value labels, you can specify a list of variables, followed by the values and labels. If specifying value labels for an array, specify the array name not individual array elements. You can specify value labels for several variables on the same command.

For example, to declare a string variable, an integer variable and a 25 element array and define value labels for each:

PROGRAM
STRING*3 STATE
INTEGER*1 REGION
INTEGER*1 ARRAY QUESTION (25)
VALUE LABELS QUESTION (1) 'Yes'
                      (2) 'No'
             REGION   (1) 'North'
                      (2) 'South'
                      (3) 'East'
                      (4) 'West'
             STATE ('NSW') 'New South Wales'
                   ('QLD') 'Queensland'
                   ('VIC') 'Victoria'
SET STATE REGION ('NSW',1)
SET QUESTION *   ( 1)
COMPUTE STATEV  = VALLAB(STATE)
COMPUTE REGIONV = VALLAB(REGION)
COMPUTE QUESTV  = VALLAB(QUESTION(1))
WRITE STATEV REGIONV QUESTV
END PROGRAM

VAR LABEL

VAR RANGES

Assigning Values

Missing Values

Some specific values of a variable may be treated as missing. A variable SEX might have valid value of 1 and 2 for Male and Female, and a value 3, for Unknown, that is treated as missing.

There are functions and procedures to get and use the actual value of the variable. In general, operations that result from evaluating a missing or undefined value yield an undefined value (e.g. adding a number to an undefined value yields an undefined value). Functions that calculate statistics on a set of values ignore undefined values.

The numeric value 0 (zero) is a normal numeric value and is different from undefined. A zero length string (a string with no characters) is also a valid value that is different from an undefined string.

Logical tests evaluate to true or false. When specifying logical tests remember that a missing value or undefined in a logical test always evaluates to false.

Expressions

COMPUTE REGION = 'Western ' + 'Canada'
COMPUTE TOTAL = 10 + 17
Expressions have two main elements; other expressions and operators. Operators are a symbol that specifies an operation between two expressions. Parentheses () may be used to specify the precedence (order) of operations.

Simple expressions are:

• Variables

• String and Numeric Constants

• Functions

Variables

• a number

• one decimal character (the period)

• a leading plus or minus sign (the + or - )

• a trailing letter E to indicate exponentiation followed by a number (that may be signed)

Following are valid examples of the SET command using several forms of expressing numeric constants.

String Operators

PROGRAM
STRING*40 ADDRESS
INTEGER ZIPCODE
COMPUTE CITY    = 'Chicago'
COMPUTE STATE   = 'Illinois'
COMPUTE ZIPCODE =  60614
COMPUTE ADDRESS =  CITY + ', ' + STATE + ' ' + FORMAT(ZIPCODE)
WRITE ADDRESS
END PROGRAM

In this example, ADDRESS is computed from three types of simple value expressions; string constants in quotes, variable names and the FORMAT function that converts a number to a string.

• + the plus sign performs addition

• - the minus sign performs subtraction

• * the asterisk performs multiplication

• / the slash performs division

• ** the double asterisk performs exponentiation, a number raised to a power. A number raised to a reciprocal power yields the root

Enclose signed constants that follow an arithmetic operator in parentheses. For example:

COMPUTE NUM1 = 10 + 20 + 33
COMPUTE NUM2 = 100 - NUM1
COMPUTE NUM2 = NUM1 * 5
COMPUTE NUM1 = 2 / 3
COMPUTE NUM1 = 4**3        | 4 cubed
COMPUTE NUM2 = NUM1**(1/3) | cube root
COMPUTE NUM = 13 * (-2)

In arithmetic expressions, operations of equal precedence are done from left to right. The precedence of operations is:

Database Variables

AUTOSET

COMPUTE

EVALUATE

GET VARS

GET VARS local_var_list = db_var_list	The values of local variables are assigned the values of the database or table variables. The two lists must be of equal length and the value assignments are performed listwise.
GET VARS db_varlist	There is an assumed left hand side list of local variables with the same names as the database or table variable list.
GET VARS ALL	The keyword ALL specifies all record or table variables are assigned to local variables of the same name.
PREFIX |SUFFIX 'text'	The keywords PREFIX and SUFFIX followed by text in quotes, specify text to append to the record or table variable names to create local variables with modified names. The text is used exactly as specified so ensure the correct case (upper/lower) is used. If the modified name exceeds to maximum length for names (32 characters), a warning is printed and the unmodified name is used.

This is the only command that accesses table variables for input.

For example: the GET VARS command is used three times to retrieve database and table data and copy it into local variables.

RETRIEVAL
PROCESS CASES ALL
. GET VARS ID
. PROCESS RECORD EMPLOYEE
.  GET VARS NAME GENDER PREFIX 'EMPLOYEE_'
.  PROCESS ROWS OCCTAB INDEXED BY OCCINDEX VIA  (ID )
.   GET VARS  POS  START = CURRENT_POSITION  START_DATE
.   PERFORM PROCS
.  END PROCESS ROWS
. END PROCESS REC
END PROCESS CASE
REPORT   FILENAME = TEST.LIS
         PRINT = ALL
END RETRIEVAL

PRESET

PRESET varlist ( repeat_value * value [ value_list])

In the following example, the first four variables are set to 2, the next three are set to 12 and the last three are set to 7,8 and 9 respectively.

PRESET VAR1 TO VAR10 (4*2,3*12,7,8,9)

INTEGER*1 ARRAY A (3,2)    | declare two dimensional array A
PRESET A * ( 0 )           | preset all elements to 0
PRESET A * ( 1,2,3,4,5,6 ) | set each element to unique value
PRESET A(1,1) A(2,1) A(3,1) A(1,2) A(2,2) A(3,2)
        ( 1,2,3,4,5,6 )    | Equivalent to previous command

PUT VARS

PUT VARS db_varlist = local_var_list	The values of the database or table variables are assigned the values currently held by the local variables on the right side of the equals sign. The lists of variables must be of equal length. The value assignments are performed list wise; the first right side value is assigned to the first left hand variable, the second right to the second left, and so forth.
PUT VARS db_varlist	There is an assumed right side list that is identical to the database or table variable list. The referenced database or table variables must have the same name as local variables. Note that the PUT VARS takes local variables as the source and sets database variables to be the same as the local variables. As these have the same name, there is an opportunity for confusion if the variable values were set inside the database block. e.g. PROCESS REC EMPLOYEE GET VARS SALARY COMPUTE SALARY = SALARY * 1.1 PUT VARS SALARY END PROCESS REC Because this is in a record processing block, the database variable SALARY is updated by the compute, not the local variable with the same name. Then the PUT VARS would restore the original value of salary because that is the current value in the local variable. If database variables are updated inside a record block, the PUT VARS is unnecessary.
PUT VARS ALL	Any record or table variables with the same names as local variables are updated.

Values of keyfields in records may not be updated. Values of keyfields of the index being used on table rows may not be updated.

For example: The program retrieves data from the database and creates a new row on a table, if one does not already exist for that employee in that position. The two forms of PUT VARS are used, one to move a local variable to a row variable of the same name, the other to set a row variable of a different name. Note that the index variables are set by the reference on the ROW IS and are not referenced by a PUT VARS.

RETRIEVAL  TUPDATE
PROCESS CASES ALL
.  GET VARS ID
.  PROCESS RECORD EMPLOYEE
.    GET VARS NAME  CURRPOS SALARY CURRDATE
.    NEW ROW IS OCCTAB INDEXED BY OCCINDEX (ID , CURRPOS)
.       PUT VARS  START_DATE = CURRDATE
.       PUT VARS  SALARY
.    END ROW IS
.  END PROCESS REC
END PROCESS CASE
END RETRIEVAL

RECODE

RECODE  [ update_var = ] recode_var
        [ (value_list =  recode_value) [...]]
RECODE  [ update_var_list = ] recode_var_list
        [ (value_list =  recode_value) [...]]

The values in the value list and the recode value are either constants or one of the value keywords documented below. (Expressions and variable names are not allowed as values.)

recode variable	The variable or array element with the initial value to recode. If an update variable is not specified, this variable is updated with the recoded value. You can specify a list of recode variables. If an update variable list is specified then it must be the same length: RECODE A B C (1=3) (3=1) RECODE D TO F = A TO C (1=6) (2=7) (3=8)
update variable	The variable or array element to receive the recoded value if the original recode variable is not updated. The update variable must be a data type compatible with the recode value. You can specify a list of variables to be updated with a corresponding list of recode variables: RECODE A B C = D E F (1=3) (3=1)
recode value	The values in the value list are converted to this single value. This value must be the same type as the update variable. Specify the keyword UNDEFINED to recode values to undefined.
value list	Specify the list of original values to recode. Specify a value list for each single recode value. Any value in the list is recoded into the single recode value. If the value of the variable is not found in a value list, the value is not recoded and is assigned unaltered to the update variable. Specify a separate parenthesised recode value list for each recode value. In the following examples: In the first, if RVAR is 1, 2 or 3, UVAR is recoded to 0. If RVAR has any other value, RVAR is copied to UVAR. In the second example, UVAR is 0 if RVAR is 1, 2 or 3 and 1 (one) if RVAR is 4, 5 or 6 : RECODE UVAR = RVAR(1,2,3 = 0) RECODE UVAR = RVAR(1,2,3 = 0)(4,5,6 = 1)

You may use a number of keywords in the value list.

THRU	Specifies an inclusive range of values. For example: RECODE UVAR = RVAR (1 THRU 3 = 0)(4 THRU 6 = 1) The value lists can overlap avoiding the possibility that a value (such as 3.5) falls between two value lists and is not recoded. The first match determines the recode used. Multiple ranges and multiple values can be specified in a value list. For example: RECODE UVAR = RVAR (1 THRU 3,7 THRU 99 = 0)(3 THRU 7 = 1)
LOWEST,LO	Specifies the lowest possible value. For example: RECODE UVAR = RVAR (LO THRU 3 = 0)(3 THRU 6 = 1)
HIGHEST,HI	Specifies the highest possible value. For example: RECODE UVAR = RVAR (LO THRU 3 = 0)(3 THRU HI = 1)
UNDEFINED	Specifies an undefined value. For example: RECODE UVAR = RVAR (UNDEFINED,LO THRU 3 = 0)(3 THRU 6 = 1) UNDEFINED may also be used as the recode value. For example: RECODE UVAR = RVAR (LO THRU 3 = 0)(3 THRU HI = UNDEFINED)
MISSING(0) MISSING(1) MISSING(2) MISSING(3)	Specifies missing values. MISSING(0) is a synonym for UNDEFINED. MISSING(1) refers to the first missing value, MISSING(2) to the second, and MISSING(3) to the third. For example: PROGRAM INTEGER*1 RVAR MISSING VALUES RVAR (7,8,9) SET RVAR (9) RECODE UVAR = RVAR (MISSING(1)=4) (MISSING(2)=5) (MISSING(3)=6) WRITE UVAR END PROGRAM
BLANK	Specifies that the blank missing value is recoded. For example: RECODE UVAR = RVAR (BLANK,7 THRU HI = 0)
ELSE	Specifies a recode for all values not included in any previously defined value list. If ELSE is specified, no other values may be specified in the value list. This must be the last recode specification of a set. For example: RECODE UVAR = RVAR (1,2,3 = 1)(4,5,6 = 2)(ELSE = 0)

PROGRAM
INTEGER*1  NUMVAR
STRING*1   STRVAR
SET     STRVAR ('A')
RECODE  NUMVAR = STRVAR('A' = 1)('B' = 2)(ELSE = 0)
WRITE   STRVAR NUMVAR
END PROGRAM

If the recode variable in a mixed data type recode has a value not referenced in a recode value list, the update variable is set to undefined.

SET

Assigns explicit values to variables and array elements during execution. Values in the value list are assigned in list order to the variables in the variable list. If the value list is shorter than the variable list, VisualPQL cycles through the value list until a value has been assigned to each variable. If the value list is longer than the variable list, the excess values are ignored.

Value Keywords for Undefined Values

Repeat Values

SET varlist ( repeat_value * value [ value_list])

In the following example, the first four variables are set to 2, the next three are set to 12 and the last three are set to 7,8 and 9 respectively.

SET VAR1 TO VAR10 (4*2,3*12,7,8,9)

Setting Array Elements

PROGRAM
INTEGER*1 ARRAY A (3,2)  | declare two dimensional array A
SET A * ( 0 )            | set all elements to 0
SET A * ( 1,2,3,4,5,6 )  | set each element to unique value
WRITE A(1,1)
END PROGRAM

The second SET statement in the above example is equivalent to:

SET A(1,1) A(2,1) A(3,1) A(1,2) A(2,2) A(3,2) ( 1,2,3,4,5,6 )

EXTERNAL VARIABLE BLOCK

EXTERNAL VARIABLE BLOCK member[:V]
       [ NOSAVE ] [ REPLACE ] [PUBLIC ] [ VARMAP ]
...
variable definitions ...
...
END EXTERNAL VARIABLE BLOCK

INCLUDE EXTERNAL VARIABLE BLOCK

Includes the variables as local variables in the routine. Do not declare variables from the included block in the routine that includes the block.

DEFINE PROCEDURE VARIABLES

DEFINE PROCEDURE VARIABLES [ { INCLUDE | EXCLUDE } (varlist)]
       [ NOARRAYS    | ARRAYS   ]
       [ NOEXTERNALS | EXTERNALS [
       (external_block_list) ] ]
       [ NOSIMPLE    | SIMPLE  ]