INTRODUCTION
This chapter describes generating and printing specialized reports, table manipulation, and other functions. It also covers how to partition data files.
9.1 GENERATING SPECIALIZED REPORTS
The normal report that Mentor creates is a summary of responses in a table format. If you would rather see how each respondent answered a specific question, then you can write a specification file that will create a specialized report. You can produce these case-by-case reports by running a procedure on the data and printing information about the selected records, much like the cleaning specs you created in Chapter 2. All you need to do is add a few commands to control the how the report is formatted. You can also add headers, footers and a summary at the end of a report.
You can create specialized reports which are lists of respondents who meet certain criteria, or lists of comments written on surveys. You can also create specialized reports to fill out existing forms your company has. These reports can include data accumulated across cases, calculated values, or text. Examples of each of these are provided below.
The specialized reports you create using Mentor can be simple or complex. In general, you will use the following commands in a spec file to create reports:
~SET AUTOMATIC_NEW_LINE
>PRINT_FILE <filename> <page size, other options>
~DEFINE
PROCEDURE={ <proc name>:
WHEN TOP/BOTTOM ''control printing at the top/bottom of a page
IF/THEN/ELSE/ENDIF ''controls program logic, printing,
or GOTO ''data modification, or other commands
EXECUTE_EOF ''executes commands at the end of a run (used
''to print text at bottom of report)
}
~INPUT <datafile>,<SELECT=specific cases or other options>
~EXECUTE PROCEDURE=<proc name>
~END
~SET AUTOMATIC_NEW_LINE
Says to go to a new line before printing a new case’s information; this is the default. Use ~SET -AUTOMATIC_NEW_LINE to stay on the same line across cases.
>PRINT_FILE (a meta command)
Specifies the name of the file to print to, its size and other options. (Refer to your UTILITIES manual under Appendix A: META COMMANDS).
~DEFINE PROCEDURE=name: commands }
Defines what commands to execute across cases.
~INPUT <datafile>,<options>
Says which data file to use, and which cases of that file, as well as other options. The SELECT= option may have any valid variable description to subset the records to use.
~EXECUTE PROCEDURE=<procedure name
Executes the procedure on the ~INPUT file, writing to the print file specified on
PRINT_FILE.
~END
Ends the program.
Most of the syntax is specified within the procedure defined with ~DEFINE PROCEDURE=name:. Use IF/ELSE/THEN/ENDIF blocks or GOTO commands to control whether to execute the printing commands within the procedure. Any other procedure command can be used in a printing procedure as well, such as data modification commands. Here are the major keywords used for report printing in a procedure:
PRINT_LINES # “text \codes text “&&
“more text” variables &
more variables
This is the main printing command. Various backslash (\) codes are specified within the text to control printing. PRINT_LINES formats the text line(s) and fills data text controls with the responses to the variables. Text printing controls are described below.
The number (#) in the command line allows you to specify which print file to print to if more than one is opened. A double ampersand (&&) after the text allows you to continue defining the text on the next line. An ampersand (&) at the end of a line allows you to continue a PRINT_LINES statement to the next syntax line. (NOTE: This command can be abbreviated to PRT)
SAY “text” variable “text” variable
This allows you to combine text and the responses to a variable, but has none of the special print controls. Use it for quick but simple listings.
The ERROR command has the following advantages over the SAY command:
-
You automatically get the CASE ID as part of the message
-
You get a summary count of the number of times the message occurred. This can be very helpful if you have conditional statements around it.
-
You can add the summary_only option to it and only get the summary count.
Use the PRINT_LINES command if you really want to control every aspect of what is printed out. This typically gets used if you are going to post process that output.
SKIP_TO +-#
This is used to skip forward some number of lines, or skip to a position some number of lines from the bottom of the page.
WHEN TOP #/END_WHEN
This is generally used for page headings. PRINT_LINES and other commands appearing between the WHEN TOP and END_WHEN will be executed at the top of each page.
If both WHEN TOP and WHEN BOTTOM are used together, WHEN BOTTOM must precede other regular print statements.
WHEN BOTTOM #/END_WHEN
Similar to WHEN TOP, this executes commands at the bottom of each page. It is commonly used to print a footer.
EXECUTE_ANY
Execute the commands following both for each data case and at the end of the file.
EXECUTE_DATA
Execute the commands following for each data case, but not at the end of the file. This is the default.
EXECUTE_EOF
Execute the following command only at the end of the run; for instance, if you wish to print a summary at the bottom of a report.
Specific syntax and options for these keywords can be found in Appendix B: TILDE COMMANDS . The >PRINT_FILE controls relevant to reporting are:
>PRINT_FILE filename <options>
This command names the file which will contain the finished report. The name will have a PRT extension unless preceded by a dollar sign ($) or the meta command >-CFMC_EXTENSION has been invoked, in which case the name you specify will be the actual name.
Options:
-FORM_FEED
Says not to include form feeds in the report. Do this if you are unsure about the number of lines per page your printer prints and wish to fill the pages as much as possible.
PAGE_LENGTH=#
Controls how many lines each page of the report will have. If unspecified, there will be 66 lines per page. The first three lines and the last three lines are reserved as default top and bottom margins respectively. Adjusting PAGE_LENGTH can prevent a new page in the middle of a respondent’s information. Example 2: A Conditional List of Client Information later in this section illustrates how to do this.
PAGE_WIDTH=#
Controls the page width. The default page width is 132.
LASER_CONTROL=<file name
Says that the printing will be controlled by the printer standards set up in the file \CFMC\CONTROL\<name> (DOS, UNIX), or <name>.CONTROL.CFMC (MPE). In the laser control file, you can specify an initial print string, what string to use for bold, underline, color, etc. and other print options. In your UTILITIES manual, refer to Appendix D: CFMC CONVENTIONS under Command Line Keywords, LISTFILE for more information.
The following options are only available on the MPE operating system:
>PRINT_FILE LP
Says to print directly to the line printer, labelled ‘LP’ in the devices list.
LASER_NUMBER=#
Says which laser printer to print directly to. The devices must be labelled LJET1, LJET2, etc.
COPIES=#
Says how many copies to print, if printing directly to a printer.
FORMS=“message”
Says to send the message to the printer, stop the printer, and wait until the operator replies to the message before continuing printing.
THE PRINT_LINES COMMAND
The PRINT_LINES command is the main command used to generate reports. Here is a short description of the text controls and variable descriptions. For more information refer to ~CLEANER PRINT_LINES in Appendix B: TILDE COMMANDS. The syntax for PRINT_LINES is:
PRINT_LINES "text and \text controls" variable1 variable2 & more variables
There are codes to control how much information is printed on a line, how the data is formatted, and codes to print specific characters. The most common codes are described below.
LINE PRINTING CONTROL CODES
Syntax: \#<code> Options: #repeat the operation X number of times (optional) G go to print position on line (must be to right of current position) N skip line(s) P starts a new page T generate actual TAB character(s) X skip print position(s)
EX: PRINT_LINES "\10GThis is my name:\N\10G\S" Name
This will print:
'This is my name: FRED SMITH '
CODES USED TO PRINT INFORMATION FROM THE DATA OR A VARIABLE
Syntax: \<modifiers><width><.numdecs><|maxitems><code>
Options:
<modifiers>:
< left-justify (default)
= center
> right-justify
# Indent second and subsequent lines by a # number of spaces.
<width> the number of print positions the item is to print in. The
default is the actual width of the item.
<.numdecs> the number of decimals a numeric item is to print with.
<|maxitems> the max number of items to include in a category.
<code>
\* Prints response codes from a category variable. Response
codes available from variables built with ~DEFINE PREPARE= or
from Survent specifications in the ~PREPARE module.
\L Prints exactly what is in the variable location.
\S Prints response text from a data location or category variable; trailing
blanks are dropped. Multiple responses are separated by commas.
\V# Prints the following (must be used with \S):
V1 Variable's name. [Q1]
V2 Variable's title text. [What's your age?]
V3 Variable's location. [1/5]
V4 Variable's question number. [0.10]
V5 User text. [This is a comment about the variable.]
PRINT_LINES "Variable's name: \V1s \V1s" Q1 Q2
This will print the Variable names for questions one and two.
PRINT_LINES "For: \V2 \NAnswer Was: \S" AGE AGE
This will print the title text and response for the variable AGE.
PRINT_LINES "Company Name: \20s, No. Employees: \>3S" &
[10.20] [numempl]
PRINT_LINES "Question One: \V1s \V2s" QN1 QN1
PRINT_LINES "First 3 Responses: \|3*" QN1
PRINT_LINES " Text: \|3S" QN1
Would print an entry for each case in the following format:
Company Name: ABC GADGET CO., No. Employees: 23
Question One: Cards, Number of Credit Cards Used
First 3 Responses: 01, 02, 05
Text: American Express, Mastercard, Visa Gold
CODES TO PRINT SPECIFIC CHARACTERS
Syntax: \<code>
Options:
\ prints a backslash
' prints an apostrophe
[ start ignoring ALL backslash codes except \] (Print the backslashes)
] stop ignoring ALL backslash codes
^ - followed by <hexdigit><hexdigit> to print special
characters from the ASCII character set (see Appendix G:
GRAPHICS CHARACTERS in your UTILITIES manual).
\^letter will print control characters.
EX: PRINT_LINES "This file is of type \'CfMC\', with name" &&
"\[\cfmc\data\myfile.tr\]"
will print: 'This file is of type 'CfMC', with name
\cfmc\data\myfile.tr'
To print a quote, use two quotes in a row.
EX: PRINT_LINES "Here is a ""quoted"" string."
Will print: Here is a “quoted” string.
VARIABLE REFERENCES
The variables in the variable list can be predefined items from a DB file, data location references, or items defined on the PRINT_LINES command itself, including mathematical expressions.
Predefined Variables
Predefined variables already have a data type. If they are categorical, the responses to the categories are printed; if they are string or text type, the text is printed. If they are numeric, the number is printed. All you need to do is reference them by name, but you can also use the name in data location type references. If the items are stored in a DB file, it must be opened with the >USE_DB meta command in order to access them.
EX: PRINT_LINES "\S" Gender Prints 'Male' or 'Female' PRINT_LINES "\S" Income Prints a number for income PRINT_LINES "\S" Whyliked Prints the text recorded under 'Whyliked' PRINT_LINES "\S" [(Name) 1.10$] Prints the first 10 characters of 'Name' PRINT_LINES "\S" Age * 5 Prints a number that is 'Age' times 5 PRINT_LINES "\S" [Cards$P] Prints all the punches in 'Cards'.
Data Location Variables
Print what is in the data depending on the data type. You cannot use multiple location data variables, but you may use a single location loop variable to print multiple items from the data.
EX: PRINT_LINES "\S" [10.5] Prints number in location 10 with length of 5 PRINT_LINES "\S" [10.5$] String in location 10 with length of 5 PRINT_LINES "\S" [10.5$P] Punches in [10.5] (i.e., "1/23/B/1.5XY/7") PRINT_LINES "\S" [10.1$T] Text pointer to text variable (for long text) PRINT_LINES "\S" [(8,2)10.2$] Print the eight strings starting at 10.2
For categorical variables, you can specify the text for each code:
PRINT_LINES "\S" [10.5#"Less than 500":0-500/501-5000/ && "> 5000":5001-99999]
This would print “Less than 500” if the data contained a 0-500 and ”> 5000” if greater than 5000. If the data was 501-5000, the text “501-5000” would print.
PRINT_LINES "\S: [10^Yes:1/No:2/"Don't know":3-Y,B]
This would print “Yes” if there was a 1 punch in column 10, “No” if a 2 punch, and “Don’t know” if anything else including no punch (B). Notice that you do not need quotes around the text unless there are special characters in the text such as spaces or apostrophes.
For calculations, any valid math calculation may be used, but you must follow it with a semi-colon before specifying the next variable to be printed.
PRINT_LINES "Factor: \5.2S Age: \S" & (Income*Age/NUMBER_OF_ITEMS(Cards))+[10.5]; Age
This would print the result of the calculation as a 5 wide, 2 decimal number then the age of the person for each case.
There are certain variables, called system variables or constants, that contain information that may be useful in your listings. Especially useful are:
DATE_TIME
Specifies the current date and time in the format:
"NOV 02, 2013 10:23 AM"
See ~CLEANER PRINT_LINES in Appendix B: TILDE COMMANDS for examples of how to get just the month, day, etc. from the date/time.
LINE_NUMBER
Keeps track of the current line number. Line numbers are seldom printed on reports, but can be used in conditional statements to print something at a specific place on the page. This is illustrated in example 2.
PAGE_NUMBER
Similar to LINE_NUMBER, this variable automatically keeps track of the current page number.
WHEN TOP PRINT_LINES "Community access report on date: \S, page \S" & DATE_TIME PAGE_NUMBER END_WHEN
See ~DEFINE VARIABLE= in Appendix B: TILDE COMMANDS for more information on defining variables to print.
Use the following guidelines for your specs:
-
Remember that print commands are executed sequentially from left to right and from top to bottom of the page.
-
Remember that Mentor automatically goes to a new line at the end of a case.
-
Use separate PRINT_LINE commands for headings and data. This makes it easier to make changes to your specs.
Example Reports
EXAMPLE 1: A LISTING OF DATA FOR CLEANING PURPOSES
This example illustrates a simple case-by-case data list. This example also illustrates some uses of the PRINT_LINES command with spacing control and different variable types. Refer toAppendix B: TILDE COMMANDS for a detailed explanation of PRINT_LINES syntax.
Example 1 SPECFILE
>USE_DB Sample ‘‘this file contains the predefined variables
‘‘referenced by name in this spec file
>PRINT_FILE Listdata
~DEFINE
PROCEDURE={List:
WHEN TOP
PRINT_LINES "ID\TNAME\TDAY\TSIBLINGS\TNUMSIBS\TOTHERS"
PRINT_LINES "----\T----\T---\T--------\T-------\T------"
END_WHEN
PRINT_LINES "\S\T\S\T\S\T\S\T\>2S\T\S" CASE_ID NAME [DAY$] [SIBLINGS$P] &
[NUMSIBS] [OTHERS$P]
} ‘‘end of procedure list
~INPUT Dataraw
~EXECUTE PROCEDURE=List
~END
Example 1 OUTPUT ID NAME DAY SIBLINGS NUMSIBS OTHERS --- ---- --- -------- ------- ------ 0001 MICKEY MON 1 12 1 0002 OLIVER TUE 0 2 0003 ZAZU WED 3 1 123 0004 THU 2 ? 8 0005 CICI 1 1 45 0006 DELI FRI ? 45 0007 CARMINA SAT 1 ? 8 0008 NORM SUN 1 7 B 0009 PEANUT MON 1 3 28 0010 CHI-CHI WEE 1 2 6
EXAMPLE 2: A CONDITIONAL LIST OF CLIENT INFORMATION
The next example is a list of a group of people who meet the condition – “all tours cancelled.” This example illustrates some of the keywords and design characteristics discussed before:
1) ”,PAGE_LENGTH=#” is used on the >PRINT_FILE command to insure that no respondent’s information will be divided between two pages. Blank lines must be included in all line counts. A page length of 66 (default) allows up to 60 lines to be printed (since there are three lines in the top and bottom margin).
For the following example, the heading takes four lines and the trailer takes one line. With 60 usable lines per page, this leaves 55 lines available for the data list (60-4-1=55). Each person’s data requires eight lines to list. Dividing the number of lines available for the data list, which is 55, by 8 yields 6.875. Since it is undesirable to divide one person’s data between two pages, each page can accommodate a maximum of six people’s data. So the actual data listing can be as big as 48 lines on a single page (6×8=48). This gives
# LINES OF GENERATED TEXT PER PAGE = 4 lines in heading + 48 lines in data list + 1 lines in trailer = 53,
and
PAGE_LENGTH = 3 lines in top margin + 3 lines in bottom margin + 53 lines of generated text per page = 59.
2) As in the previous example, specs for the heading were divided to resemble the final report. It was also practical to do this with the block that prints from the data (“SURVEY #”,”NAME”, etc.). Again, the results are more readable specs which are more likely to produce correct output in fewer runs.
3) LINE_NUMBER is used in an IF-THEN conditional statement to insure that the final trailer will appear at the end of the last page.
Example 2 SPECFILE
>USE_DB Tours
~INPUT data,SELECT=Tours(C) ‘‘Only use those whose tours were cancelled
>PRINT_FILE REPORT,PAGE_LENGTH=59
~DEFINE ‘‘Variable names were not assigned previously.
‘‘They are assigned here to make the specs more readable
‘‘and for ease of maintenance.
Survey: [1.3$] ‘‘Use dollar sign ($) to print data as a string instead
‘‘of a number
First: [12.12$]
Last: [24.15$]
Company: [39.30$]
Address: [69.30$]
City: [99.20$]
State: [209.3$]
Zip: [212.7$]
Phone: [126.10$]
Tour_name: [140.50$]
PROCEDURE={print_it:
WHEN TOP
PRINT_LINES " SAMPLE TOURISM BOARD TOUR STUDY"
PRINT_LINES " RESPONDENTS WHO HAD ANY OR ALL TOURS &&
CANCELLED"
PRINT_LINES " APR 2013"
PRINT_LINES "\N"
END_WHEN
WHEN BOTTOM
PRINT_LINES "\19X------ Page \S ------" PAGE_NUMBER
END_WHEN
PRINT_LINES " SURVEY #: \S" Survey
PRINT_LINES " NAME: \S \S" First Last
PRINT_LINES " COMPANY: \S" Company
PRINT_LINES " ADDRESS: \S" Address
PRINT_LINES " \20S \3S \7S" City State Zip
PRINT_LINES " TELEPHONE #: (\S) \S-S" [(Phone)1.3]
[(Phone)4.3] &
[(Phone)7.4)]
PRINT_LINES " TOUR NAME: \S" Tour_name
PRINT_LINES "\N" ''throws an extra line since it's the end of the case
EXECUTE_EOF ''goes to end of last page as determined by
LINE_NUMBER ''and prints footer
IF LINE_NUMBER < 56 THEN ''56=# lines of generated
''text/page +
SKIP_TO -1 ''# lines in top margin
PRINT_LINES "\19X------ Page \S ------" PAGE_NUMBER
ENDIF
} ''end of procedure Print_it
~EXECUTE PROCEDURE=Print_it
~END
Example 2 OUTPUT
SAMPLE TOURISM BOARD TOUR STUDY
RESPONDENTS WHO HAD ANY OR ALL TOURS CANCELLED
APR 2013
SURVEY #: 001
NAME:
COMPANY: JOE SMITH & SONS
ADDRESS: 1111 MAIN #111
DENVER NY 11111
TELEPHONE #: (888) 888-8888
TOUR NAME: TREASURES OF THE MIDWEST
SURVEY #: 002
NAME:
COMPANY: HARKER TOURS INC
ADDRESS:
TX
TELEPHONE #:
TOUR NAME: MIDWESTERN PANORAMA
SURVEY #: 003
NAME:
COMPANY: MIDAMERICAN TOURS
ADDRESS: BOX 222
FT COLLINS KS 22222
TELEPHONE #: (111) 111-1111
TOUR NAME: FLAT LANDS PLUS
SURVEY #: 004
NAME:
COMPANY: FLATLAND TOURS INC
ADDRESS: 333 MAIN ST
COLORADO SPRINGS VT 33333
TELEPHONE #: (222) 222-2222
TOUR NAME: FLATLANDS
SURVEY #: 005
NAME:
COMPANY: JED & WALLY FERRIS TOURS
ADDRESS: 444 GRAHAM RD
CENTRAL CITY GA 44444
TELEPHONE #: (333) 333-3333
TOUR NAME: KANSAS WHEAT FIELDS
SURVEY #: 006
NAME:
COMPANY: ELVIS BUS LINES
ADDRESS: PO BOX 5555
AURORA NB 55555
TELEPHONE #: (444) 444-4444
TOUR NAME: MIDWESTWARD HO
------ Page 1 ------
SAMPLE TOURISM BOARD TOUR STUDY
RESPONDENTS WHO HAD ANY OR ALL TOURS CANCELLED
APR 2013
SURVEY #: 007
NAME:
COMPANY: SHELAC TOURS, INC
ADDRESS: 6666 U.S. HWY 66, #6
SIOUX CITY OK 66666
TELEPHONE #: (555) 555-5555
TOUR NAME: MIDWEST & THE CORN FIELDS
SURVEY #: 008
NAME: JOHN SMITH
COMPANY: WHEAT CLUB TOURS
ADDRESS: PO BOX 777-7777 DEL NORTE DR
BOULDER LA 77777
TELEPHONE #: (666) 666-6666
TOUR NAME: NEBRASKA PLAINS
SURVEY #: 009
NAME: JOHN DOE
COMPANY: MIME TOURS, INC
ADDRESS: PO BOX 888-8888 ST ANDREWS DR
GREELY AK 88888
TELEPHONE #: (777) 843-1211
TOUR NAME: FLATLAND RAIL ADVENTURE
SURVEY #: 010
NAME: DRAKE WHITE
COMPANY: JIM MAGEE TOURS
ADDRESS: PO BOX 999-9999 W FORREST LN
SILVERTHORN NM 99999
TELEPHONE #: (888) 843-1211
TOUR NAME: MIDWEST INDIAN LANDS
------ Page 2 ------
9.1.1 Printing a Report Footer using WHEN BOTTOM
In order to print a footer or WHEN BOTTOM block at exactly the bottom of every page of your specialized report run, use the following method:
-
Turn off the Mentor’s new line default with ~SET -AUTOMATIC_NEW_LINE.
-
Count the exact number of lines that should appear at the bottom of the page. This number should include all blank lines, and should account for any \N commands.
-
Use the number calculated above on the WHEN BOTTOM # statement. To print three lines at the bottom of each page, the WHEN BOTTOM statement would be: WHEN BOTTOM 3.
-
The first line of the WHEN BOTTOM block should be “SKIP_TO -#”, where # is the same number specified on the WHEN BOTTOM statement. Once again, if there are three lines in the WHEN BOTTOM block, then the first statement of the WHEN BOTTOM block should be: SKIP_TO -3.
-
If you use both WHEN TOP and WHEN BOTTOM together, the WHEN BOTTOM statements must precede other regular print statements.
-
The WHEN BOTTOM block will not print on the bottom of the last page of the run unless you include instructions for EXECUTE_EOF. These instructions should be exactly those found inside of the WHEN BOTTOM block.
Here is an example of a three line WHEN BOTTOM block, including the EXECUTE_EOF instructions:
WHEN BOTTOM 3 SKIP_TO -3 PRINT_LINES "\NLine 1 of WHEN BOTTOM block\N" PRINT_LINES "Line 2 of WHEN BOTTOM block\N" PRINT_LINES "Line 3 of WHEN BOTTOM block" END_WHEN EXECUTE_EOF SKIP_TO -3 PRINT_LINES "\NLine 1 of WHEN BOTTOM block\N" PRINT_LINES "Line 2 of WHEN BOTTOM block\N" PRINT_LINES "Line 3 of WHEN BOTTOM block"
The example above will print on the last three lines of each page. There will be no blank lines between the last regular printed line, and the first line of the WHEN BOTTOM block.
If you want a blank line separating the last regular printed line from the first line of the WHEN BOTTOM block, include it inside the WHEN BOTTOM block. Be sure to count this line on your WHEN BOTTOM # and SKIP_TO -# settings.
Because the WHEN BOTTOM block and the EXECUTE_EOF instructions are exactly the same, a more efficient way to write the above example is to save the PRINT_LINES statements as an item in a DB file and then read in the db item (i.e., &&dbitem):
>FILE_TO_DB print_bot # SKIP_TO -3 PRINT_LINES "\NLine 1 of WHEN BOTTOM block\N" PRINT_LINES "Line 2 of WHEN BOTTOM block\N" PRINT_LINES "Line 3 of WHEN BOTTOM block" >END_OF_FILE WHEN BOTTOM 3 &&print_bot END_WHEN EXECUTE_EOF &&print_bot
Although both examples contain the same number of lines, by using the second method, you would only need to alter the PRINT_LINES instructions in one location if an alteration or correction was necessary.
9.2 TABLE MANIPULATION
Table manipulation is useful if you have numbers you want to combine from one or more tables, if you have information in a data file that you want to display on a table, or if you want to transfer numbers from a table to a data file. Tables can be manipulated in either the ~CLEANER block or a procedure can be written in the ~DEFINE block which is later processed in the ~EXECUTE block. (In the SPL software, this was called TPROG.)
Use the ~CLEANER block if you have all the tables you want to manipulate in a DB file and the names of the tables are readily available.Write a procedure to manipulate tables in a ~DEFINE block if you:
-
want to save the procedure in a DB file so you can run it again at a later time
-
need to use IF/THEN/ELSE/ENDIF statements to evaluate table cells
-
find it easier to maintain the list of tables and cells you want to use in a data file rather than in a >REPEAT.
A table is made up of columns and rows. In addition to columns and rows that are defined, there are system-generated Total and No Answer columns and rows.
The following terms may be used to describe columns and rows on a table:
T system-generated Total column or Total row NA system-generated No Answer column or No Answer row ALL all columns or all rows (including system-generated) LAST last column or last row on a table
Other examples of describing columns (or rows) are:
1 TO 5 columns 1,2,3,4, and 5 1,...,5 columns 1,2,3,4, and 5 1,3,...,5 columns 1 and 3 and 5 T TO 5 the system-generated Total and No Answer column and columns 1 to 5
The word “LAST” describes the last column or row on a table. “LAST” is helpful if you don’t know what the last row on a table is or if you want to use one statement to describe several tables of unequal size. Some examples of using “LAST” to describe a column (or row) are:
2 TO LAST column 2 to the last column in the table 1 TO LAST-1 column 1 to the second to the last column (last column minus 1)
You can divide a table into regions. A region is described in terms of column number(s) BY row number(s). A region can be as large as the whole table described as “ALL BY ALL” which means all columns by all rows, or a region can be as small as a cell described as “1 BY 1” which means the cell where column 1 and row 1 meet. The word “BY” separates the columns of a region from the rows of a region. In the ~CLEANER block the whole region description is enclosed in parentheses. A table region described as (1,3,5,7 BY 2,4,6,8) is a region made up of all the odd columns and all the even rows on a table.
Other examples of describing a region on a table are:
T001(1 TO 3 BY 3 TO 5) the region of table T001 which includes columns 1, 2
and 3 by rows 3, 4 and 5
T002(ALL BY 1,...,5) the region of table T002 which includes all columns
(including the system-generated Total and No Answer
column) by rows 1 to 5
T003(1 TO LAST BY 4) the region of table T003 which includes all columns first
to last (excluding the system-generated Total and No
Answer column) by row 4
If you are using anything other than simple column ranges (eg, 1 to last BY 1,3,…,last) then a semicolon may be required in place of BY. This is also true if a previously defined name is used in place of a range.
We will concentrate on ~CLEANER command table manipulation in this section and follow with writing a procedure in the next section. In the ~CLEANER block, the following table manipulation commands are available for creating a new table or changing an existing table:
CREATE_TABLES
Creates a new table in memory of specified dimensions and fills the cells of the table with a numeric value or MISSING. Can also create a new table in memory by copying or combining other tables.
MODIFY
Alters or combines tables. A table with 3 columns and 5 rows would be 5 by 7 when the System columns and rows are added. The following statement would create such a table and fill all the cells with zero.
CREATE_TABLES T001(=5, =7)=0
We can create a new table that was exactly the same as an existing table:
CREATE_TABLES T002 = T001
We can create a table that is the sum of one table added to another:
CREATE_TABLES T003 = T001 + T005
We can create a new table T004 that has the same number of columns as table T001 and four more rows than table T005 and fill the table with zeros:
CREATE_TABLES T004 (= NUMCOLS(T001), = NUMROWS(T005) + 4) = 0
The following arithmetic operations are available in both the ~CLEANER and ~DEFINE blocks:
= copies table cells != copies table and all table elements (banner, stub, etc.) += adds table cells -= subtracts table cells *= multiplies table cells /= divides table cells %= percentages table cells
We can change a cell in column 1 and row 1 to the number 5:
MODIFY T001(1 BY 1) = 5
We can change all the cells in column 3 to a 7:
MODIFY T001(3 BY ALL) = 7
We can add the number 3 to all the cells in row 5:
MODIFY T001(ALL BY 5) += 3
The following commands are available in both the ~CLEANER and ~DEFINE blocks for loading and unloading tables from memory and storing tables in a DB file.
LOAD_TABLES tablename Loads the table from a DB file into memory STORE_TABLES tablename Stores the table in a DB file that has write access UNLOAD_TABLES tablename Unloads the table from memory UNLOAD_TABLES * Unloads automatically loaded tables from memory UNLOAD_TABLES ! Unloads every table from memory
In the following example we will define, execute and store a table called T001 in a DB file. If you are not familiar with these steps, see Chapter 4: Basic Tables. We will then create a new table called T101 that is exactly the same as T001. We will multiply every cell in T101 by 2 and then print our two tables (T001 and T101).
>CREATE_DB TEST1 >PRINT_FILE TEST1 ~DEFINE COLDEF: TOTAL WITH [6^1//3] ROWDEF: [4^1/2] EDIT=EDIT_BASIC: COLUMN_WIDTH=10, STUB_WIDTH=0, -VERTICAL_PERCENT, -COLUMN_TNA, -ROW_TNA } BANNER=BAN1: | TOTAL A B C | ----- -- -- -- } STUB=STUB1: FIRST SECOND } ~SET AUTOMATIC_TABLES ~INPUT DATA ~EXECUTE COLUMN=COLDEF, ROW=ROWDEF RUN_CHAIN ~CLEANER CREATE_TABLES T101=T001 MODIFY T101 *= 2 STORE_TABLE T101 ~EXECUTE EDIT=EDIT_BASIC,BANNER=BAN1,STUB=STUB1 LOAD_TABLE=T001,PRINT_TABLE LOAD_TABLE=T101,PRINT_TABLE ~END
The resulting two tables from the above setup will look like this:
TABLE 001
BANNER: TOTAL WITH [6^1//3]
STUB: ROWDEF
TOTAL A B C
----- -- -- --
FIRST 6 2 1 3
SECOND 4 2 1 1
TABLE 101
TOTAL A B C
----- -- -- --
FIRST 12 4 2 6
SECOND 8 4 2 2
The following ~CLEANER commands are useful for examining tables that are either in a DB file or in memory:
PRINT_TABLES tablename Prints the table
SHOW_TABLES Lists the tables that are loaded in memory
SHOW tablename Shows the table and all the elements (banner, stub, etc.) that are stored with the table.
If we use the DB file that we just created we could examine table T001 in the following ways:
>USE_DB TEST1 ~CLEANER LOAD_TABLES T001 SHOW_TABLES PRINT_TABLES T001 SHOW T001 ~END
The results (which have been edited for readability) would be:
SHOW_TABLES
dump of all 1 tables in in_core chain
#1: test1^T001 (6 by 4)
end of dump
PRINT_TABLES T001
TABLE test1^T001
BANNER: TOTAL WITH [6^1//3]
STUB: ROWDEF
Total N/A TOTAL 6^1 6^2 6^3
Total 10 - 10 4 2 4
100.0% 100.0% 100.0% 100.0% 100.0%
N/A - - - - - -
4^1 6 - 6 2 1 3
60.0% 60.0% 50.0% 50.0% 75.0%
4^2 4 - 4 2 1 1
40.0% 40.0% 50.0% 50.0% 25.0%
SHOW T001
title= T001_t (size 2, user_said 0)
..column= COLDEF_c (size 874, user_said 1)
..banner= COLDEF_bn (size 1, user_said 0)
..row= ROWDEF_r (size 410, user_said 1)
.stub= ROWDEF_s (size 2, user_said 0)
table:
row (-1): 10 0 10 4 2 4
row (0): 0 0 0 0 0 0
row (1): 6 0 6 2 1 3
row (2): 4 0 4 2 1 1
You will notice in the previous table that the Total row is shown as ‘row (-1)’ and the No Answer row is shown as ‘row (0)’. The defined rows are numbered (1) and (2). The columns are described in the same manner. This means the first defined column will be referred to as column 1.
We will use the same table T001 that we just created and we will create a new table called T102 that is 6 columns wide (includes T and NA columns) and 4 rows long (includes T and NA rows) and every cell is set to missing. We will modify table T102 so that it will be the same table T001 with rows 1 and 2 switched. We will then print both tables.
>USE_DB TEST1,READ_WRITE,DUPLICATE=WARN >PRINT_FILE TEST2 ~CLEANER CREATE_TABLES T102(=6, =4)=MISSING MODIFY T102(ALL BY 1 TO 2)=T001(ALL BY 2 TO 1) STORE_TABLES T102 ~EXECUTE EDIT=EDIT_BASIC, BANNER=BAN1, STUB=STUB1 LOAD_TABLE=T001, PRINT_TABLE LOAD_TABLE=T102, PRINT_TABLE ~END
The results will look like:
TABLE 001
BANNER: TOTAL WITH [6^1//3]
STUB: ROWDEF
TOTAL A B C
----- -- -- --
FIRST 6 2 1 3
SECOND 4 2 1 1
TABLE 102
TOTAL A B C
----- -- -- --
FIRST 4 2 1 1
SECOND 6 2 1 3
Functions are also available that operate on table cells. Some examples are:
ABSOLUTE_VALUE Return the absolute value of a cell
MAKE_NUMBER Treat the result of another function as a number
NUMBERS_FROM_TABLE(#,tablename) Make a region with # numbers by copying numbers from the table that is specified
TABLE_FROM_NUMBERS(VALUES(#,#),2,1) Fill a table region with numbers (#)
SQUARE_ROOT Return the square root of a number
SUM Sum a range of table cells
X Return zero if the cell is missing
(See 9.3.2 Functions for more information on these and other functions.)
We can take the absolute value of a number on a table:
MODIFY T002(4 BY 4) = & ABSOLUTE(NUMBERS_FROM_TABLE(1,T001(3 BY 3)))
We can modify a table cell to equal a square root or an absolute value:
MODIFY T001(1 BY 1) = SQUARE_ROOT(14) MODIFY T001(1 BY 1) = ABSOLUTE(-6)
We can modify a table region to equal a square root or an absolute value:
MODIFY T001(ALL BY ALL) = MAKE_NUMBER(SQUARE_ROOT(14)) MODIFY T001(ALL BY ALL) = MAKE_NUMBER(ABSOLUTE(-6))
We can fill a table region with different values. The syntax for this function is:
TABLE_FROM_NUMBERS(VALUES (numbers desired starting in upper left-most corner, ending with lower right-most corner) number of columns, number of rows)
MODIFY T001(1 TO 2 BY 1 TO 2) = TABLE_FROM_NUMBERS(VALUES(2,3,4,5),2,2)
We can take the square root of a number on a table:
MODIFY T002(2 BY 2) = SQUARE_ROOT(NUMBERS_FROM_TABLE(1,T001(1 BY 1)))
or, for a two cell region:
MODIFY T002(2 BY 2 TO 3) = SQUARE_ROOT(NUMBERS_FROM_TABLE(2,T001(3 BY 3 & TO 4)))
or, for a four cell region:
MODIFY T002(2 TO 3 BY 2 TO 3) = & SQUARE_ROOT(NUMBERS_FROM_TABLE(4,T001(3 TO 4 BY 3 TO 4)))
or, to a region from a single cell:
MODIFY T002(2 TO 3 BY 2 TO 3) = & MAKE_NUMBER(SQUARE_ROOT(NUMBERS_FROM_TABLE(1,T001(3 BY 4))))
We can sum a range of table cells and place the result into another cell: (the number after the NUMBERS_FROM_TABLES function, 6 in this case, is the number of cells in the range to be summed)
MODIFY T001(7 BY 1) = & SUM(NUMBERS_FROM_TABLES(6,T001(1 TO 6 BY 1)))
We can make a procedure to return a zero if a table cell is missing:
PROCEDURE={PROC1:
IF X(MAKE_NUMBER(NUMBER_FROM_TABLE(1,T001(4 BY 4))))=0
THEN MODIFY T002(3 BY 3) = 100
ENDIF
}
Functions are also available to combine or evaluate tables.
FLIP Turns columns into rows and the reverse
JOIN_COLUMNS Joins tables side by side
JOIN_ROWS Joins tables by appending one to the other
NUMBER_OF_COLUMNS Returns how many columns are in a table
NUMBER_OF_ROWS Returns how many rows are in a table
REPLICATE Duplicates blocks of columns or rows on a table
We could flip an entire table:
MODIFY T001=FLIP(T001)
or just a region:
MODIFY T001(1 TO 3 BY 2 TO 4) = FLIP(T001(1 TO 3 BY 2 TO 4))
We could make a new table by joining two other complete tables column wise:
CREATE_TABLES T002=JOIN_COLUMNS(T001,T001)
or row wise:
CREATE_TABLES T002=JOIN_ROWS(T001,T001)
or by joining two other table regions column wise:
CREATE_TABLES T002=JOIN_COLUMNS(T001(1 TO 3 BY ALL),& T001(1 TO 3 BY ALL))
or row wise:
CREATE_TABLES T002=JOIN_ROWS(T001(ALL BY 1 TO 3),& T001(ALL BY 1 TO 3))
We could fill a table cell with the number of columns in a table:
MODIFY T002(1 BY 1) = NUMBER_OF_COLUMNS(T001)
or the number of rows:
MODIFY T002(1 BY 1) = NUMBER_OF_ROWS(T001)
We can replicate regions of one table onto another table. The syntax for this command is:
REPLICATE(tablename(region),column multiplier,row multiplier)
For this function the receiving region must be the appropriate size to receive the sending region as specified by the multiplying factors:
MODIFY T002(3 TO 4 BY 2 TO 3) = & REPLICATE(T001(1 BY 3 TO 4),2,1)
In a procedure created in the ~DEFINE block, table regions are described by $R. To create a new table with 3 detail columns and 5 detail rows we would say:
MODIFY [$R=T001 T TO 5 BY T TO 7]=0
In the following example we will write a procedure to create a new table, copy data from an old table to the new table and store the new table. A new table called NEW1 is created and it is exactly the same as the old table named GB009.
~DEFINE
PROCEDURE={PROC:
MODIFY [$R=?=NEW1]=GB009 ''? means get size of new table
''from old table
STORE_TABLES NEW1
}
Tables with the same name that are stored in different DB files can be combined and stored in a third DB file using the following statement:
MODIFY COMBINE^T010 = WAVE1^T010 + WAVE2^T010
Name^ in the above example means get the table from that DB file, which must have been opened with a previous DB command.
The following example uses table manipulation to create a table of means called TAB_MEANS from three tables, called TAB_QUALITY, TAB_CORRECT and TAB_DELIVERY that have been stored in a DB file called TABS1. We will define our banner and stub for our table of means first. Then our table manipulation step will occur in a ~CLEANER block where we will make row 1 of our table of means equal to the mean row (row 7) of the table TAB_QUALITY, row 2 of our table of means equal to the mean row (again row 7) of the table TAB_CORRECT, and row 3 of our table of means equal to the mean row (still row 7) of the table TAB_DELIVERY. We will then print the table TAB_MEANS.
>USE_DB TABS1,READ_WRITE,DUPLICATE=WARN
>PRINT_FILE PRTMEANS
~DEFINE
BANNER={BAN_AGE:
| TOTAL 18-24 25-34 35-44 45-54 55-64 65-99
| ----- ----- ----- ----- ----- ----- ----- }
STUB={STUB_MEANS:
QUALITY
CORRECT
DELIVERY }
EDIT={EDIT_MEANS:
COLUMN_WIDTH=7, STUB_WIDTH=15, -VERTICAL_PERCENT,
-COLUMN_TNA, -ROW_TNA, FREQUENCY_DECIMALS=2,
PRINT_ALPHA_TABLE_NAMES }
LINES={TITLE_MEANS: SUMMARY TABLES OF MEAN SCORES }
~CLEANER
CREATE TAB_MEANS(=9, =5) = 0
MODIFY TAB_MEANS(1 TO 7 BY 1) = TAB_QUALITY(1 TO 7 BY 7)
MODIFY TAB_MEANS(1 TO 7 BY 2) = TAB_CORRECT(1 TO 7 BY 7)
MODIFY TAB_MEANS(1 TO 7 BY 3) = TAB_DELIVERY(1 TO 7 BY 7)
STORE TAB_MEANS
~EXECUTE
BANNER=BAN_AGE,EDIT=EDIT_MEANS,STUB=STUB_MEANS
TITLE=TITLE_MEANS
LOAD_TABLE=TAB_MEANS,PRINT_TABLE
~END
This will produce output which looks like this:
TABLE TAB_MEANS
SUMMARY TABLES OF MEAN SCORES
TOTAL 18-24 25-34 35-44 45-54 55-64 65-99
----- ----- ----- ----- ----- ----- -----
QUALITY 3.39 3.25 3.26 3.65 3.43 3.56 3.50
CORRECT 3.61 3.42 3.47 3.85 4.00 3.61 3.57
DELIVERY 3.88 3.83 3.80 3.80 4.14 4.00 3.75
This table could then be utilized in a specialized report run to form an ASCII file for input into a spreadsheet or graphics program. In the following example $TAB is the label we will have on each line, $ROW is the row in our table of means (TAB_MEANS) that we will associate with each label, and $NEW is the report command to give us a new line in the output file which we will call GRAPH1. The first PRINT command prints the $TAB variable in the first 8 spaces of the line.
This first PRINT command is repeated three times, once for each line of our table of means. The second PRINT command says to skip a space then print a number from a cell on our table of means (TAB_MEANS) into a four column location with two decimal places. This second PRINT command is repeated seven times for each line, once for each cell of each line in our table of means. (For more information on print control, see 9.1 GENERATING SPECIALIZED REPORTS ).
>USE_DB TABS1 >PRINT_FILE GRAPH1 ~CLEANER >REPEAT $TAB=QUALITY,CORRECT,DELIVERY;& $ROW=1,2,3;& $NEW="","\N","\N" PRINT_LINES "$NEW\8S" "$TAB" >REPEAT $COL=1,...,7 PRINT_LINES "\1X\4.2S" NUMBERS_FROM_TABLE(1,TAB_MEANS($COL BY $ROW)) >END_REPEAT >END_REPEAT ~END
The output from this run will be named GRAPH1.PRT and will look like:
QUALITY 3.39 3.25 3.26 3.65 3.43 3.56 3.50
CORRECT 3.61 3.42 3.47 3.85 4.00 3.61 3.57
DELIVERY 3.88 3.83 3.80 3.80 4.14 4.00 3.75
Normally, tables saved into db files have only frequencies and variable statistics saved, not percents or any edit statistics. If our original tables, TAB_QUALITY, TAB_CORRECT and TAB_DELIVERY, had edit means as opposed to variable means, these mean rows would not normally be stored with the table into the db file TABS1. However, through the use of the ~SET command, SAVE_TABLE=“_X” (“_X” is a user defined tablename suffix), we can save the tables as printed (including all percent and edit statistics rows).
>CREATE_DB TABS1
>PRINT_FILE TABS1
~DEFINE
AGE: TOTAL WITH [40.2#18-24/25-34/35-44/45-54/55-64/65-99]
QUALITYP: [98^5//1/0]
CORRECTP: [99^5//1/0]
DELIVERYP: [100^5//1/0]
BANNER={BAN_AGE:
| TOTAL 18-24 25-34 35-44 45-54 55-64 65-99
| ----- ----- ----- ----- ----- ----- ----- }
STUB={STUB_RATINGP:
5- EXCELLENT
4- GOOD
3- FAIR
2- POOR
1- VERY POOR
NO OPINION
[PRINT_ROW=MEAN] EDIT MEAN
[PRINT_ROW=STD] EDIT STD }
EDIT={EDIT_PRTST: COLUMN_WIDTH=7,STUB_WIDTH=15,
VERTICAL_PERCENT=T,-COLUMN_TNA,
PRINT_ALPHA_TABLE_NAMES,
COLUMN_STATISTICS_VALUES=VALUES(5,4,3,2,1),
COLUMN_MEAN,COLUMN_STD,STATISTICS_DECIMALS=2
}
LINES={TITLE_QUALITY: RATING QUALITY OF FOOD }
LINES={TITLE_CORRECT: RATING CORRECT FOOD ITEMS
RECEIVED }
LINES={TITLE_DELIVERY: RATING PROMPT DELIVERY OF FOOD
}
~INPUT DATACLN
~SET SAVE_TABLE="_X"
~EXECUTE
COLUMN=AGE,BANNER=BAN_AGE,EDIT=EDIT_RATING
STUB=STUB_RATINGP
TITLE=TITLE_QUALITY,LOCAL_EDIT=EDIT_PRTST,ROW=QUALITYP
TABLE=TAB_QUALITYP
TITLE=TITLE_CORRECT,LOCAL_EDIT=EDIT_PRTST,ROW=CORRECTP
TABLE=TAB_CORRECTP
TITLE=TITLE_DELIVERY,LOCAL_EDIT=EDIT_PRTST,ROW=DELIVERYP
TABLE=TAB_DELIVRYP
~END
We can find out what saved printed table row corresponds to the mean row (or a percentage row if desired) by printing the saved printed tables using a small spec file such as:
>USE_DB TABS1
>PRINT_FILE PRTTABS
~DEFINE EDIT={FREQ_EDIT: FREQUENCY_ONLY,FREQUENCY_DECIMALS=2 }
~EXECUTE
EDIT = FREQ_EDIT
LOAD=TAB_QUALITYP_X,PRINT_TABLE
LOAD=TAB_CORRECTP_X,PRINT_TABLE
LOAD=TAB_DELIVRYP_X,PRINT_TABLE
~END
Once again, we will define our banner and stub for our table of means first. Then our table manipulation step will occur in a CLEAN block where we will make row 1 of our table of means equal to the mean row (row 17) of the table TAB_QUALITYP_X, row 2 of our table of means equal to the mean row (again row 17) of the table TAB_CORRECTP_X and row 3 of our table of means equal to the mean row (still row 17) of the table TAB_DELIVRYP_X. We will then print the table TAB_EMEANS.
>USE_DB TABS1,READ_WRITE,DUPLICATE=WARN
>PRINT_FILE PRTMEANS
~DEFINE
BANNER={BAN_AGE:
| TOTAL 18-24 25-34 35-44 45-54 55-64 65-99
| ----- ----- ----- ----- ----- ----- ----- }
STUB={STUB_MEANS:
QUALITY
CORRECT
DELIVERY }
EDIT={EDIT_MEANS: COLUMN_WIDTH=7, STUB_WIDTH=15,
-VERTICAL_PERCENT, -COLUMN_TNA, -ROW_TNA,
FREQUENCY_DECIMALS=2, PRINT_ALPHA_TABLE_NAMES }
LINES={TITLE_EMEANS: SUMMARY TABLE OF EDIT MEAN SCORES }
~CLEANER
CREATE TAB_EMEANS(=9, =5) = 0
MODIFY TAB_EMEANS(1 TO 7 BY 1) = &
TAB_QUALITYP_X(1 TO 7 BY 17)
MODIFY TAB_EMEANS(1 TO 7 BY 2) = &
TAB_CORRECTP_X(1 TO 7 BY 17)
MODIFY TAB_EMEANS(1 TO 7 BY 3) = &
TAB_DELIVRYP_X(1 TO 7 BY 17)
STORE TAB_EMEANS
~EXECUTE
BANNER=BAN_AGE, EDIT=EDIT_MEANS, STUB=STUB_MEANS
TITLE=TITLE_EMEANS
LOAD=TAB_EMEANS, PRINT_TABLE
~END
This table could then be utilized in the same formatted report run as described above to form an ASCII file for input into a spreadsheet or graphics program. You should be familiar with the following ~SET options that are useful in manipulating tables and are described in Appendix B: TILDE COMMANDS.
TABLE_DROP_MODE=# Specifies when tables or regions are unloaded from memory
TABLE_DROP_WARN=# Specifies how modified tables will react when unloaded from memory
TABLE_MISSING MODE=# Specifies how the program reacts when tables are not found
TABLE_MODIFY_MODE=# Specifies how the program reacts when tables do not fit together
TABLE_STORE_MODE=# Specifies when tables are stored in a DB file
In the following example, we will utilize the RANK_TABLE_COLUMNS function to add a row to the bottom of a table which shows the rank of the mean for each column in the banner. Since the RANK_TABLE_COLUMNS function is designed to rank row cells and show the ranks as a column, we must use the FLIP function to rank column cells.
1 ~INPUT DATACLN
2 >CREATE_DB TRANK
3 >PRINT_FILE TRANK
4
5 ~DEFINE
6 TABLE_SET={BANCNTRS:
7 EDIT=: COLUMN_WIDTH=6, STUB_WIDTH=20, -COLUMN_TNA
8 -ROW_NA, CALL_TABLE="", VERTICAL_PERCENT=T,
9 PERCENT_DECIMALS=0, STATISTICS_DECIMALS=2 }
10 BANNER=:
11 |
12 | STORE STORE STORE STORE STORE STORE STORE
13 | TOTAL 1 2 3 4 5 6 7
14 | ----- -- -- -- -- -- -- -- }
15 COLUMN=: NET([23.2#00,01,02/03,05/06,38,39,07,08/&
16 10,31,35/09,13/37,19,12,30/36,21,32,34]) }
17
18 TABLE_SET={Q4A:
19 LOCAL_EDIT=: EXTRA_STUBS_OK }
20 TITLE=: Q4. HOW CONSISTENT IS THE CONCEPT:\N
21 A. GOOD PLACE TO SHOP }
22 STUB=:
23 7-VERY CONSISTENT
24 6
25 5
26 4-NEITHER CONSISTENT NOR INCONSISTENT
27 3
28 2
29 1-VERY INCONSISTENT
30 [STATISTICS] MEAN
31 [FREQUENCY_ONLY] RANK MEANS }
32 ROW=: [35^7//1] $[MEAN] [35] }
33
34 ~EXECUTE
35 TABLE_SET=BANCNTRS
36 TABLE_SET=Q4A TAB=TQ4A
37
38 ~CLEANER
39 CREATE TEMP(+1,*)=FLIP(TQ4A)
40 MODIFY TEMP(LAST BY 2 TO 8) = &
41 RANK_TABLE_COLUMNS(HIGH,LOW_TIES,TEMP(LAST-1 BY 2 TO 8))
42 CREATE RANKQ4A=FLIP(TEMP)
43 STORE RANKQ4A
44
45 ~EXECUTE
46 TABLE_SET=BANCNTRS, STUB=Q4A_S, TITLE=Q4A_T
47 LOAD=RANKQ4A, PRINT_TABLE
48
49 ~END
To add the row of ranks, we create a temporary table in line 39 of the above spec file which is one row bigger in size than our existing table (TEMP(+1,*)) and into this temporary table we place the existing table, FLIPped. Then in line 40 of the above spec file we say to modify the last row (the RANK MEANS row) to reflect the rank of the cells in the next to last row (the MEAN row) but only for columns 2 to eight (we don’t want to rank the TOTAL column). Then the temporary file is FLIPped back to its proper orientation for final printing. When the ranking is accomplished in line 41, we say to rank high to low (HIGH) and show any ties in ranking with the lower rank number for all ties (LOW_TIES). (See 9.3.2 Functions, Table Related Functions for a more complete discussion of the RANK_TABLE_COLUMNS function.)
Here is the final table:
Q4. HOW CONSISTENT IS THE CONCEPT:
A. GOOD PLACE TO SHOP
STORE STORE STORE STORE STORE STORE STORE
TOTAL 1 2 3 4 5 6 7
----- -- -- -- -- -- -- --
Total 87 14 14 14 7 15 12 11
100% 100% 100% 100% 100% 100% 100% 100%
7-VERY CONSISTENT 4 - - 2 - 2 - -
5% 14% 13%
6 17 5 3 2 1 2 3 1
20% 36% 21% 14% 14% 13% 25% 9%
5 15 2 - 3 2 5 1 2
17% 14% 21% 29% 33% 8% 18%
4-NEITHER CONSISTENT 26 5 7 3 2 4 2 3
NOR INCONSISTENT 30% 36% 50% 21% 29% 27% 17% 27%
3 14 - 2 1 1 2 4 4
16% 14% 7% 14% 13% 33% 36%
2 5 - 1 2 - - 1 1
6% 7% 14% 8% 9%
1-VERY INCONSISTENT 6 2 1 1 1 - 1 -
7% 14% 7% 7% 14% 8%
MEAN 4.22 4.43 3.93 4.36 4.00 4.87 3.83 3.82
RANK MEANS - 2 5 3 4 1 6 7
9.3 USING Mentor’s SYSTEM CONSTANTS AND SPECIAL FUNCTIONS
Mentor has some special keywords to give you more power to describe and manipulate your data. These are divided into System constants and functions.
System constants allow you to get information on current System values or use System-defined items. Functions allow you to modify the meaning of variables. Both have special uses which you will need at times when cleaning data, running procedures, or building tables.
Functions and System constants can be used anywhere standard variables are used in Mentor. Remember that most of the names can be abbreviated. See Appendix X: ALLOWED ABBREVIATIONS for the allowed abbreviations.
NOTE: Cross-case operations (also called functions) are special features used for row and column creation, and are not related to the functions described here. For more information, see 5.2 Axis Commands/Cross-Case Operations and Appendix B: TILDE COMMANDS, ~DEFINE VARIABLE=.
9.3.1 System Constants
System constants can be accessed at any time, but cannot be modified. They contain information such as the current date and time, information about the data case being worked on, and other current system values.
The System constants can be classified as follows:
-
Variable constants refer to constants that are used with or in creation of variables or vectors when running procedures or building tables.
-
Case reading constants hold information about the data case being read.
-
System information constants contain other general information.
You can specify the name of certain system constants inside parentheses causing the data to come from the value of the system variable instead of the case.
EX: [(DATE_TIME) $] will say the date/time [(DATE_TIME) 1.6 $] will be the day and month only [(DATE_TIME) 13.2 # 00//23] will evaluate the hour [(DATE_TIME) 16.2] will be the minutes as a number [(DATE_TIME) 13.2 #"Morning":6-11/"Afternoon":12-16/"Eve":0-5,17-23] [(LINE_NUMBER) # 1-30] > 39
You can subset the following System constants:
-
CASE_ID
-
CASE_NUMBER
-
DATE_TIME
-
JULIAN_DATE
-
LINE_NUMBER
-
PAGE_NUMBER
-
TABLE_NAME
-
TEXT_AREA_STATUS
The following constants can be referenced for a specific data file (when you have multiple data files open) by using a caret (^) after the data file name:
ALTER_FLAG
CASE_ID
CASE_NUMBER
CASE_WRITTEN
DELETE_FLAG
EOF_DATA
ERROR_FLAG FIRST_CASE
TEXT_AREA_STATUS
For instance you could compare the value of CASE_ID in two different data files.
data1^CASE_ID EQ data2^CASE_ID
The constants LINE_NUMBER and PAGE_NUMBER can be used (in an IF block) to control printing when you have multiple print files open. See the meta command >PRINT_FILE in your Utilities manual for information on using multiple print files.
Here are the descriptions of the system constants. The examples given highlight cases where the System constant would be particularly useful. As with other keywords throughout the manual, many of these can be abbreviated. Those that can be shortened will show the allowed abbreviation in the syntax or example for that keyword or enclosed in parentheses at the end of the description.
VARIABLE CONSTANTS
CATEGORIES(#,#) Specifies which categories are turned ON using numbers; the numbers are the category numbers to be treated as being ON.
Syntax:CATS( #,#,#-#,#,...,# )
You can use ranges or ellipses to describe a CATEGORIES list.
CATS(1,3,...,23 Says every other category from 1 to 23 is ON. CATS(1-5,9) Says that categories 1 through 5 and 9 are ON.
This is used for particular data modifications. For instance, to add a category to a multi-category variable without affecting the other categories:
TRANSFER [10^1/3/5/7/9] += CATS(3)
This would add a 5 punch to column 10 since it is the third category.
CATEGORIES is used with the RANDOM_CATEGORY function to return a random category to a variable:
TRANSFER [10^1/3/5/7/9] = RANDOM_CATEGORY(CATS(1-5))
This would randomly assign one of the 5 categories (1, 3, 5, 7, or 9).
Another special use of category assignment is when reading a file under the control of a ~MAKE_READ_CONTROL variable, which speeds up processing when trying to read only particular cases:
~INPUT MYFILE ~MAKE_READ_CONTROL,STORE = [5.3#1//100] ~INPUT MYFILE,READ_CONTROL=STORE(1,5-10,81,90,92,...,98)
The items in parentheses are the categories from the store variable to be used when determining which cases to read in the file.
DUD A category variable with one category, that one being FALSE. Can be used to describe an empty cell in a table. Especially useful to have an empty cell in $[OVERLAY] or $[BREAK] tables where the number of categories must be the same for each section, but you want to combine different numbers of categories in the different sections.
ROW=Items_ate: [10^1//5] $[BREAK] [11^1//4] WITH DUD
The DUD category would line up with the 5 of column 10 and put a blank cell in the table for that category.
ERRORS Returns the number of errors (i.e., (ERROR # text)) for a particular run. This constant can be used to control execution in a specification file.
~GO_TO (done) ERRORS >0
FALSE A boolean which has the value of FALSE. Same as DUD.
MISSING A numeric category which has no value.
TOTAL A category variable, with one category being TRUE. This is useful when you want a Total category in a table, such as a Total column.
COLUMN=Banner: TOTAL WITH Sex WITH Age
TRUE A boolean which has the value of TRUE. Same as TOTAL.
VALUES(values) Returns a vector of a set of numbers, where the numbers are the value for a given category. If you have no number, just a comma, then that category does not have a value. VALUES(5.56,1.2-3) has 6 categories with values 5.56 in category 3, 1.2 in category 4, and -3 in category 6.
In table building, this is used with the SELECT_VALUE function to describe the values for weights or when using assigned values for mean or percentile calculations.
~DEFINE Weight1: SELECT([10^1//5],VALS(1.2,.85,1.275,0.654,.999) ~EXECUTE WEIGHT=Weight1 COLUMN=..., ROW=..., TABLE=... ...
This would assign 1.2 as a weight for those with a 1 in col 10, .85 for those with a 2, and so on.
On the EDIT statement, VALUES is used to describe the weights used for the rows or columns when calculating print time statistics.
~DEFINE EDIT=Domean: COLUMN_MEAN,COLUMN_STATISTICS_VALUES=VALS(,,10,20,...,90) }
This says to do a mean on the table skipping the first two rows (,,) then using the values 10, 20, etc. through 90 for the rows 2 through 10.
CASE READING CONSTANTS
ALTER_FLAG This is TRUE or FALSE depending on whether the case has ever been changed. When the case is written to a new file, this is set back to FALSE. You can see the current value of the ALTER_FLAG for the case you are on in the ~CLEANER block with the >STATUS command.
CASE_ID A string whose value is the case ID of the current case being read. This is usually used in procedures when trying to find a particular case, or just to print the case ID.
IF [5^1] SAY "CASE" CASEID "IS A MALE" ENDIF
This is not the data in the columns that are said to be the case ID, but the value the system has for the case ID. You can reload a new case ID with the PUT_ID command.
The CASE_ID value is displayed whenever you move to a new case in the ~CLEANER block. It is also used by the ~CLEANER NEXT command when looking for a particular case.
CASE_NUMBER This is the relative position of the data case in the file, not to be confused with CASE_ID, which is the assigned identifier for the case.
The case number is used by the ~CLEANER NEXT command using the syntax:
NEXT ###
For example, NEXT 256. This would find the 256th case in the file.
CASE_WRITTEN This returns TRUE if the case has been written to an output file during the current procedure. It is most useful to gather all cases that haven’t been written to a prior file into a separate file, i.e., write out the exceptions.
IF [5^1] THEN WRITE_CASE #1 ENDIF IF [5^2] THEN WRITE_CASE #2 ENDIF IF CASE_WRITTEN ELSE WRITE_CASE #3 ENDIF
CHECK_ERROR This is a boolean that returns either TRUE or FALSE for the last ~CLEANER CHECK statement executed. Use this constant in an IF or GOTO block to control the execution of a data cleaning procedure.
DELETE_FLAG This says whether the case has been marked as deleted. Note that to read previously deleted cases, the file must be opened with the USE_DELETED option on, i.e., ~INPUT oldfile,USE_DELETED. DELETE_FLAG is turned on when a case is written after the ~CLEANER ASSIGN_DELETE_FLAG or DROP commands have been issued. It can be turned off with the ~CLEANER UNDELETE command.
IF DELETE_FLAG PRINT_LINES "CASE \S WAS DELETED\N" CASE_ID ENDIF
When working in ~CLEANER interactively, the >STATUS command will also tell you whether the delete flag is set for the case you are looking at.
EOF_DATA Says whether you are at the end of the data file. Useful in procedures when you wish to do something after you are done processing the data, such as print summary information.
IF EOF_DATA PRINT_LINES "Total exceptions: \S\N" Exctot "Total errors: \S\N" Errtot ENDIF
ERROR_FLAG Tells whether the case has been marked as having an error. The error flag is turned on by the ~CLEANER commands CLEAN, CHECK, EDIT, and ERROR.
The error flag is removed after modifications are made to the case. The ~CLEANER FIND_FLAGGED command finds the next case with the error flag turned on.
The >STATUS command will tell you if the error flag is turned on for the case, or use the System constant ERROR_FLAG in an IF block.
IF ERROR_FLAG command(s) . . . ENDIF
FIRST_CASE Is TRUE whenever you are on the first case of the data file. This is useful in procedures when you want to do something special at the beginning of the run.
IF FIRST_CASE TRANSFER COUNTER[2/1.5]=1 ENDIF
This sets a counter to 1 at the beginning of a procedure.
LAST_CASE Is true when Mentor reaches the last case of the data file. This is useful in procedures when you want to do something at the end of a run.
TEXT_AREA_STATUS Checks the status of the text area and returns one of following numbers:
1 text area empty 2 text area is okay 3 some front pointers do not point to text because they are blank 4 text area not blank after last text answer 5 some front pointers do not point to text, and are not blank 6 some other problem with the text area, bad back pointers 10 not the input file specified 11 no data case in hand 12 no text location specified
TEXT_AREA_STATUS evaluates to 1-6 for the values 1-6 described above, to -1 for 10 or 11. It is a fatal error if value 12 is returned.
Use <studyname>^TEXT_AREA_STATUS or <studyname>!TEXT_AREA_STATUS to control which input file to check,when multiple files are open. You may also use this system variable inside brackets [ ], e.g., [(TEXT_AREA_STATUS) # -1/1/2//6]
Related commands are the ~ADJUST options INPUT_TEXT_LOCATION and OUTPUT_TEXT_LOCATION.
SYSTEM INFORMATION CONSTANTS
DATE_TIME Returns the current system date/time in the form:
DD MMM YYYY HH:MM (day month year hours minutes) 12 OCT 2013 15:23
This is useful to print the date on reports made with procedures.
EX: ~CLEANER PRINT "The current date and time are: \S" DATETIME
DATE_TIME_DIFF Here is an example of the syntax:
EX: datetimediff(string,string,datepart)
Where the strings are YYYYMMMMDDHHMMSS and, where the datepart can be:
1 for seconds
2 for minutes
3 for hours
4 for days
5 for months
6 for years
7 for weeks
EX:
datetimediff("20130504030201","20120504030201",4)
datetimediff([11.14$],[31.14$],4)
datetimediff(str1,str2,4)
OFFSETDATE Here is an example syntax:
EX: offsetdate(string,increment,datepart)
Where the strings are YYYYMMDDHHMMSS and where increment is how much to offset the datepart.
The datepart can be:
1 for seconds
2 for minutes
3 for hours
4 for days
5 for months
6 for years
7 for weeks
EX: say offsetdate("20130101010101",1,1)
JULIAN_DATE Returns the current system date and time in the form:
0 1 2 1234-6789-1234-6789- YYYYMMDDHHMMSSHHWJJJ (year/month/day/hour/minutes/seconds/hundredths/day of week/julian date) 20131112155145606316
Day of the week begins with Monday as day 1.
LINE_NUMBER Returns the current line number you are positioned on in the opened print file (see the meta command >PRINT_FILE). This can be used to print something on the same line of every page. See 9.1 GENERATING SPECIALIZED REPORTS for an example of this constant.
IF LINE_NUMBER = 60 THEN PRINT_LINES "Values for America Report, End of page \S" & PAGE_NUMBER ENDIF
MATH_VALUES A 17 category vector defined by the constant (seven of which have values as indicated):
VALUES(,1,0,,-1,,1.41,,,,3.14,,2.72,,,,1.62)
These are special values often used in mathematical calculations, i.e., the 11th value is Pi.
PAGE_NUMBER Returns the page number of the currently opened print file (see the >PRINT_FILE command). This is usually used to print the page number on the page during printing procedures. See 9.1 GENERATING SPECIALIZED REPORTS for an example of this constant.
WHEN TOP PRINT_LINES "Values for America Report - page \S \2N" PAGE_NUMBER END_WHEN
RANDOM_VALUE This returns a 14 digit random number between 0 and 1. It can then be used to make decisions about random samples or assigning random categories.
IF RANDOM_VALUE > .5 PRINT_LINES "This should get about half the cases\N" WRITE_CASE ENDIF
The RANDOM_VALUE system variable can also be used to assign a number in a range of 0 to some high value, then making decisions based on the number returned. This example writes out a 10% random sample to a new data file.
~CLEANER TRANSFER Pick[2/35.3] = RANDOM_VALUE * 100 IF Pick <= 10 THEN WRITE_CASE ENDIF
TABLE_NAME The name of the table currently loaded.
9.3.2 Functions
Functions are used to get special values or translate one type of element to another. They can be divided into several groups. You can use these functions interchangeably wherever functions can be used. The function types are:
-
Arithmetic functions for mathematical computation
-
Vector functions for table axis creation
-
Number returning functions returning special numbers
-
Logical functions
-
Table related functions
-
Integer functions
-
String functions
The general syntax for all functions is:
Syntax: function name( argument1, argument2, …,argumentn )
The function name must be immediately before the opening parenthesis. Items inside the parentheses can have spaces separating them from the parentheses as well as each other. A comma is required between arguments, and a closing parenthesis must follow the arguments. Most functions have only one argument.
Here are the functions within each group. The examples include likely uses for that function. Note that items within functions may be defined earlier, then referenced by name. Also, a vector is a complex variable description; you may also use simple data variables or numbers wherever a vector is mentioned. Allowed abbreviation of function names are shown in the syntax or example, or indicated in parentheses at the end of the description.
ARITHMETIC FUNCTIONS
ABSOLUTE_VALUE( vector ) Returns the absolute (positive) values of the numbers in the vector. See 9.2 TABLE MANIPULATION for an example of this function.
TRANSFER [10.2] = ABSOLUTE_VALUE(Age-20000)
AVERAGE( vector1, vector2, vectorn, region ) Returns the average of all of the numbers present. The average is the sum of the values divided by the number of values present. AVERAGE can also operate on a table region (see SUM for an example).
$[MEAN] AVERAGE([10,...,15])
On a table, this returns the mean of the average of columns 10 to 15.
EXPONENT( vector ) Returns the exponents of the numbers in the vector.
TRANSFER [20.5,25,30*F3]=EXPONENT(1 WITH 2 WITH 3)
This returns the exponents of 1, 2, and 3 to the specified columns with three decimal places of significance.
LOGARITHM( vector ) Returns the natural logs (e sub n) of the numbers in the vector.
TRANSFER [20.5,25,30*F3]=LOGARITHM(1 WITH 2 WITH 3)
This returns the logarithm of 1, 2, and 3 to the specified columns with three decimal places of significance.
MAKE_NUMBER(function) Treats the result of another function as a number.
MAKE_NUMBER(SQUARE_ROOT(25) )**
See 9.2 TABLE MANIPULATION for an example of this function.
SQUARE_ROOT( vector ) Returns the square roots of the numbers in the vector.
TRANSFER [35.5*F4]= SQUARE_ROOT(Age)
This returns the square root of the variable AGE. See 9.2 TABLE MANIPULATION for an example of this function.
STANDARD_DEVIATION( vector1, vector2, vectorn ) Performs a standard deviation on a list of numeric fields.
STD( [1.2], [3.2], [5.2] )
SUM( vector1, vector2, vectorn, region ) Returns the sum of all of the numbers and categories present.
Tab1: SUM([2/1,...,2/5]) WITH [2/1,...,2/5]
This returns the sum of the five fields followed by each of the fields. SUM can also operate on a table region.
SAY SUM(tab1(T to last by 1) )
X( Numeric variable or Math equation ) Returns a 0 if the numeric variable or equation is blank or is not a valid number. This is usually used to make sure that a good value gets used even if part of the equation is missing. By default, if an item is missing in an equation, the equation returns MISSING.
TRANSFER [45.2] = X([50.2]) + 5
This returns the sum of the data in location 50-51 plus 5. If columns 50-51 are not a valid number, a 0 is used, and 5 is the result. See 9.2 TABLE MANIPULATION for an example of this function.
VECTOR FUNCTIONS
BALANCE( vector )
Returns the vector, followed by the No Answer category for the vector. This is the same as CATEGORY_FUNCTION(-2,vector), only easier to say.
Row23: BALANCE([10.2#1//20])
This returns 21 categories, the twenty numeric categories plus one category containing anyone not included in the other categories.
BALANCE must be used with a category variable and not a boolean variable.
EX: a: BALANCE(([1^1] BY [1^2])) b: BALANCE(MAKE_CATEGORIES([1^1/2] AND [1^2])) c: BALANCE(MAKE_CATEGORIES([1^1] AND [1^2])) d: BALANCE(CATS(1,2))
CATEGORY_FUNCTION( -#, vector ) Returns the vector with different combinations of Total, No Answer, and Net of the vector added before and/or after the vector.
-32 = T before -16 = NA before -8 = NET before -4 = T after -2 = NA after -1 = NET after
Add together elements to get combinations; i.e., if you want the Total and No Answer before and Net after the vector, use:
(-32) + (-16) + (-1) = -49 Row19: CATEGORY_FUNCTION(-49,[10^1] WITH [11^1//5])
This returns the Total and No Answer, followed by the six categories of the vector, followed by the Net of the vector.
NET( vector ) Returns a vector which is the Net of the vector followed by the original vector. This is the same as CATEGORY_FUNCTION(-8,vector), but easier to say.
NET(Age WITH Income) This returns the Net of AGE WITH INCOME followed by AGE, followed by INCOME.
NUMBERS_FROM_TABLE( maximum # of cells to get, table description ) Makes a vector of # numbers and assigns the numbers from a table region there.
A standard table region description is:
TABLENAME(<first col> TO <lastcol> BY <first row> TO <last row>) TRANSFER [10.2,12,...,24] = & NUMBERS_FROM_TABLE(8,T001(1 TO 4 BY 1 TO 2))
This moves the first 4 columns and 2 rows of table T001 into the data in 2 column wide fields from 10 through 24. The first cell will be in 10-11, the second column, first row will be in 12-13, and so on. See 9.2 TABLE MANIPULATION for an example of this function.
RANDOM_CATEGORY( category vector ) Randomly picks one of a set of categories that are TRUE.
This is used to pick one of a set of categories that have been previously chosen. A typical example is picking one of two codes that have been chosen to rate an item; i.e., the respondent picked 3 and 4 on a 5 point rating scale, and you wish to just pick one of the codes and use it for analysis.
To pick a random category among categories chosen, and put it back in the same location:
TRANSFER [12^1//10] = RANDOM_CATEGORY([12^1//10])
RANDOM_CATEGORY can also be used to just pick a number out of a set of numbers. Do this by using numeric categories.
To get a random integer returned between 1 and 100:
TRANSFER [10.3#1//100] = RANDOM_CATEGORY(CATS(1,...,100))
See the RANDOM System constant to get a random number between 0 and 1. This could be multiplied by any number to get a random number in the range from 0 to that number.
NUMBER RETURNING FUNCTIONS
FIRST_SUBSCRIPT( category vector ) Returns the subscript (or number) of the first category seen in the vector. For example, if the third and fifth values of the category vector are present, FIRST_SUBSCRIPT returns a 3.
FIRST_VALUE( vector ) Returns the first numeric value present in the vector. Note that the vector can include categorical data, in which case a 1 will be returned if the category is the first thing present.
This would be used if you allowed responses in different locations, but only wanted to tabulate the first location answered.
Tab1: FIRST_VALUE([10,...,20])
This would return the first number seen in columns 10 through 20.
FSIG( df1,df2,f) Returns the level of significance for a one-tailed test base on df1, the degrees of freedom in the numerator, and df2, the degrees of freedom in the denominator, and f, the f-ratio. See TSIG.
LAST_SUBSCRIPT( categorical vector ) Like FIRST_SUBSCRIPT, except it returns the subscript (or number) of the last category seen in the vector.
LAST_VALUE( vector ) Like FIRST_VALUE, except it returns the last value present in the vector.
MAXIMUM_VALUE( vector1, vector2, vectorn, region ) Returns the highest number present in the vectors. MAXIMUM_VALUE can also operate on a table region (see SUM for an example).
MAXIMUM_VALUE_SUBSCRIPT( vector1, vector2, vectorn ) Returns the subscript of the item with the highest value in the vector. If the second item is the highest, it returns a 2.
This is useful when checking against a set of values, then using the highest value in later calculations.
IF MAXIMUM_VALUE_SUBSCRIPT([10,...,15]) = 1 THEN
TRANSFER [20/5] = [1] * AGE
ELSE
IF MAXIMUM_VALUE_SUBSCRIPT([10,...,15]) = 2
THEN
TRANSFER [20/5] = [2] * AGE
...
ENDIF
ENDIF
This finds the high value to use for the ~CLEANER TRANSFER calculation.
MINIMUM_VALUE( vector1, vector2, vectorn, region ) Returns the smallest number present in the list. MINIMUM_VALUE can also operate on a table region (see SUM for an example).
MINIMUM_VALUE_SUBSCRIPT( vector1, vector2, vectorn ) Returns the subscript of the item with the lowest number seen in the vector. If the third value is 12, and the fifth is 34, it returns a 3.
NUMBER_OF_ITEMS( vector ) Returns the total number of categories present. If there is a data location in the vector ([col.wid]), this returns the number of binary punches in the columns, in addition to the other categories in the vector. The NUMBER_OF_ITEMS function can be used to count ASCII responses as well as punches.
NUMITEMS creates the equivalent of the Total Responses in a category set, which is often useful as a percentage base in tables. A zero is returned if there are no responses, never MISSING.
Q12open_end: NUMBER_OF_ITEMS([10.3]) WITH [10.3^1//36]
This produces the sum of the punches in columns 10 to 12, then each of the punches as a separate category.
Q13open: NUMBER_OF_ITEMS([13.2^1//5/18//24]) WITH [13.2^1//5/18//24]
This produces the sum of the categories described in columns 13 to 14, then the separate categories.
To get a table of the number of responses to a set of questions, you could do the following:
In a procedure ... TRANSFER [5/78.2] = NUMBER_OF_ITEMS([2/10.3,2/13,2/16^1//27]) In a table definition ... Q23Responses: [5/78.2#0//12/13-99] $[MEAN,STD] [5/78.2]
This would produce a table of the number of responses to question in columns 2/10 to 2/18, along with the mean number and standard deviation of responses.
RANDOM_SEQUENCE(vector variable(,seed)) Used to obtain specific random values, it generates two category vectors: the next random number and the resulting seed for the next call to the program for a random number.
Its most common use is maintaining a user-controlled random chain with a specification such as
~CLEANER MODIFY var1[1/11.10,1/21.10]=RANDOM_SEQUENCE(var2)
where the user can supply the first seed, or if var2(2)=MISSING, then the program will generate a random start or the user can supply one with the meta command >RANDOM_SEED=.
SELECT_VALUE( vector, <vector or VALUES( #,…,# )> ) Returns a number which is the number in the second vector or values list corresponding to the category seen in the first vector. Can have only one active category, or an ERROR is produced. You must have the same number of categories or values on both sides of the comma.
SELECT_VALUE is usually used to assign weights for data or statistical calculations (such as mean, standard deviation, percentile, etc.).
~DEFINE Weight1: SELECT_VALUE([10^1//5],VALUES(1.2,.85,1.275,0.654,.999) ) ~EXECUTE WEIGHT=Weight1 COLUMN=... ,ROW=... ,TABLE=...
This would assign 1.2 as a weight for those with a 1 in col 10, .85 for those with a 2, etc.
STRING_LENGTH( <“string”, $, $T, or $P string variable> ) Returns the number of characters in the string variable, starting at the first position and going to the last non-blank character.
This is often used when listing open-end responses:
IF STRING_LENGTH(a[2/23.20$]) > 5 SAY "Question had response" A ENDIF
This checks to see the length of the response in columns 2/23 to 2/42, then prints the response if the length is greater than 5.
SUBSCRIPT( vector ) Returns the subscript (position of the category) of the category found. Used to assign ordered values. If more than one value is found, returns MISSING.
SUBSCRIPT is often used to assign values when you want continuous numeric categories; for instance, changing a 0 punch to a 10 for purposes of statistical calculation.
$[MEAN] SUBSCRIPT([2/13^1//0])
If column 2/13 was a 0, this would return a 10. If there was more than one category present, the value would be MISSING, and would not be used in the MEAN calculation.
See also FIRST_SUBSCRIPT and LAST_SUBSCRIPT.
TSIG( df,t) Returns the level of significance for a two-tailed test base on df, the degrees of freedom and t, the calculated t-value. TSIG(df,t) = FSIG(1,df,t*t). See FSIG.
VARIABLE_EXISTS( string variable ) Evaluates the string variable as a variable name and checks for its existence in any open data base (DB) files. Returns a number indicating the type of DB entry if the variable named is found in an open DB file, otherwise it returns MISSING. The number returned is the same number found for that variable type in the >LIST_DB_CONTENTS meta command.
The string variable can be a name in quotes (i.e., “thisvar”), or a string in the data (i.e., [20.5$]).
IF VARIABLE_EXISTS("myvar") TRANSFER Myvar = 10 ENDIF
This would put the number 10 in the variable MYVAR if the variable is in an open DB file.
WORD_MATCHES( string variable, string variable ) Returns the number of ASCII character matches in the string variable to the string variable. This is a word search; the string must not be contained within another string. This is a non-case-sensitive search.
The comparison is word oriented, thus WORD_MATCHES(“b”, “abc”) will return zero (no matches), but WORD_MATCHES(“b”, “a b c”) will return one, as will WORD_MATCHES(“B”, “a_b_c”).
The string variable can be a specific string in quotes (“string”), or an ASCII data string ([2/23.10$]), a punch string ([2/23.10$P]), or a text string (probably from Survent) ([4/10.1$T]).
This is useful when recoding open-ended responses to codes. You can search for meaningful keywords to help put the text into categories.
IF WORD_MATCHES(food[20.20$],"candy") >= 1 THEN SAY "CASE " CASE_ID "HAS " food "Maybe code as '1'?" ENDIF
This looks for all occurrences of the word “candy” in data locations 20 through 39, and, if one or more, lists the response to possibly be recoded as a 1 in the response list. To find all words starting with a string, see the WORD_STARTS function.
WORD_STARTS( string variable 1, string variable 2 ) Returns the number of times the second string starts a word in the first string. This is a non-case-sensitive search.
The string variable can be a specific string in quotes (“string”), or an ASCII data string ([2/23.10$]), a punch string ([2/23.10$P]), or a text string (probably from Survent) ([4/10.1$T]).
WORD_STARTS([20.1$T],”HE”) This returns the number of times “HE” starts a word in the text variable with a pointer in column 20. If it contained “hello there Henry”, STARTS would return the value 2.
LOGICAL FUNCTIONS
CASCADE( vector ) Returns TRUE if all categories are true starting at the first category until no more are true. This is useful when checking a set of items where you are supposed to mark the top ones in a larger list, without forcing the user to mark all the items.
IF CASCADE([2/1.2,2/3,...,2/19*F#1//10]) ELSE ERROR "Ranking should be continuous from 1 on" ENDIF
If there was just a 1 ranking, this would be OK. If there were no ranking at all, it would be OK. If there was a 1, 2, 4, and 5 ranking , this would not be OK. If there were a 1, 2, and 3 ranking, it would be OK.
COMPLETE( vector ) This returns TRUE if all categories in the vector are true. This is useful when checking a set of items that are supposed to be ranked, say from 1 to 10, with no skips.
IF COMPLETE([2/1.2,2/3,...,2/19*F#1//10]) ELSE ERROR "Ranking should be continuous from 1 to 10" ENDIF
If there was just a 1 ranking, this would not be OK. If there were no ranking at all, it would not be OK. If there was a 1, 2, 4, and 5 ranking, this would not be OK. It is only OK if it were ranked 1 through 10.
COMPLETE is also useful to make sure that all items in a set are present.
MAKE_BOOLEAN( vector ) Returns TRUE if any category in the vector is true. This is useful to get a net category from a complex vector.
Mytable: MAKE_BOOLEAN([5^1] WITH [8.2#110]) WITH & [5^1] WITH [8.2#110]
This returns a 12 category vector; the first category is anyone having a 1 punch in column 5 or a number 1 to 10 in columns 8-9, then each of the separate categories is laid out.
MAKE_BOOLEAN is particularly useful when you have previously defined an item, and now just want a net of the answers in the item.
table23: MAKE_BOOLEAN(likeit) with likeit
This would provide the net of LIKEIT followed by LIKEIT.
MAKE_BOOLEAN is also useful when you need to collapse any vector to a single category for other purposes.
NOTE: IF statements always collapse all categories into one category like MAKE_BOOLEAN.
TABLE RELATED FUNCTIONS
FLIP( table region ) This flips the columns and rows in a region of a table.
CREATE Tab001=FLIP(t001)
This would create the new table TAB001 which is a flipped version of the original table T001.
JOIN_COLUMNS( table region 1, table region 2 ) This extends a table by columns. It is used to put two tables or table regions side-by-side on a page; i.e., first wave vs. second wave numbers. The tables must have the same number of rows to be joined.
CREATE t301 = JOIN_COLUMNS(t001,t201) This would make a new table T301 which would combine tables T001 and T201 such that the columns of the tables would be side-by-side. Table T001’s columns would be first, followed by table T201’s columns.
JOIN_ROWS( table region 1, table region 2 ) This extends tables by rows. It is used to put two tables or table regions one above the other on a page; i.e., to combine two product lists. The tables must have the same number of columns to be joined.
CREATE t301 = JOIN_ROWS(t001,t201)
This would make a new table T301 which would combine tables T001 and T201 such that the rows of the tables would be combined. Table T001’s rows would be first, followed by table T201’s rows.
LOADED( table region ) This returns TRUE or FALSE depending on whether the table region is currently loaded in core.
NUMBER_OF_COLUMNS( table region ) This counts the number of columns in a region. It is used to calculate the number of columns to use in future tables.
For instance, if you want to combine two tables you can say:
CREATE t003(=NUMBER_OF_COLUMNS(t001) + NUMBER_OF_COLUMNS(t002),=5 ) = 0
You can create tables with twice as many columns, etc.
NUMBER_OF_ROWS( table region ) This counts the number of rows in a region. It is used to calculate the number of rows to use in future tables. For instance, if you want to combine two tables you can say:
CREATE t003(=10,=NUMBER_OF_ROWS(t001) + NUMBER_OF_ROWS(t002)) = 0
You can create tables with twice as many rows, etc.
RANK_TABLE_COLUMNS(HIGH/LOW,LOW_TIES/MEDIAN_TIES,<region>) Reads a region of a table and returns the rank value of each item in a column.
Options: HIGH rank high to low. LOW rank low to high. LOW_TIES return the low rank value where rank value is the same (tied) for all ties. MEDIAN_TIE return the midpoint where rank value is the same (tied) for all ties. <region> defines the region of the table to rank
If columns and rows are included, it ranks down the columns, one column at a time.
You cannot rank across rows; if you want to do this, set things up in columns, then flip the table. See section 9.2 TABLE MANIPULATION for an example table.
EX: MODIFY T001=RANK_TABLE_COLUMNS(HIGH, MEDIAN_TIES,T000) MODIFY T007(5 TO 6 BY 6 TO9)=RANK_TABLE_COLUMNS(HIGH, MEDIAN_TIES,& T000(5 TO 6 BY 1 TO 4)) REPLICATE( table region, # of col reps, # of row reps )
This is used to have a smaller number of columns or rows act on a larger number of columns or rows (must be evenly divisible).
TRANSFER T001(1 TO 10 BY 1) += REPLICATE(t002(1 BY 1),10,1)
This would add the cell from column 1, row 1 of table T002 to the corresponding cells of table T001 (columns 1 through 10, row 1).
See the ~CLEANER FILL command; it also does replicates. If you are using only numbers, then the table automatically fills in a replicated manner.
TRANSFER T001(1 by all) = 5
This would put the number 5 in all rows of column 1 of table T001.
TABLE_FROM_NUMBERS( vector, # columns, # rows ) This takes data from the vector and fills a region of a table.
TRANSFER T001(1 TO 5 BY 1) = TABLE_FROM_NUMBERS([5.2,7,...,13],5,1)
This takes data from columns 5-6, 7-8, etc. and moves it into columns 1 through 5, row 1of table T001. See 9.2 TABLE MANIPULATION for an example of this function.
INTEGER FUNCTIONS
FILE_COMPARE(“file1”,”file2”, n, filter options, # lines until resynch) Compares two files, and produces an error for every instance where they are not identical. File1 is the master file and file2 is the compare file. You can stop the comparison after n number of errors. This function returns the number of errors found between the two files or one of the following.
-
If one of the files can’t be opened (for example, if one of the files does not exist)
-
If the resulting line would be too long. For example, if you are comparing two files with a PAGE_WIDTH=132 and writing the FILE_COMPARE results to a printfile, the printfile must be 18 columns wider or PAGE_WIDTH=150. If it’s not at least that wide, FILE_COMPARE will return a -2 and a program WARNING message.
-
If one of the file names is a bad file name
Options:
n Stop after n errors. 0 means don't print any errors, and return a 1 if
there are any errors. -1 means print all the errors that occur and
return the number of errors. Otherwise, the function prints up to the
number of errors you specify and returns the number of errors.
Filter Options: Will filter out items as follows:
0 no filter
1 filter out blank lines
2 filter out text marked between /* and */
4 filter out temporary file names (i.e. TE001001)
8 trim trailing blanks off lines before compare
You can combine filters by adding them together:
3 1 and 2
5 1 and 4
9 1 and 8
6 2 and 4
10 2 and 8
7 1, 2, and 4
15 all filters (1, 2, 4 and 8)
Number (#) of lines until files are re-synchronized controls how many lines are read, when the files are out of synchronization, before attempting another comparison. The default is 5, used if a number less than 0 is specified. If you set this to “0” then no attempt is made to re-synchronize. You can set this to any other positive number to specify the number of lines to be read before a resynchronization is attempted. If you only want to confirm that two files are exactly the same, set this parameter to 0. If you are comparing two print files in which one file could have whole pages that the other did not, use a value of at least 66 (the number of lines on a page).
Line 1 is read from the master file and compared to line 1 in the compare file. If there is a match then line 2 in both files is read. If the two lines do not match, FILE_COMPARE reads up to the ”# lines until resynch” (default is 5) in the compare file looking for a match, FILE_COMPARE also reads the next”# lines until resynch” from the master file.
FILE_COMPARE sees if any of these lines in the compare file match any of these lines in the master file. If any match then the file that needs to skip forward the shortest number of lines is resynched to the other file and the lines skipped are listed. If no match is found in these lines then the unmatched line from the master file is printed. Another line is then read from the master file and the compare process begins again, without advancing in the compare file.
>PRINT_FILE keywords
~DEFINE
DIFF: FILE_COMPARE("sk.old","sk.new",-1,7,5)
~CLEANER
PRINT_LINES "\S" "Checking new msgfile for keyword changes"
PRINT_LINES "\S" diff
~END
Here is a sample of the print file output. The total number of errors found would be listed at the end of the file. The master file is listed first, followed by the line number and text of the line that differs from the compare file. In this example lines 1 and 2 in the master file differ from the same line in the compare file. 24 is the total errors found comparing these two files.
Checking new msgfile for keyword changes File: 1 ( 1) postrelease Mentor13Jul93(showkey,sk.new) ... Watcom (C) CfMC 1978 -1993 File: 1 ( 2) System versions: 714 ... 0 ... 0 ...9305 File: 2 ( 1) postrelease Mentor21Jul93(showkey,sk.new) ... Watcom (C) CfMC 1978 - 1993 File: 2 ( 2) System versions: 720 ... 0 ... 0 ...9305 . . . 24
STRING FUNCTIONS
FIND_STRING( string variable1, string variable2) Reports the number times string variable1 appears in string variable2. This is a string search, text may be inside other words. The search is not case sensitive.
EX: FIND_STRING("IF","ENDIF")
Will return a 1. You can use variables in your search.
EX: INPUT $
~CLEAN
MODIFY str1 [1.10$]="123456"
PRINT "Number of matches: \s"
FIND_STRING("5",str1)
~END
This example would return: Number of matches: 1
For a search that match entire strings only, use the WORD_MATCHES function.
STRING_FROM_NUMBER(num, wid, dec) Converts a numeric argument into a string. Num is the numeric argument, wid is the string width, and dec is the number of decimals. You can use a negative number for wid to zero fill the string. This function is useful in combination with the PUTID command to assign case IDs. Num, wid and dec can be either numbers or variables. For example, you can use the system constant CASE_NUMBER for the numeric argument, for example:
EX: PUTID STRING_FROM_NUM(CASE_NUMBER, -4, 0)
The example below assigns sequential case IDs to a data file that has no ID field.
EX:
~DEFINE
PROC={mkid:
IF FIRST_CASE THEN
CREATE tmp(=1, =1)=0
ENDIF
MODIFY [$r=tmp T by T] += 1
PUTID STRING_FROM_NUM([$r=tmp T by T], 4, 0)
ERROR "Table cell" CASEID
WRITECASE
}
~INPUT data.asc ascii=80:11.4
~OUTPUT string
~EXC PROC=mkid
~END
STRIP(string variable) Strips leading and/or trailing blanks from a string variable or data location containing string information.
~DEFINE str1[$S=" abc de "] MODIFY [10-16$]=STRIP(str1)
This would strip the blanks off the front and back of the variable str1, and return “abc de” to columns 10 – 16.
SUBSTITUTE(string variable, “original string”, “replacement string” ) Allows you to substitute one string of characters for another in a string variable or data location containing string information.
This function is useful when making changes to “forms” being printed.
info[$s="Beaver Cleaver, 4 Primrose Ln, Upper Kirkwood MO"] SUBSTITUTE(info, ", ", "\n")
This would change each ”, ” in the string variable INFO to a new line character, resulting in:
Beaver Cleaver 4 Primrose Ln Upper Kirkwood MO
This function is case-sensitive, thus in the fourth example below, “apples” is not a match with “Apples”. No substitution is done. The SUBSTITUTE function can be a part of a variable definition.
~DEFINE
str_1: [$="Apples"]
str_2: [$="Oranges"]
str_orig: [$="Apples for sale!"]
sub_def: SUBSTITUTE("Apples and lemons say the bells of &
St. Clemons.", "Apples", "Oranges")
~CLEANER
SAY SUBSTITUTE(str_orig, str_1, str_2)
SAY SUBSTITUTE(str_orig, "Apples", "Oranges")
SAY SUBSTITUTE("Apples for sale!", "Apples","Oranges")
SAY SUBSTITUTE("Apples for sale! Get your apples here!" &
, "Apples", "Oranges")
SAY sub_def
~END
UPSHIFT() and DOWNSHIFT () Changes the case of a string.
EX: ~CLEAN M test[1.12$]="ABcdEFghIJkl" PRINT "Upshifted= \s" upshift(test) PRINT "Downshifted= \s" downshift(test)
would print the lines:
Upshifted= ABCDEFGHIJKL Downshifted= abcdefghijkl
The upshift and downshift functions can also be used in conjunction with $U, $D and $N variable types. For example, the DOWNSHIFT() function can return a lowercase version of an $U variable (see Chapter 3 Changing Case).
9.4 PARTITIONING DATA FILES
If multiple table runs are to be performed on a single data file, with each run based on a subset of the whole, we can partition or index the data utilizing a user-supplied criteria. For instance, we may want to run multiple sets of tables using a particular region or store as a base for each run. We could accomplish this using a SELECT option on the ~INPUT statement or using a FILTER for a given run. However, Mentor gives us another, faster way to accomplish this goal.
MAKE_READ_CONTROL
This command is used to define the variable that controls the reading of the data file in future runs. The variable must have unique categories so that no case falls into more than one category. CAT or NUM type variables may be used. Categories must be specified in order of sort. Table suffixes will differentiate the sets of tables and will be based on the controlling categories.
MAKE_READ_CONTROL =MARITAL[359#M/S]
In this example, the table suffixes will be “_M” and “_S”.
READ_CONTROL
Used as an option to the ~INPUT command, this command allows the run to read only categories previously specified by the MAKE_READ_CONTROL command, thereby speeding up data processing. Categories must be specified in ascending order.
Specific syntax for these keywords can be found in Appendix B: TILDE COMMANDS.
Example Reports
1) An open-ended opinion table run by a combination of responses to a prior scale question.
This example illustrates a simple use of MAKE_READ_CONTROL and READ_CONTROL to get two sets of tables, each based on different responses to a scale question; one run on those very or somewhat satisfied and one run on those very or somewhat dissatisfied.
>CREATE_DB HARDWARE
~DEFINE
LINES= {HEADSAT: =SATISFACTION LEVEL: VERY/SOMEWHAT SATISFIED\N
}
LINES= {HEADDISSAT: =SATISFACTION LEVEL: VERY/SOMEWHAT &
DISSATISFIED\N
}
TABLE_SET={BAN1:
EDIT=:
COLUMN_WIDTH=8
STUB_WIDTH=22
PERCENT_DECIMALS=1
-COLUMN_TNA
-PERCENT_SIGN
STATISTICS_DECIMALS=2
RUNNING_LINES=1
}
BANNER={:
| AGE
| =============================
| UNDER 45 OR
| TOTAL 25 25-34 35-44 OLDER
| ----- ----- ----- ----- ----- }
COLUMN=: TOTAL WITH &
[24^1,2//5,6] ''AGE
}
TABLE_SET={Q3:
TITLE=: Q3. HOW SATISFIED ARE YOU WITH HARRY'S HARDWARE?\N
}
STUB=:
VERY SATISFIED (1)
SOMEWHAT SATISFIED (2)
SOMEWHAT DISSATISFIED (3)
VERY DISSATISFIED (4)
[STATROW] MEAN
[STATROW] STD }
ROW=: [6^1//4] $[MEAN,STD] [6] }
TABLE_SET={Q3A:
TITLE=: Q3A. WHY ARE YOU SATISFIED OR DISSATISFIED WITH &
HARRY'S HARDWARE?\N }
LOCAL_EDIT=: RANK_LEVEL=1,MINIMUM_FREQUENCY=1 }
STUB=:
[COMMENT, UNDER_LINE]POSITIVE RESPONSES
[R=1, STUB_INDENT=2]GOOD QUALITY TOOLS/HARDWARE
[R=1, STUB_INDENT=2]PAYMENT CHOICES GOOD
[R=1, STUB_INDENT=2]RESPONSIVE/COOPERATIVE
[R=1, STUB_INDENT=2]HARRY'S IS GOOD HARDWARE
[R=1, STUB_INDENT=2]GOOD PROJECT MANAGEMENT ADVICE
[R=1, STUB_INDENT=2]EDUCATION/INFORMATION
[R=0, STUB_INDENT=2]OTHER POSITIVE
[COMMENT, UNDER_LINE]NEGATIVE RESPONSES
[R=1, STUB_INDENT=2]POOR QUALITY TOOLS/HARDWARE
[R=1, STUB_INDENT=2]INSUFFICIENT PAYMENT OPTIONS
[R=1, STUB_INDENT=2]POOR MANAGEMENT/HELP
[R=1, STUB_INDENT=2]TOO CROWDED
[R=1, STUB_INDENT=2]HIGH PRICES
[R=1, STUB_INDENT=2]INSUFFICIENT PROJECT HELP
[R=1, STUB_INDENT=2]LACK OF LUMBER CHOICES
[R=0, STUB_INDENT=2]OTHER NEGATIVE
[R=1L] NO RESPONSE
}
ROW=: [07.2,...,15.2*F#1//6/9/10//16/19/20] }
~INPUT DATACLN
~MAKE_READ_CONTROL Q3READCTRL = Q3RC[6^1,2/3,4]
>PRINT_FILE HHSAT
~INPUT DATACLN,READ_CONTROL=Q3READCTRL(1)
~EXECUTE
HEADER=HEADSAT
TABLE_SET=BAN1
TABLE_SET=Q3,TABLE=*
TABLE_SET=Q3A,TABLE=*
RESET
>PRINT_FILE HHDISSAT
~INPUT DATACLN, READ_CONTROL=Q3READCTRL(2)
~EXECUTE
HEADER=HEADDISSAT
TABLE_SET=BAN1
TABLE_SET=Q3,TABLE=*
TABLE_SET=Q3A,TABLE=*
~END
This run stores the READ_CONTROL item Q3READCTRL in the open DB file HARDWARE and can therefore be called up in other runs without re-specifying.
A previously defined variable, i.e., SEX could be used in a ~MAKE_READ_CONTROL statement, but only if the responses were 1,2 or F,M because M,F isn’t in sorted order.
The list file created by this run has a summary of the READ_CONTROL item included. This summary looks like:
~MAKE_READ_CONTROL Q3READCTRL = Q3RC[6^1,2/3,4] Number of cases read not fitting into any category: 5 Number of cases fitting into category 1: 38 Number of cases fitting into category 2: 25
The print file HHSAT.PRT, the first table of which follows, has only those respondents who qualified for category 1 of the READ_CONTROL item named Q3READCTRL. This category is those respondents who had a 1 or a 2 punch in column 6. The HEADER was created by the user and the table name was created automatically due to the use of TABLE=*.
EXAMPLE 1 TABLE:
SATISFACTION LEVEL: VERY/SOMEWHAT SATISFIED
TABLE 001
Q3. HOW SATISFIED ARE YOU WITH HARRY'S HARDWARE?
AGE
=============================
UNDER 45 OR
TOTAL 25 25-34 35-44 OLDER
----- ----- ----- ----- -----
TOTAL 38 7 15 10 6
100.0 100.0 100.0 100.0 100.0
N/A - - - - -
VERY SATISFIED (1) 12 5 4 1 2
31.6 71.4 26.7 10.0 33.3
SOMEWHAT SATISFIED (2) 26 2 11 9 4
68.4 28.6 73.3 90.0 66.7
SOMEWHAT DISSATISFIED
(3) - - - - -
VERY DISSATISFIED (4) - - - - -
MEAN 1.68 1.29 1.73 1.90 1.67
STD 0.47 0.49 0.46 0.32 0.52
Related keywords:
TABLE_FIELD Allows automatic table names to be suffixed with the categories specified by the READ_CONTROL command. Causes a separate set of tables to be run for each category of the MAKE_READ_CONTROL variable. If this command is used then each category in the MAKE_READ_CONTROL variable can have only one value.
#VARIABLE=<varname># A System variable similar to #DATE# or #PAGE# that allows substitution of variable category labels into text strings.
TABLE_NAME A System constant which is the name of the last table the program has dealt with.
JOIN A function used to join two text type variables or a text string with a text variable.
2) Automatic switching between four bases which are the responses to a prior scale question.
This example uses MAKE_READ_CONTROL and READ_CONTROL to get four runs through the data, each based on a different response to a scale question. The tables are numbered and titled automatically using TABLE_FIELD.
>CREATE_DB HARDWARE
~DEFINE
TABLE_SET= {BAN1:
EDIT=: COLUMN_WIDTH=8
STUB_WIDTH=22
-COLUMN_TNA
PERCENT_DECIMALS=1
-PERCENT_SIGN
STATISTICS_DECIMALS=2
RUNNING_LINES=1
}
BANNER=:
| AGE
| =============================
| UNDER 45 OR
| TOTAL 25 25-34 35-44 OLDER
| ----- ----- ----- ----- ----- }
COLUMN=: TOTAL WITH &
[24^1,2/3/4/5,6] ''AGE
}
~SPEC_FILES HHSPC ‘‘this is required before the DEFINE block
~DEFINE
TABLE_SET= {Q3:
TITLE=:
Q3. HOW SATISFIED ARE YOU WITH HARRY'S HARDWARE?\N }
STUB=:
VERY SATISFIED (1)
SOMEWHAT SATISFIED (2)
SOMEWHAT DISSATISFIED (3)
VERY DISSATISFIED (4)
[STATROW] MEAN
[STATROW] STD }
ROW=: [6^1//4] $[MEAN,STD] [6]
}
TABLE_SET= {Q3A:
TITLE=: Q3A. WHY ARE YOU SATISFIED OR DISSATISFIED WITH &
HARRY'S HARDWARE?\N }
LOCAL_EDIT={: RANK_LEVEL=1, MINIMUM_FREQUENCY=1 }
STUB=:
[COMMENT, UNDERLINE]POSITIVE RESPONSES
[R=1, STUB_INDENT=2] GOOD QUALITY TOOLS/HARDWARE
[R=1, STUB_INDENT=2] PAYMENT CHOICES GOOD
[R=1, STUB_INDENT=2] RESPONSIVE/COOPERATIVE
[R=1, STUB_INDENT=2] HARRY'S IS GOOD HARDWARE
[R=1, STUB_INDENT=2] GOOD PROJECT MANAGEMENT ADVICE
[R=1, STUB_INDENT=2] EDUCATION/INFORMATION
[R=0, STUB_INDENT=2] OTHER POSITIVE
[COMMENT, UNDER_LINE] NEGATIVE RESPONSES
[R=1, STUB_INDENT=2] POOR QUALITY TOOLS/HARDWARE
[R=1, STUB_INDENT=2] INSUFFICIENT PAYMENT OPTIONS
[R=1, STUB_INDENT=2] POOR MANAGEMENT/HELP
[R=1, STUB_INDENT=2] TOO CROWDED
[R=1, STUB_INDENT=2] HIGH PRICES
[R=1, STUB_INDENT=2] INSUFFICIENT PROJECT HELP
[R=1, STUB_INDENT=2] LACK OF LUMBER CHOICES
[R=0, STUB_INDENT=2] OTHER NEGATIVE
[R=1L] NO RESPONSE }
ROW=: [07.2,...,15.2*F#1//6/9/10//16/19/20]
}
CTRLNAME[ (TABLE_NAME) 6.1 # "VERY SATISFIED":1 /&
"SOMEWHAT SATISFIED":2/&
"SOMEWHAT DISSATISFIED":3/&
"VERY DISSATISFIED":4 ]
LINES= {HEADSAT: =SATISFACTION LEVEL: #VARIABLE=CTRLNAME#\N }
~INPUT DATACLN
~MAKE_READ_CONTROL Q3READCTRL = Q3RC[6^1/2/3/4]
>PRINT_FILE HHALL
~INPUT DATACLN,DOTs=1,READ_CONTROL=Q3READCTRL(1,2,3,4)
~SET TABLE_FIELD="_" JOIN [ Q3RC $]
AUTOMATIC_TABLES
~EXECUTE
HEADER=HEADSAT
TABLE_SET=BAN1
TABLE_SET=Q3
TABLE_SET=Q3A
RESET,PRINT_RUN
~END
NOTE: There must be one label for each category specified for the tables to be labeled properly. Punches don’t have to be in the same order, but they must match.
The list file created by this run has a summary of the READ_CONTROL item included. This summary looks like:
~MAKE_READ_CONTROL Q3READCTRL = Q3RC[6^1/2/3/4] Number of cases read not fitting into any category: 5 Number of cases fitting into category 1: 12 Number of cases fitting into category 2: 26 Number of cases fitting into category 3: 18 Number of cases fitting into category 4: 7
The print file HHALL.PRT, the first table of which follows, has only those respondents who qualified for category 1 of the READ_CONTROL item named Q3READCTRL. This category is those respondents who had a 1 punch in column 6. The HEADER was created by using the CTRLNAME variable which is the sixth column of the table name with associated text. The table names, which because of AUTOMATIC_TABLES being set would normally be T001, in this run have been JOINed to an “_” and the four separate categories of the variable Q3RC made into a text string.
The pertinent lines in the spec file above are:
CTRLNAME[ (TABLE_NAME) 6.1 # "VERY SATISFIED":1/&
"SOMEWHAT SATISFIED":2/&
"SOMEWHAT DISSATISFIED":3/&
"VERY DISSATISFIED":4 ]
LINES= {HEADSAT: =SATISFACTION LEVEL: #VARIABLE=CTRLNAME#\N
}
~MAKE_READ_CONTROL Q3READCTRL = Q3RC[6^1/2/3/4]
~INPUT,DATACLN,DOTS=1,READ_CONTROL=Q3READCTRL(1,2,3,4)
~SET TABLE_FIELD="_",JOIN [ Q3RC $]
AUTOMATIC_TABLES
Four different sets of tables were made (one for each category of the MAKE_READ_CONTROL variable Q3READCTRL and each with an automatically created HEADER), with a table names that look like T001_1, T002_1, etc. for category 1 and T001_2, T002_2, etc. for category 2 and so on, the tables are printed in the order of all category 1 tables in numerical order, then all category 2 tables in numerical order, etc.
EXAMPLE 2 TABLE:
SATISFACTION LEVEL: VERY SATISFIED
TABLE 001_1
Q3. HOW SATISFIED ARE YOU WITH HARRY'S HARDWARE?
AGE
=============================
UNDER 45 OR
TOTAL 25 25-34 35-44 OLDER
----- ----- ----- ----- -----
Total 12 5 4 1 2
100.0 100.0 100.0 100.0 100.0
N/A - - - - -
VERY SATISFIED (1) 12 5 4 1 2
100.0 100.0 100.0 100.0 100.0
SOMEWHAT SATISFIED (2) - - - - -
SOMEWHAT DISSATISFIED
(3) - - - - -
VERY DISSATISFIED (4) - - - - -
MEAN 1.00 1.00 1.00 1.00 1.00
STD 0.00 0.00 0.00 ? 0.00