|
![]() | ![]() ![]() ![]() ![]() ![]() ![]() ![]() | Variables |
The VARMAP
option prints a list of program variables.
Every variable has a name and a data type. Variables may have extended definitions such as value labels and missing values.
All of the definitions that can be given to database variables in schema definition may be given to variables in routines. The extended variable definitions can be explicitly defined or copied from the dictionary schema with the GET VARS
command.
Variable declarations and extended definitions typically appear at the beginning of a routine. The declaration of a variable must precede any extended variable definition commands. Variable definitions must precede any reference to the variable whether the declaration is implicit or explicit. The code that defines the variable must physically precede the lines of code that reference the variable. Only define variables in subprocedures if the variable is only referenced in the subprocedure.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
DATE, INTEGER, REAL, STRING, TIME
. The type can be followed by a length and a format for date and time. For example:
INTEGER*1 gender REAL*8 total total1 STRING*25 name DATE curdate ('DDIMMIYYYY')
VALUE LABELS
that defines descriptive labels for individual values of a variable.
VAR LABEL
that defines a 78 character label for the variable that can be used in place of the variable name.
MISSING VALUES
that defines specific values that are treated as missing in computations and statistical procedures.
VALID VALUES
and VAR RANGES
that defines values or ranges of values that are valid for this variable.
SCALED VARS
that defines a scaling factor for an integer variable. The scaling factor is a power of ten, negative values specify decimal places, positive values specify tens, hundreds, etc.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
TO
keyword and use the same method to reference the variable. The order of local variables is determined by the order they are declared in the program. The order of database variables is determined by the order they are defined in the schema. Typically, programs declare variables whose names indicate a position within the list but this is not necessary. For example
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
Variable list references may appear anywhere an expression may appear. The index value may be any numeric expression, including variable names, array references and more complex expressions. For example:
COMPUTE NUMA TO NUMD(3) = 32 IF(NUMA TO NUMD(3) EQ 32) WRITE 'O.K.'Note: The variable reference is resolved and the variable moved to a temporary string or numeric variable before further computations are done. This means that variables such as categorical, date and time variables always return their numeric value when referenced in a
TO
list.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
type [* size] ARRAY array_list (dimension [,...] ) [ (format)]
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')
Some commands, such as SET
and PRESET
, can operate on whole arrays, in which case reference the array by name plus an asterisk *. The extended variable commands refer to whole arrays. Most other commands operate on individual array elements.
Reference array elements by the array name and the element location within the array in parentheses, commonly called the array subscript. The subscript may be a numeric expression or constant. Specify a value for each dimension: e.g.
COMPUTE MONTHTOT(12) = TOTAL COMPUTE TOTAL = MONTHTOT(MONTH) COMPUTE JAN01 = DAILYTOT(1,1) COMPUTE DEC31 = DAILYTOT(12,31)
REDEFINE ARRAY array_name_exp (dim1, dim2,...)
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 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) :
SORT N sorts by column then row:
SORT N ALTERNATE sorts by row then column:
|
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Variables are implicitly defined in VisualPQL in two ways:
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.
GET VARS
command implicitly declares new program variables copying the type and format, value labels and missing values from the schema definition of a database or table variable. GET VARS
can copy individual variables or can use the keyword ALL
that implicitly declares new program variables for all variables from a given record or table. A routine may access record variables directly in an appropriate block structure or may copy the data into an internal variable for further processing. For example:PROCESS REC EMPLOYEE GET VARS NAMEThis creates a new implicit program variable
NAME
. This program variable is available outside the PROCESS REC
block. The GET VARS
command can copy database or table variables into explicitly defined local variables, in which case the definition of the variable is not affected.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
CAT VARS varname ('value' .... ) varname ('value' .... ) ....
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 varlist
Declares a list of variables or arrays that are Control variables for the TABULATE
procedure.
The variables and arrays named on the command must be numeric and must have a VAR RANGES
defined.
By default, variables that have VALID VALUES
or VALUE LABELS
are automatically control variables.
All other numeric variables are observation variables, that is variables with continuous values.
PROGRAM INTEGER*2 var1 VAR RANGES var1 (1,30) CONTROL VARS var1 .... PERFORM PROCS .... TABULATE var1 ....
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
DATE varlist ('date format') [ ... ]
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.
The date format defines the default format. That is the format that is expected on input, is written on output and is assigned to string variables. See date formats for a complete description.
CautionWhen comparing dates and strings remember that the date is converted to a string using its default format then compared with the string. For example (assuming the date format for birthday isDDIMMIYYYY ):IF (BIRTHDAY lt '01 01 2007') SET AGEGROUP (2)This is a comparison of strings does not classify dates correctly as any date string that has days of the month greater than 1 (i.e.'02 mm yyyy') is greater than the string '01 01 2007'.
If the date format for birthday is IF (BIRTHDAY lt '2007/01/31') SET AGEGROUP (2)This compares strings like '2007 12 31' with '2007/01/31'. Again this gives rise to errors because the former is less than the later because the character ' '(blank) has a lower ASCII value to the slash. |
For example, the following program declares and uses date variables. The program expects a string such as 'Jan 30, 2007' as input for BIRTHDAY
and a string like '01-30-07' for VISDATE
. The input strings '01 30, 2007' and 'JA/30/07' are also valid. Note that on output, the default separator characters are spaces not slashes or dashes. Use the format options on the write to output other characters.
PROGRAM DATE BIRTHDAY ('MMMiDDiiYYYY') / VISITDAT ('MMiDDiYY') COMPUTE BIRTHDAY = 'Feb 26, 1970' COMPUTE VISITDAT = '07/13/05' WRITE 'Born on ' BIRTHDAY ('WWW DD/MMM/YYYY') 'and visited on ' VISITDAT ('DD/MM/YYYY') END PROGRAM
The M, D and Y strings cannot be split. The following is not allowed:
DATE BIRTHDAY ('YiMMiY')
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
INTEGER [ * { 1 | 2 | 4 } ] varlist
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,643Example:
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 varlist (value [,value [,value]]) [/...]
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 varlist
Specifies variables and arrays that the TABULATE
procedure use as Observation Variables. By default, variables that have Valid Values or Value Labels are Control Variables. OBSERVATION VARS
changes these to Observation Variables.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
REAL [ * { 4 | 8 } ] varlist
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 varname (n)
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 [ * number] varlist
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 varlist ('time format') [ ... ]
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.
The time format defines the default format. That is the format that is expected on input, is written on output and is assigned to string variables. See time formats for a complete description.
CautionWhen comparing times and strings remember that the time is converted to a string using its default format then compared with the string.
e.g. (assuming the time format for START is
In these cases it is best to convert the string to a number for the comparison: |
H | A number of hours greater than 24 or minutes/seconds greater than 60 sets the variable to undefined. If hours, minutes or seconds are not input, they default to zero. |
The following program declares and uses time variables:
PROGRAM TIME STARTIME ENDTIME ('HHiMM') COMPUTE STARTIME = SREAD('Enter Starting Time (HH:MM)') COMPUTE ENDTIME = SREAD('Enter Quitting Time (HH:MM)') COMPUTE TTIME = ENDTIME - STARTIME WRITE 'You worked ' TTIME(TIME 'HH') ' hours and ' TTIME(TIME 'MM') ' minutes.' END PROGRAM
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
VALID VALUES varlist ( value_list ) [ ... ]
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:
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 varlist (value1 ) 'label text' [ (value2 ) 'label text_2' [...]] [...]Defines descriptive labels for individual values of a variable. Each label may be up to 78 characters long. Enclose labels in quotes. The keywords
UNDEFINED
and BLANK
can be used as values and assign labels to undefined or blank missing values.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 {variable | array } 'var label text'
VAR LABEL
specifies a descriptive label for a variable. A variable label may be up to 78 characters in length and may be enclosed in quotes. Labels for multiple variables may be specified on a single command. The variable label can be retrieved during program execution with the VARLAB
function.
Several VisualPQL Procedures automatically use a variable label if one is defined.
Examples:
STRING*3 STATE INTEGER*1 REGION INTEGER*1 ARRAY QUESTION (25) VAR LABEL STATE 'State of Residence' REGION 'Region of the State' QUESTION 'Survey Question'
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
VAR RANGES {variable | array } (min_value , max_value) [/ . . .]
Specifies the range of values that a variable can have. Input values outside the specified range are set to undefined. If specific VALID
VALUES
are defined for a variable, do not specify VAR RANGES
. If both are specified, the value must satisfy both specifications. When a variable is updated during the running of a VisualPQL program, data validation takes place in the following order:
Examples:
INTEGER*1 YRSEDUC YRSWORK YRSPLAY INTEGER*4 INCOME DATE LASTDATE ('MMiDDiYY') VAR RANGES YRSEDUC TO YRSPLAY ( 0,99 ) / INCOME ( 10000 , 90000) / LASTDATE ( '01/01/2004' , '12/31/2005')
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
AUTOSET
resets implicitly defined local variables to undefined. It is typically used to ensure that values from a GET VARS
in a RECORD/ROW
block are not carried forward accidentally when the block is not executed due to a non-occurrence of that record for this particular instance. It also resets any variable explicitly declared after the start of the routine (the first executable command). It resets the values each time the command is executed.
COMPUTE
sets a variable to a specified constant or expression value.
EVALUATE
compiles small VisualPQL expressions during execution, allowing programs to accept expressions 'on the fly'.
GET VARS
copies the definition and the value of a database or table variable to a local variable.
PRESET
sets the initial value of variables at compilation time. Pre-compiled subroutines and stored executable programs save any preset values as part of the executable image that is loaded and executed at run time.
PUT VARS
writes local data back into table or record variables.
SET
sets variables to given constant values at execution time. It resets the values each time the command is executed.
RECODE
recodes the value of a variable into itself or another variable.
The initial values of program variables are set to undefined unless PRESET
is specified.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
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.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
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:
EVALUATE
command it is possible to specify a string inside another string by using both types of quotation mark:
EVALUATE X = 'NUMBR ( "20")' + ' + 22'
Following are valid examples of the SET
command using several forms of expressing numeric constants.
SET TESTNUMB ( 22 )
SET TESTNUMB (+3.1)
SET TESTNUMB (-3.1)
SET TESTNUMB ( 4.5E-2)
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 rootEnclose 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:
COMPUTE NUM = 6 + 3 / 3 | NUM is 7 COMPUTE NUM = ( 6 + 3 ) / 3 | NUM is 3 COMPUTE NUM = 16**1 / 2 | NUM is 8, 16 divided by 2 COMPUTE NUM = 16**(1/2) | NUM is 4, square root of 16 MISSING VALUES NUM (1) COMPUTE NUM = 1 | NUM is missing COMPUTE NUM2 = NUM + 3 | NUM2 is undefined COMPUTE NUM3 = 1 / 0 | NUM3 is undefined
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
GET VARS
and PUT VARS
commands access case, record or row variables specifically.It is possible, even likely, that a local variable has the same name as a variable in the record. When a retrieval references one of these variables in a case or record block, VisualPQL determines which variable is used.
GET VARS
and PUT VARS
commands access ROW
variables directly. All other commands access local variables.
For example, the first program updates the salary on every employee record as well as listing the records. (Without the RETRIEVAL UPDATE
command, this would not compile). The second program does not update the database, it simply produces a list of new salaries:
RETRIEVAL UPDATE PROCESS REC EMPLOYEE . COMPUTE SALARY = SALARY* 1.1 . WRITE NAME SALARY END PROCESS REC END RETRIEVAL RETRIEVAL PROCESS REC EMPLOYEE . GET VARS NEWSALARY = SALARY . COMPUTE NEWSALARY = NEWSALARY * 1.1 . WRITE NAME NEWSALARY END PROCESS REC END RETRIEVAL
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
AUTOSET [ varlist ( value_list )]
AUTOSET
sets all implicitly declared variables and any variables not declared before the first executable command. An executable command is any command except variable declaration, variable definition and PRESET
commands. AUTOSET
is typically used to initialise local implicitly defined variables defined with GET VARS
. AUTOSET
sets variables to UNDEFINED
unless a variable list and value list is specified. If such a list is specified, all AUTOSET
variables are set to undefined and then the listed variables are set to the values specified in the parenthesised value list. If fewer values are specified than variables, the value list is cycled through as many times as needed to assign a value to each of the variables in the list.
In the following retrieval, AUTOSET
is used to make sure that values from a previous record type 3 record aren't accidentally carried over to another case if that case happens not to have a record type 3 record.
RETRIEVAL PROCESS CASES | for every case AUTOSET | initialise variables . PROCESS REC 1 | step thru rectype 1 recs . GET VARS ALL | move all vars to summary rec . PROCESS REC 3 REVERSE | step thru rectype 3, backwards . GET VARS ALL | move all vars to summary rec . EXIT REC | we only want this one, get out . END REC . PERFORM PROCS | copy summary rec to summary table . END REC
END CASE SAS SAVE FILE FILENAME = 'SAS.SYS' | create SAS file VARIABLES = ALL END RETRIEVAL
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
COMPUTE varname = expression
Assigns the value determined by the
expressions to a variable or array element. COMPUTE
cannot be used to set a whole array. (Use SET
)
The computed variable may be a local variable, an array element or a database variable.
The data type of the computed variable or array element must be compatible with the type implied by the expression. You must declare arrays before use with COMPUTE
. If the computed variable has not been declared, an implicit local variable is created as either a string or real number, depending on the type implied by the computation expression.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
EVALUATE varname = string_expression
The EVALUATE
command compiles and then evaluates a VisualPQL expression during program execution. The expression that is evaluated is re-compiled and re-evaluated every time that it is traversed that is an expensive process to perform at run time. This is typically used when a user is asked to type in some condition at execution time.
If the expression is a logical expression, the command returns a 0 (zero) or a 1 (one) depending on whether the expression is true or false. If the expression is a numeric calculation, the result is returned. If the expression is a string operation, the result is a string. The left hand side variable determines the type expected from the right hand side expression. If this variable is not explicitly declared, it is implicitly declared as real.
The following retrieval allows the user to specify a condition for retrieving records.
RETRIEVAL LOOP . COMPUTE EXPRESS = SREAD('Enter search condition (CR to quit)') . IF (LEN(TRIM(EXPRESS)) = 0) STOP . PROCESS CASES . PROCESS REC 1 . EVALUATE TRUE = EXPRESS . IF (TRUE) WRITE ID NAME TO CURRDATE . END REC . END CASE END LOOP END RETRIEVAL
The expression to the right of the equal sign is a string expression and therefore enclosed in quotes. The syntax of the command may also require a string expression enclosed in quotes. Use a mixture of single and double quote marks. Each matching pair denotes a string. For example:
EVALUATE X = 'NUMBR ("20")' + ' + 22'
This passes a valid VisualPQL expression NUMBR ("20") + 22
to the compiler that then produces the result 42 in X.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
GET VARS
transfers values of database or table variables to local variables. If the referenced local variables are not explicitly declared, this command implicitly declares them with all the schema definitions of the database or table variables, including Data Type, Value Labels, Missing Values, Valid Values and Ranges. The command is only allowed inside a case, record or table block. It takes three forms:
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 varlist (value_list) ...
Assigns constants to variables and array elements during compilation. PRESET
statements must precede the first executable command within a routine. PRESET
may also be used in an EXTERNAL VARIABLE BLOCK
. The preset values are the initial values when program execution begins. The syntax is identical to the SET
command. PRESET
happens once at compilation; SET
happens during execution whenever the SET
is encountered.
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, the value list is cycled until a value has been assigned to each variable. If the value list is longer than the variable list, the excess values are ignored.
MISSING, NMISSING
and SMISSING. NMISSING
assigns a numeric undefined value, SMISSING
assigns a string undefined value and MISSING
assigns the appropriate type of undefined value
depending on the type of the variable being set. If MISSING
is specified for an undeclared variable, it is implicitly declared as REAL
.
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
transfers values of local variables into database or table variables. This command must be used to update the values in table variables, whereas database variables are automatically updated by assignment within a record or case block. PUT VARS
takes three forms:
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 [ update_var = ] recode_var [ (value_list = recode_value) [...]] RECODE [ update_var_list = ] recode_var_list [ (value_list = recode_value) [...]]The
RECODE
command computes a value according to the specifications and assigns the value to a named variable. The computed value may be re-assigned to the original variable or assigned to a different variable leaving the original value unchanged.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.
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 varlist (value_list) ...
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.
BLANK MISSING NMISSING SMISSING
. BLANK
assigns blanks to a variable. This can be used to assign a blank missing value to a numeric variable. NMISSING
assigns a numeric undefined value, SMISSING
assigns a string undefined value and MISSING
assigns the appropriate type of undefined value depending on the type of the variable being set. If MISSING
is specified for an undeclared variable, it is implicitly declared as type REAL
.
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)
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 member[:V] [ NOSAVE ] [ REPLACE ] [PUBLIC ] [ VARMAP ] ... variable definitions ... ... END EXTERNAL VARIABLE BLOCKAn
EXTERNAL VARIABLE BLOCK
declares a set of variables and arrays that may be shared between routines. The external variable block contains variable declarations and definitions and the PRESET
command. No other commands are allowed in an external variable block. The block is ended with the END EXTERNAL VARIABLE BLOCK
command.
The external variable block is compiled separately (by running it) and is stored in its compiled form in the specified member. This member is given the :V (for Variables) suffix. This set of variables is made available to routines by specifying the INCLUDE EXTERNAL VARIABLE BLOCK
command within a routine.
The external variable block provides a common data area that can be used by a VisualPQL program and its subroutines as an alternative to passing values between subroutines with argument lists on the EXECUTE SUBROUTINE
command. External variables that are updated in one routine are available to all other routines that include the external variable block during VisualPQL execution.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
INCLUDE EXTERNAL VARIABLE BLOCK member[:V]
Includes the variables as local variables in the routine. Do not declare variables from the included block in the routine that includes the block.
member
| Names a member with the :V suffix that is a previously compiled and stored set of variable declarations. |
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
DEFINE PROCEDURE VARIABLES [ { INCLUDE | EXCLUDE } (varlist)] [ NOARRAYS | ARRAYS ] [ NOEXTERNALS | EXTERNALS [ (external_block_list) ] ] [ NOSIMPLE | SIMPLE ]Controls the variables that are copied to the Procedure Table with the
PERFORM PROCS
command. If this command is not used, by default all local simple variables from the main routine are passed to the Procedure Table; arrays and external variables are not. If arrays or external variables are needed for the procedures, this command must be used. The options on the command are:
![]() ![]() ![]() ![]() ![]() ![]() ![]() |