[toc]
6.4 SUMMARY TABLES (MARKET SHARE)
A table of sums (sometimes called a market share table) is usually created when you have a series of numeric type questions about how many purchases have been made of particular brands in a specified time period. The table you create needs to show what percentage a particular brand’s purchases constitute of all purchases.
In order to create this table you must realize that if you just specify a data location [col.wid] and nothing else, the program will calculate the sum of all the valid numbers in that field. To create the overall sum though, you need to add all the individual fields together making sure that you are doing any recoding if necessary (9=DK or – = 100). You may want to use the SUM function, the ++ joiner, or an exclamation point (!) after each open bracket so that missing values are counted as zero for purposes of the total sum. After you have created the sum you need to access each of the appropriate fields and join them using the WITH joiner or use multiple locations inside the same set of brackets. You will also want to make sure that you suppress the printing of the system total row (usually with a STUB_PREFACE) and that if you are printing the vertical percentage you change the percentage base to the total sum row.
>PRINT_FILE SUMS
~INPUT SUMS
~SET AUTOMATIC_TABLES,DROP_LOCAL_EDIT, DROP_BASE BEGIN_TABLE_NAME=T401
~DEFINE
STUB= STUBTOP1:
[SUPPRESS] TOTAL
[SUPPRESS] NO ANSWER }
TABLE_SET= {BAN1:
EDIT=: COLUMN_WIDTH=8,STUB_WIDTH=20,-COLUMN_TNA, STATISTICS_DECIMALS=2 }
STUB_PREFACE= STUBTOP1
BANNER=:
| GENDER
| <———->
| TOTAL MALE FEMALE
| —– —- ——}
COLUMN=: TOTAL WITH [5^1/2]
}
~EXECUTE
TABLE_SET= BAN1
~DEFINE
TABLE_SET= {TAB401:
HEADER=: SUMS TABLE OR MARKET SHARE}
TITLE=: TOTAL GALLONS USED}
LOCAL_EDIT=: VERTICAL_PERCENT=1 }
STUB=:
TOTAL GALLONS
BRAND A
BRAND B
BRAND C
BRAND D
BRAND E
BRAND F
BRAND G OTHER}
ROW=: SUM([11.4,15,…,39]) WITH [11.4,15,…,39]
}
~EXECUTE
TABLE_SET= TAB401
Here are some alternate ways to write the row variable:
ROW401A: ([!11.4] + [!15.4] + [!19.4] + [!23.4] + [!27.4] + & [!31.4] + [!35.4] + [!39.4]) &
WITH [11.4,15,…,39]
ROW401B: ([11.4] ++ [15.4] ++ [19.4] ++ [23.4] ++ [27.4] ++ & [31.4] ++ [35.4] ++ [39.4]) &
WITH [11.4,15,…,39]
Here is the table that is printed.
SUMS TABLE OR MARKET SHARE
TABLE 401
TOTAL GALLONS USED
GENDER
<———->
TOTAL MALE FEMALE
TOTAL GALLONS 166066 71569 94497
100.0% 100.0% 100.0%
BRAND A 26299 12674 13625
15.8% 17.7% 14.4%
BRAND B 2989 1450 1539
1.8% 2.0% 1.6%
BRAND C 30939 12787 18152
18.6% 17.9% 19.2%
BRAND D 25885 12904 12981
15.6% 18.0% 13.7%
BRAND E 21075 9347 11728
12.7% 13.1% 12.4%
BRAND F 19183 8191 10992
11.6% 11.4% 11.6%
BRAND G 16170 5817 10353
9.7% 8.1% 11.0%
OTHER 23526 8399 15127
14.2% 11.7% 16.0%
6.5 HOLECOUNT AND BREAK TABLES
A “holecount” table is CfMC’s term for a table that cannot be built in a simple cross-tab format. There are many table designs that you may want to see that cannot be created by crossing one variable by another. Being able to recognize when you need to create a holecount table is usually much more difficult than creating the table itself. The idea behind the table is that you will define all the categories as if the table was a long one column (or one row) table and use the keyword $[BREAK] to break the definition at a certain category and force the next set of categories to print as the table’s next column (or row, depending on the table orientation).
The easiest example of when you need to use a holecount table is when you have a series of rating scales on different brands or attributes and you want to create a summary table in which the brands are the stub or banner and the rating scale itself is the other axis. Suppose for this example that the brands are being used for the banner and the rating scale for the stub, then notice that there are no two variables that you can create to describe each of these items since the data in each column of the table is coming from a different data location.
“Break” tables are a variation of holecount tables. You have the same basic situation, but under each banner heading you are showing data from a current and previous wave, or two different products that are being compared. See “6.5.4 Break Table with a Multi-level Banner” for a quick preview of a break table. The data for Brand A and Brand B is coming from two different data locations, and it is also being “broken” by city. Again, you cannot write a true cross-tab but you can create the table as though you were creating Brand B’s mentions after Brand A’s and then use the $[BREAK] keyword to wrap the data so that it formats properly.
There are a number of things to be aware of when you try to create a $[BREAK] table. One very important point is whether or not a total column and/or row will be needed for the table, and if so what it should look like. This is very important because you often need to create your own summary totals because the system summary rows and columns will be the total base for the table, unless you have used the $[BREAK_CONTROL] option (see 6.5.3 Holecount Table with a Varying Percentage Base for more information on the $[BREAK_CONTROL]).
NOTE: The number of categories in each piece of the BREAK variable must be the same.
6.5.1 Holecount Table with Different Brands (Locations) in the Banner
Suppose you need to produce a table with a number of different brands in your banner and a rating scale for each brand down the side. You cannot create a normal cross-tab because each rating scale is located in a different location. The first thing to determine is whether you want to define each column as you go and then break to the next column or if you want to define each row as you go and then break to the next row. Either way can give you the appropriate table, and the method of choice is usually determined by which of those two variables is easier to write and understand.
In the first example below, it is assumed that the rating scales for five different brands of colas are stored in data columns 7 through 11. To create this table by defining each column one at a time, you would define the column variable as TOTAL, and then you define the row variable by defining the first column (possibly the total), then use the $[BREAK] keyword, and then define the second column and so on. This table could also be written by defining the row variable as TOTAL and defining each row in the column variable.
NOTE: The following set of commands define a standard front end for the all the examples in this section, except where noted.
>PRINT_FILE HOLE
~INPUT HOLE
~SET AUTOMATIC_TABLES,DROP_LOCAL_EDIT,DROP_BASE, BEGIN_TABLE_NAME=T501
~DEFINE
STUB= STUBTOP1:
[SUPPRESS] TOTAL
[SUPPRESS] NO ANSWER }
These commands are exclusive to this example.
TABLE_SET= {TAB501:
HEADER=: HOLECOUNT TABLE WITH THE DIFFERENT BRANDS (LOCATIONS) IN THE BANNER}
TITLE=: RATING OF COLAS}
TITLE_4=: BASE= TOTAL SAMPLE}
EDIT=: COLUMN_WIDTH=8,STUB_WIDTH=20 -COLUMN_TNA,
VERTICAL_PERCENT=1} STUB_PREFACE= STUBTOP1
BANNER=:
| <—————- COLAS ————->
| TOTAL BRND A BRND B BRND C BRND D BRND E
| —– —— —— —— —— ——}
COLUMN=: TOTAL
STUB=:
TOTAL
VERY GOOD
GOOD
FAIR
POOR
VERY POOR
DON’T KNOW }
ROW=: [07,…,11*L^1-12,B/1//6] $[BREAK] TOTAL WITH [07^1//6] &
$[BREAK] TOTAL WITH [08^1//6] $[BREAK] TOTAL WITH [09^1//6] &
$[BREAK] TOTAL WITH [10^1//6] $[BREAK] TOTAL WITH [11^1//6]
}
~EXECUTE
TABLE_SET= TAB501
Here is an alternate way to write the row:
COL501A: [7,…,11*L^1-12,B] WITH [7,…,11^1-12,B] $[BREAK] &
>REPEAT $A=1,…,6; STRIP=”$[BREAK] &”
[7,…,11*L^$A] WITH [7,…,11^$A] $[BREAK] &
>END_REPEAT
ROW501A: TOTAL
Here is the table Mentor prints:
HOLECOUNT TABLE WITH THE DIFFERENT BRANDS (LOCATIONS) IN THE BANNER
TABLE 501
RATING OF COLAS BASE= TOTAL SAMPLE
<—————- COLAS ————->
TOTAL BRND A BRND B BRND C BRND D BRND E
—– —— —— —— —— ——
TOTAL 500 100 100 100 100 100
100.0% 100.0% 100.0% 100.0% 100.0% 100.0%
VERY GOOD 83 25 15 17 13 13
16.6% 25.0% 15.0% 17.0% 13.0% 13.0%
GOOD 85 15 22 12 16 20
17.0% 15.0% 22.0% 12.0% 16.0% 20.0%
FAIR 81 14 19 18 10 20
16.2% 14.0% 19.0% 18.0% 10.0% 20.0%
POOR 98 17 18 16 25 22
19.6% 17.0% 18.0% 16.0% 25.0% 22.0%
VERY POOR 74 12 11 19 17 15
14.8% 12.0% 11.0% 19.0% 17.0% 15.0%
DON’T KNOW 79 17 15 18 19 10
15.8% 17.0% 15.0% 18.0% 19.0% 10.0%
6.5.2 Holecount Table with Rating Scales (Different Values) in Banner
Suppose you want to produce the same table as in the previous section except that you want to rotate the table 90 degrees so that the rating scale is now in the banner and the different brands are in the stub. You will want to use the same logic as the preceding table, but you will want to flip the column and row variable definitions, along with redefining your banner and stub labels to match. One other important difference is that you will probably want to percentage horizontally instead of vertically.
~DEFINE
TABLE_SET= {TAB502:
HEADER=: HOLECOUNT TABLE WITH THE RATING SCALES (DIFFERENT VALUES) IN THE BANNER}
EDIT=: COLUMN_WIDTH=8,STUB_WIDTH=20,-COLUMN_TNA,-VERTICAL_PERCENT,
HORIZONTAL_PERCENT=1}
BANNER=:
| OVERALL RATING
| <==========================================>
| VERY VERY DON’T
| TOTAL GOOD GOOD FAIR POOR POOR KNOW
| —– —- —- —- —- —- —–}
COLUMN=: [07,…,11*L^1-12,B/1//6] $[BREAK] TOTAL WITH [07^1//6] &
$[BREAK] TOTAL WITH [08^1//6] $[BREAK] TOTAL WITH [09^1//6] &
$[BREAK] TOTAL WITH [10^1//6] $[BREAK] TOTAL WITH [11^1//6]
STUB=:
TOTAL
BRAND A
BRAND B
BRAND C
BRAND D
BRAND E}
ROW=: TOTAL
}
~EXECUTE
TABLE_SET= TAB502
Here is the table Mentor prints:
HOLECOUNT TABLE WITH THE RATING SCALES (DIFFERENT VALUES) IN THE BANNER
TABLE 502
RATING OF COLAS
OVERALL RATING
<==========================================>
VERY VERY DON’T
TOTAL GOOD GOOD FAIR POOR POOR KNOW
—– —- —- —- —- —- —–
TOTAL 500 83 85 81 98 74 79
100.0% 16.6% 17.0% 16.2% 19.6% 14.8% 15.8%
BRAND A 100 25 15 14 17 12 17
100.0% 25.0% 15.0% 14.0% 17.0% 12.0% 17.0%
BRAND B 100 15 22 19 18 11 15
100.0% 15.0% 22.0% 19.0% 18.0% 11.0% 15.0%
BRAND C 100 17 12 18 16 19 18
100.0% 17.0% 12.0% 18.0% 16.0% 19.0% 18.0%
BRAND D 100 13 16 10 25 17 19
100.0% 13.0% 16.0% 10.0% 25.0% 17.0% 19.0%
BRAND E
100 13 20 20 22 15 10
100.0% 13.0% 20.0% 20.0% 22.0% 15.0% 10.0%
6.5.3 Holecount Table with a Varying Percentage Base
If you are trying to create a table similar to the one above, but each column wants to be percentaged off a different number like those who purchased that brand, then you want to create a $[BREAK_CONTROL] variable which allows you to define an individual base for each piece of the $[BREAK] variables that will be created. The $[BREAK_CONTROL] variable will immediately precede the beginning of the $[BREAK] variable (see example following).
Suppose that you want to create a table similar to the one in “6.5.1 Holecount Table with Different Brands (Locations) in the Banner”, except that now you want each brand’s column based on the fact that the respondent purchased that brand (I.E. You want the percentage base to be any response). If brand purchase was stored in column 6 with BRAND A stored as a 1, BRAND B as a 2, BRAND C as a 3, BRAND D as a 4, and BRAND E as a 5, then you can write the $[BREAK_CONTROL] variable as $[BREAK_CONTROL=[6^1//5]] or if you have previously defined [6^1//5] with a name like BRAND_PUR, then you define it as $[BREAK_CONTROL=BRAND_PUR]. This will put a base of column 6 a 1 punch on the first $[BREAK] item, a base of column 6 a 2 punch on the second, and so forth. This will also put the base on the system summary rows so that you will not have to create your own.
NOTE: If you do not have the variable pre-defined you will need to terminate the $[BREAK_CONTROL] option with two right brackets, one to close the variable and the second to close the $[BREAK_CONTROL]. Use of the BREAK_CONTROL option can significantly reduce processing time, if most respondents do not fall into most of the BREAK categories.
~DEFINE
STUB= STUBTOP2:
TOTAL
[SUPPRESS] NO ANSWER }
TABLE_SET= {TAB503:
HEADER=: HOLECOUNT TABLE WITH REDUCED BASE}
TITLE=: RATING OF COLAS }
TITLE_4=: BASE= THOSE WHO HAVE PURCHASED THE COLA}
EDIT=: COLUMN_WIDTH=8,STUB_WIDTH=20,-COLUMN_TNA }
STUB_PREFACE= STUBTOP2
BANNER=:
| <—————- COLAS ————->
| BRND A BRND B BRND C BRND D BRND E
| —— —— —— —— ——}
COLUMN=: TOTAL
STUB=:
VERY GOOD
GOOD
FAIR
POOR
VERY POOR
DON’T KNOW }
ROW=: $[BREAK_CONTROL=[06^1//5]] [07^1//6] $[BREAK] [08^1//6] &
$[BREAK] [09^1//6] $[BREAK] [10^1//6] $[BREAK] [11^1//6]
}
~EXECUTE
TABLE_SET= TAB503
Here is the table Mentor prints:
HOLECOUNT TABLE WITH REDUCED BASE
TABLE 503
RATING OF COLAS
BASE= THOSE WHO HAVE PURCHASED THE COLA
<—————- COLAS ————->
BRND A BRND B BRND C BRND D BRND E
—— —— —— —— ——
TOTAL 40 41 37 37 37
100.0% 100.0% 100.0% 100.0% 100.0%
VERY GOOD 9 2 7 3 4
22.5% 4.9% 18.9% 8.1% 10.8%
GOOD 9 11 4 9 7
22.5% 26.8% 10.8% 24.3% 18.9%
FAIR 4 8 10 2 8
10.0% 19.5% 27.0% 5.4% 21.6%
POOR 5 7 4 11 8
12.5% 17.1% 10.8% 29.7% 21.6%
VERY POOR 5 6 7 5 5
12.5% 14.6% 18.9% 13.5% 13.5%
DON’T KNOW 8 7 5 7 5
20.0% 17.1% 13.5% 18.9% 13.5%
6.5.4 Break Table with a Multi-level Banner
If your banner is split into multiple levels so that the higher level is a demographic item such as area and the lower level is multiple products with the data for each product in a different location, then you also need to use the $[BREAK] keyword to produce this table. Instead of setting the column equal to the total as we have in the above examples, for this situation you set the column equal to the upper level variable. The row definition then is each of the variables for the two or more products joined together with the $[BREAK] keyword. Notice that the number of banner points in the finished table will be the number of categories in the column definition times the number of different breaks in the row definition. The number of rows in the table will be the number of categories in any one piece of the break variable. In the example below the rating for two products (A and B) are stored in locations 7 and 8 and the city designation is stored in location 5. The $[BREAK_CONTROL] keyword is used so that the System Total row will show correct values.
~DEFINE
TABLE_SET= {TAB504:
HEADER=: $[BREAK] TABLE WITH A MULTI-LEVEL BANNER}
TITLE=: RATING OF BRAND A AND BRAND B BY CITY}
EDIT=: COLUMN_WIDTH=6,STUB_WIDTH=20,-COLUMN_TNA }
BANNER=:
| CITY
| <=============================================>
| SAN LOS
| TOTAL FRANCISCO ANGELES NEW YORK BOSTON
| <———> <———> <———> <———> <———>
| BRD A BRD B BRD A BRD B BRD A BRD B BRD A BRD B BRD A BRD B
| —– —– —– —– —– —– —– —– —– —–}
COL=: TOTAL WITH [5^1//4]
STUB=:
VERY GOOD
GOOD
FAIR
POOR
VERY POOR
DON’T KNOW }
ROW=: $[BREAK_CONTROL=[7,8^1-6]] [7^1//6] $[BREAK] [8^1//6]
}
~EXECUTE
TABLE_SET= TAB504
Here is the table Mentor prints:
$[BREAK] TABLE WITH A MULTI-LEVEL BANNER
TABLE 504
RATING OF BRAND A AND BRAND B BY CITY
CITY
<=============================================>
SAN LOS
TOTAL FRANCISCO ANGELES NEW YORK BOSTON
<———> <———> <———> <———> <———>
BRD A BRD B BRD A BRD B BRD A BRD B BRD A BRD B BRD A BRD B
—– —– —– —– —– —– —– —– —– —–
TOTAL 100 100 28 28 30 30 20 20 22 22
100.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0%
VERY GOOD 25 15 9 6 7 4 4 3 5 2
25.0% 15.0% 32.1% 21.4% 23.3% 13.3% 20.0% 15.0% 22.7% 9.1%
GOOD 15 22 4 3 6 8 4 5 1 6
15.0% 22.0% 14.3% 10.7% 20.0% 26.7% 20.0% 25.0% 4.5% 27.3%
FAIR 14 19 3 3 3 4 1 6 7 6
14.0% 19.0% 10.7% 10.7% 10.0% 13.3% 5.0% 30.0% 31.8% 27.3%
POOR 17 18 3 4 7 9 3 4 4 1
17.0% 18.0% 10.7% 14.3% 23.3% 30.0% 15.0% 20.0% 18.2% 4.5%
VERY POOR 12 11 5 5 3 2 2 – 2 4
12.0% 11.0% 17.9% 17.9% 10.0% 6.7% 10.0% 9.1% 18.2%
DON’T KNOW 17 15 4 7 4 3 6 2 3 3
17.0% 15.0% 14.3% 25.0% 13.3% 10.0% 30.0% 10.0% 13.6% 13.6%
6.6 MULTIPLE LOCATION TABLES (OVERLAY AND LOOP STRUCTURES)
Whenever you create a table where multiple responses were collected from a given respondent and each response was collected in a separate location, you can refer to that as a “multiple location” table. Very often when you have this design you want to report information across all the locations, rather than across respondents, especially if a respondent can give the same answer in more than one location. A typical example of this might be if you ask each respondent the make of each automobile they own and now you want to produce a table that talks about automobiles instead of respondents. For instance, you want to know what percentage of all the automobiles owned are manufactured by Chevrolet, Ford, Toyota etc.
NOTE: A given respondent can own more than one car of the same make.
This scenario can be further complicated if there are follow up questions asked about each item originally mentioned. Suppose as above the respondent is asked the make of each automobile they own, but then is asked other questions about each automobile like what model year it is, how many miles it has on it, or if they like the service they have received on it. This is sometimes refer to as a loop or a loop structure because you will loop through a series of questions about each automobile until you have talked about all the automobiles. The difficulty in these constructions is that the information for a particular make may be in any of the data positions allocated and each respondent may have gone through the loop a different number of times.
There are three different basic approaches that can be used to create the above tables and the one you will want to use is determined by the data layout and the type of table you wish to produce. The three methods are: using a multiple location variable, using a looped variable, and creating an overlay structure.
To create a multiple location table using a multi-location variable you need to have the data collected in the same format in each of the different locations, only one axis of the table has an item that has data coming from different locations, and no statistical testing is being done. If so, then just specify each of the data locations in a single set of brackets and use either the *F or *L modifier to either net or sum the locations together. See 4.3 DEFINING DATA VARIABLES for more information on the *F and *L modifiers.
To create a multiple location table using a loop variable you again need to have the data collected in the same format in each of the different locations and the locations must be the same distance from each other. Unlike using multi-location variables you can have a loop variable in more than one axis of the table, but if two or more of the axes are looped, they must contain the same number of iterations.
The third method to create a multiple location table is to use an overlay structure. This method has no restrictions on the data layout and you can have more than one axis overlayed, except that each axis that is overlayed must have the same number of overlays.
Multi-location variable:
MULTI_LOC: [11,12,13,14*L^16/9]
Loop variable:
LOOPVAR: [(4,1)11^16/9]
Overlay Structure:
OVERLAY: [11^16/9] $[OVERLAY] [12^16/9] $[OVERLAY] & [13^16/9] & $[OVERLAY] [14^16/9]
The above three variables would all produce a similar table to each other depending upon what the other axes look like. If both the column and the base were single location variables then the tables would be exactly the same. If you are producing a simple multiple location table, then approach one is preferred, but if the data is stored in a loop structure and either the base or the column variable is also dependent upon the loop then you must use either the second or third approach.
For loop or overlay variables, the program treats each iteration of the loop or overlay piece as a distinct case. Suppose you have 100 respondents in your sample, but you create a variable with five overlay pieces in it, then the program will act as though there are 500 possible cases for that table. If one of the axes is not overlayed, then that variable will be used for each piece of the overlay. The same logic also applies to loop variables.
NOTE: You often want to create your own summary rows and columns because the system-generated numbers may not be appropriate when using overlayed or loop variables.
The maximum number of iterations in a loop variable is 99. See also ~SET LOOP_KICKOUT.
6.6.1 Simple Multiple Location Tables
If you create a table with a typical demographic banner and the row is a question that is stored in multiple locations for each respondent with the same coding scheme and this table will count all mentions together regardless of which location the answer came from, then you create this table by creating a multi-location variable.
For example, you ask respondents about the type of banking they do. You ask each respondent about each of the different banks they use including what kinds of accounts they have at each bank and their overall satisfaction with that account. In the example below, the sex of the respondent has been stored in column 5 and we are collecting information for up to six different banks. For each account mentioned there is a 10 column field which holds information just about that bank. The first two columns of each field are a two digit number which indicates which bank the account is held at. The third column is a punch to note the type of account. The fourth column is an overall rating of that bank’s service for that account and the fifth through the tenth columns are the total dollar amount held in that account. The first mention is stored in columns 11-20, the second in 21-30, the third in 31-40, the fourth in 101-110, the fifth in 111-120, and the last in 121-130.
This first table that is produced is a simple multiple location table with the percentage base for the table being the total number of banks which is much greater than the total number of respondents, so you will need to generate your own total.
NOTE: The following set of commands define a standard front end for the next set of examples.
>PRINT_FILE OVERL
~INPUT OVERL
~SET AUTOMATIC_TABLES, DROP_LOCAL_EDIT, DROP_BASE, BEGIN_TABLE_NAME=T601
~DEFINE
STUB= STUBTOP1:
[SUPPRESS] TOTAL
[SUPPRESS] NO ANSWER }
TABLE_SET= {BAN2:
EDIT=: COLUMN_WIDTH=8, STUB_WIDTH=25,-COLUMN_TNA, STATISTICS_DECIMALS=2, VERTICAL_PERCENT=1}
STUB_PREFACE= STUBTOP1
BANNER=:
| GENDER
| <———->
| TOTAL MALE FEMALE
| —– —- ——}
COLUMN=: TOTAL WITH [5^1/2]
}
~EXECUTE
TABLE_SET= BAN2
Here are the commands exclusive to this example.
~DEFINE
TABLE_SET= {TAB601:
HEADER=: TABLE WITH MENTIONS FROM MULTIPLE LOCATIONS ADDED TOGETHER}
TITLE=: ACCOUNTS HAVE AT ANY BANK}
TITLE_4=: BASE: TOTAL ACCOUNTS OF BANKS}
STUB=:
TOTAL
CHECKING ACCOUNT
SAVINGS ACCOUNT
VISA/MASTERCARD
MORTGAGE/HOME LOAN
CAR/PERSONAL LOAN
ATM CARD
DK/NA/REF}
ROW=: [13,23,33,103,113,123*L^1-6,9/1//6/9]
~EXECUTE
TABLE_SET= TAB601
}
Here is an alternate way to write the row variable:
ROW601A: [13^1-6,9/1//6/9] $[OVERLAY] [23^1-6,9/1//6/9] $[OVERLAY] &
[33^1-6,9/1//6/9] $[OVERLAY] [103^1-6,9/1//6/9] $[OVERLAY] &
[113^1-6,9/1//6/9] $[OVERLAY] [123^1-6,9/1//6/9]
Here is the table Mentor prints:
TABLE WITH MENTIONS FROM MULTIPLE LOCATIONS ADDED TOGETHER
TABLE 601
ACCOUNTS HAVE AT ANY BANK BASE: TOTAL ACCOUNTS OF BANKS
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 285 126 159
100.0% 100.0% 100.0%
CHECKING ACCOUNT 37 16 21
13.0% 12.7% 13.2%
SAVINGS ACCOUNT 45 16 29
15.8% 12.7% 18.2%
VISA/MASTERCARD 36 18 18
12.6% 14.3% 11.3%
MORTGAGE/HOME LOAN 37 17 20
13.0% 13.5% 12.6%
CAR/PERSONAL LOAN 48 22 26
16.8% 17.5% 16.4%
ATM CARD 38 17 21
13.3% 13.5% 13.2%
DK/NA/REF 44 20 24
15.4% 15.9% 15.1%
A second table could be produced using this construction, but instead of counting each account as many times as it appears you only want to count it once no matter how many times it appears. The syntax only changes slightly for the multi-location approach, as the *L now becomes *F. In the overlay approach the keyword OVERLAY is now replaced with the keyword NET_OVERLAY. The loop variable approach cannot produce this table.
The previous table tells you how many of each account there are, while this next table tells you how many respondents have that type of account. Notice that the percentage base for this table is the true total number of respondents.
~DEFINE
STUB= STUBTOP2:
TOTAL
[SUPPRESS] NO ANSWER }
TABLE_SET= {TAB602:
HEADER=: TABLE WITH MENTIONS FROM MULTIPLE LOCATIONS NETTED TOGETHER}
LOCAL_EDIT=: VERTICAL_PERCENT=T}
STUB_PREFACE= STUBTOP2
STUB=:
CHECKING ACCOUNT
SAVINGS ACCOUNT
VISA/MASTERCARD
MORTGAGE/HOME LOAN
CAR/PERSONAL LOAN
ATM CARD
DK/NA/REF}
ROW=: [13,23,33,103,113,123*F^1//6/9]
~EXECUTE
TABLE_SET= TAB602
}
Here is an alternate way to write the row variable:
ROW602B: [13^1//6/9] $[NET_OVERLAY] [23^1//6/9] $[NET_OVERLAY] &
[33^1//6/9] $[NET_OVERLAY] [103^1//6/9] $[NET_OVERLAY] &
[113^1//6/9] $[NET_OVERLAY] [123^1//6/9]
Here is the table Mentor prints:
TABLE WITH MENTIONS FROM MULTIPLE LOCATIONS NETTED TOGETHER
TABLE 602
ACCOUNTS HAVE AT ANY BANK
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 100 42 58
100.0% 100.0% 100.0%
CHECKING ACCOUNT 32 14 18
32.0% 33.3% 31.0%
SAVINGS ACCOUNT 33 12 21
33.0% 28.6% 36.2%
VISA/MASTERCARD 32 17 15
32.0% 40.5% 25.9%
MORTGAGE/HOME LOAN 31 14 17
31.0% 33.3% 29.3%
CAR/PERSONAL LOAN 35 17 18
35.0% 40.5% 31.0%
ATM CARD 28 13 15
28.0% 31.0% 25.9%
DK/NA/REF 30 16 14
30.0% 38.1% 24.1%
6.6.2 Tables With Both the Row and the Base Overlayed
By far the most useful example of an overlay table or a loop variable is when you have a loop structure as described above and the table you want to produce has only information about a particular product in it. You want the program to look in each of the data locations, but only report information from those locations when it is referring to the particular product you are interested in. In this example suppose you want to produce a table similar to the ones above, but now you only want to report on accounts at a particular bank. In order to create this table you must define an overlay variable for the base. Similar syntax is used for defining the row definition and you must have the same number of overlay pieces in the base definition as the row definition. The resultant table will be a compilation of the six separate rows and bases with the banner. Again, you will want to produce your summary rows and columns to ensure you get the numbers you are expecting.
TIP: When creating overlay tables it is often helpful to think of each separate loop location as a separate table. Usually each of these individual tables is simple to create, and then all you need to do is combine these tables by making each of them a separate piece in the overlay. For instance, in the example below if the data was only stored in columns 11-13, then you would write the table with a BASE of [11.2#01] and the ROW of [13^1- 6,9/1//6/9]. This then becomes the first overlay piece in each of the base and the row definitions. Now looking only at the second iteration, the data was stored in columns 21-23 and therefore it would have a BASE of [21.2#01] and a ROW of [23^1-6,9/1//6/9]. This continues for the rest of the iterations.
NOTE: The same row spec would be used regardless of which bank you were reporting on as only the base definition would change.
~DEFINE
TABLE_SET= {TAB603:
HEADER=: MULTIPLE MENTION TABLE BASED ON A SINGLE ITEM FROM THE LOOP STRUCTURE}
TITLE=: ACCOUNTS HAVE AT BANK A}
TITLE_4=: BASE: THOSE WHO USE BANK A}
BASE=: [11.2#01] $[OVERLAY] [21.2#01] $[OVERLAY] [31.2#01] $[OVERLAY] &
[101.2#01] $[OVERLAY] [111.2#01] $[OVERLAY] [121.2#01]
STUB_PREFACE= STUBTOP1
STUB=:
TOTAL
CHECKING ACCOUNT
SAVINGS ACCOUNT
VISA/MASTERCARD
MORTGAGE/HOME LOAN
CAR/PERSONAL LOAN
ATM CARD
DK/NA/REF}
ROW=: [13^1-6,9/1//6/9] $[OVERLAY] [23^1-6,9/1//6/9] $[OVERLAY] &
[33^1-6,9/1//6/9] $[OVERLAY] [103^1-6,9/1//6/9] $[OVERLAY] &
[113^1-6,9/1//6/9] $[OVERLAY] [123^1-6,9/1//6/9]
}
~EXECUTE
TABLE_SET= TAB603
Here is the table Mentor prints:
MULTIPLE MENTION TABLE BASED ON A SINGLE ITEM FROM THE LOOP STRUCTURE
TABLE 603
ACCOUNTS HAVE AT BANK A BASE: THOSE WHO USE BANK A
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 44 17 27
100.0% 100.0% 100.0%
CHECKING ACCOUNT 7 3 4
15.9% 17.6% 14.8%
SAVINGS ACCOUNT 6 2 4
13.6% 11.8% 14.8%
VISA/MASTERCARD 5 2 3
11.4% 11.8% 11.1%
MORTGAGE/HOME LOAN 5 1 4
11.4% 5.9% 14.8%
CAR/PERSONAL LOAN 7 2 5
15.9% 11.8% 18.5%
ATM CARD 5 4 1
11.4% 23.5% 3.7%
DK/NA/REF 9 3 6
20.5% 17.6% 22.2%
6.6.3 Overlay Tables With Summary Statistics (Means)
If the loop structure contains variables that you would like to produce statistics on, then you can follow the procedures as above, but you also must now overlay the definition of the statistic (MEAN). Suppose a rating scale for each bank was located in columns 14, 24, 34, 104, 114, and 124 and you want to produce the mean of that rating. You will need to specify the mean for each piece of the overlay separately and the program will combine them to give you an overall mean.
NOTE: Any needed recoding of the mean would follow the normal procedures and would have to be done for each piece of the overlay. As in the table before you will need to overlay the base definition if you want to produce the table on a particular bank.
~DEFINE
TABLE_SET= {TAB604:
HEADER=: OVERLAY TABLE WITH SUMMARY STATISTICS LIKE A MEAN}
TITLE=: OVERALL RATING OF BANK A}
TITLE_4=: BASE: THOSE WHO USE BANK A}
BASE=: [11.2#01] $[OVERLAY] [21.2#01] $[OVERLAY] [31.2#01] $[OVERLAY] &
[101.2#01] $[OVERLAY] [111.2#01] $[OVERLAY] [121.2#01]
STUB=:
TOTAL
EXCELLENT (5)
VERY GOOD (4)
GOOD (3)
FAIR (2)
POOR (1)
DK/NA
[STATISTICS] MEAN
[STATISTICS] STANDARD DEVIATION }
ROW=: [14^1-5,9/5//1/9] $[MEAN,STD] [14*R=1-5] $[OVERLAY] &
[24^1-5,9/5//1/9] $[MEAN,STD] [24*R=1-5] $[OVERLAY] &
[34^1-5,9/5//1/9] $[MEAN,STD] [34*R=1-5] $[OVERLAY] &
[104^1-5,9/5//1/9] $[MEAN,STD] [104*R=1-5] $[OVERLAY] &
[114^1-5,9/5//1/9] $[MEAN,STD] [114*R=1-5] $[OVERLAY] &
[124^1-5,9/5//1/9] $[MEAN,STD] [124*R=1-5]
}
~EXECUTE
TABLE_SET= TAB604
Here is the table Mentor prints:
OVERLAY TABLE WITH SUMMARY STATISTICS LIKE A MEAN
TABLE 604
OVERALL RATING OF BANK A BASE: THOSE WHO USE BANK A
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 42 16 26
100.0% 100.0% 100.0%
EXCELLENT (5) 7 3 4
16.7% 18.8% 15.4%
VERY GOOD (4) 11 4 7
26.2% 25.0% 26.9%
GOOD (3) 7 1 6
16.7% 6.3% 23.1%
FAIR (2) 7 4 3
16.7% 25.0% 11.5%
POOR (1) 5 1 4
11.9% 6.3% 15.4%
DK/NA 5 3 2
11.9% 18.8% 7.7%
MEAN 3.22 3.31 3.17
STANDARD DEVIATION 1.34 1.38 1.34
6.6.4 Tables with the Banner and the Row Overlayed
If you have a scenario where the banner also contains a category that was collected in the loop structure you then will have to create the column variable with an overlay also. A typical example of this is that the banner might consist of all the banks and the stub consists of the services used at each bank. In order to create this table you will need to overlay all three parts of the table, row, banner, and base, because a given cell in the table is dependent upon both the row and column variable coming from the same iteration of the loop. Again, the easiest way to write this table is to pretend that you are writing six different tables each only coming from one location and then use the OVERLAY keyword to combine them all.
This type of table would also be required if you were creating a table that crosses the amount of money in the bank by type of accounts have, since both variables are inside the loop structure.
~DEFINE
TABLE_SET= {TAB605:
HEADER=: TABLE WITH BANNER, ROW, AND BASE ALL WITH OVERLAY STRUCTURES}
TITLE=: OVERALL RATING OF EACH BANK}
TITLE_4=: BASE: THOSE WHO USED THAT BANK}
BASE=: [11.2#1-10] $[OVERLAY] [21.2#1-10] $[OVERLAY] &
[31.2#1-10] $[OVERLAY] [101.2#1-10] $[OVERLAY] &
[111.2#1-10] $[OVERLAY] [121.2#1-10]
BANNER=:
| <——————- USED ———>
| BANK BANK BANK BANK BANK
| TOTAL A B C D E
| —– —- —- —- —- —-}
COLUMN=: [11.2#1-10/1//5] $[OVERLAY] [21.2#1-10/1//5] $[OVERLAY] &
[31.2#1-10/1//5] $[OVERLAY] [101.2#1-10/1//5] $[OVERLAY] &
[111.2#1-10/1//5] $[OVERLAY] [121.2#1-10/1//5] STORE_TABLES=*
}
~EXECUTE
TABLE_SET= TAB605
Here is the table Mentor prints:
TABLE WITH BANNER, ROW, AND BASE ALL WITH OVERLAY STRUCTURES
TABLE 605
OVERALL RATING OF EACH BANK BASE: THOSE WHO USED THAT BANK
<—————USED—————->
BANK BANK BANK BANK BANK
TOTAL A B C D E
—– —- —- —- —- —-
TOTAL 285 42 37 43 46 34
100.0% 100.0% 100.0% 100.0% 100.0% 100.0%
EXCELLENT (5) 33 7 7 3 3 3
11.6% 16.7% 18.9% 7.0% 6.5% 8.8%
VERY GOOD (4) 57 11 10 5 12 4
20.0% 26.2% 27.0% 11.6% 26.1% 11.8%
GOOD (3) 49 7 6 8 5 8
17.2% 16.7% 16.2% 18.6% 10.9% 23.5%
FAIR (2) 62 7 10 8 11 9
21.8% 16.7% 27.0% 18.6% 23.9% 26.5%
POOR (1) 43 5 2 10 10 3
15.1% 11.9% 5.4% 23.3% 21.7% 8.8%
DK/NA 41 5 2 9 5 7
14.4% 11.9% 5.4% 20.9% 10.9% 20.6%
MEAN 2.90 3.22 3.29 2.50 2.68 2.81
STANDARD DEVIATION 1.32 1.34 1.25 1.31 1.33 1.18
6.7 LONG BRAND LISTS
Very often when you produce a table from a long list of brands, attributes, or other similar type mentions, you can make the table easier to read by doing any of the following:
- Producing nets of categories that are similar.
- Ranking the table with those categories mentioned most printing first.
- Suppressing rows that have no mentions.
- Collapsing rows with few mentions into an All Other category.
6.7.1 Producing Net Categories
You may want to produce an additional category which is a net of other mentions. There are a number of different ways to do this, most of which are also discussed in other parts of the manual. They are gathered here to show the difference between them. The way you choose to do the net depends on how the data is coded and personal preference.
The easiest way to create a net is inside an existing punch or numeric variable. If you use the dash it will net from the code before the dash to the code after the dash. [11^1-4] will produce one category which is the net of punches 1 through 4 and [12.2#1-10] will produce one category which is the net of numbers 1 through 10. See 4.6.1 Summary of Rules for Defining Data for more information on this. Examples in this sub-section just show the stub and the row definition for a table.
TABLE_SET= {NET_DASH:
STUB=:
11^1-4
12.2#1-10 }
ROW=: [11^1-4] WITH [12.2#1-10]
}
All the other forms of netting depend on how the data is coded. If you need to form a net of a punch category with a numeric category, two different punches from two different columns, or two different numbers from two different fields, then you will need to use the OR joiner. See “5.1.1 Logical Joiners” for more information on the OR joiner.
TABLE_SET= {NET_OR:
STUB=:
11^1 OR 53^3
11^2 OR 12.2#45
12.2#45 OR 14.2#28 }
ROW=: ([11^1] OR [53^3]) WITH &
([11^2] OR [12.2#45]) WITH &
([12.2#45] OR [14.2#28])
}
If you wish to form nets as above, but want the net category to appear in front of all the inside categories, then you will want to use the NET function. The NET function allows you to define an expression and add one additional category to the front of it which is the net of anyone who is in any of the categories in the expression.
TABLE_SET= {NET_FUNC:
STUB=:
11^1-4 OR 12.2#1-20
11^1
11^2
11^3
11^4
12.2#1-10
12.2#11-20 }
ROW=: NET([11^1//4] WITH [12.2#1-10/11-20])
}
This is the way the variable would look if you used the OR joiner instead of the NET function.
NO_NET_FUNC: ([11^1-4] OR [12.2#1-20]) WITH &
[11^1//4] WITH [12.2#1-10/11-20]
If you are netting the same code from a number of different locations, then you can use the *F modifier to net the locations. This is most useful if you have a set of answers in two different fields where the codes are the same for the two fields and you want to net all the answers together.
TABLE_SET= {NET_STAR_F:
STUB=:
11^1 OR 12^1
11^2 OR 12^2
11^3 OR 12^3
11^4 OR 12^4 }
ROW=: [11,12*F^1//4]
}
This is the way the variable would look if you used the OR joiner instead of the NET function.
NO_NET_STAR_F: ([11^1] OR [12^1]) WITH ([11^2] OR [12^2]) WITH &
([11^3] OR [12^3]) WITH ([11^4] OR [12^4])
If you need to net two variables together, such that the first category of each is netted, and then the second, and so on, then you will want to use the NET joiner. This is most useful for grids where the unaided awareness was coded as a long list and the aided awareness was coded as yes/no (1 / 2).
TABLE_SET= {NET_JOINER:
STUB=:
11^1 OR 15^1
11^2 OR 16^1
11^3 OR 17^1
11^4 OR 18^1 }
ROW=: [11^1//4] NET [15,16,17,18^1]
}
This is the way the variable would look if you used the OR joiner instead of the NET joiner.
NO_NET_JOINER: ([11^1] OR [15^1]) WITH ([11^2] OR [16^1]) WITH &
([11^3] OR [17^1]) WITH ([11^4] OR [18^1])
6.7.2 Ranking With Nets And Sub-Nets
When you rank a table with nets and sub-nets you need to assign rank levels to the different stubs, so that like items stay together under their appropriate nets and sub-nets. This is done by setting RANK_LEVEL=# on each stub item, where the # is the rank level. Highest order nets, stubs that are not under any other net, and stubs like ALL OTHER, DON’T KNOW, REFUSED, and NONE should all be set to level 1. All items directly under a net are then assigned level 2 including any sub-nets. Any items directly under a sub-net are then assigned the next level (3), including any sub-sub-nets. Continue with this process till every item has been assigned a rank level. Also be sure to force any items low or high in their level as needed by putting an L or H after the number (i.e., any Other type response should be kept low in its rank level). The default rank level is 1 unless it is specified on the EDIT statement or you have used the KEEP_RANK option which says keep this rank level in effect until you see another rank level command.
After you have assigned the rank level to each stub item, you then can invoke ranking either by using RANK_LEVEL=1 or RANK_IF_INDICATED on the EDIT statement.
When the program then ranks such an annotated stub, it first ranks all the level 1 items from high to low, forcing any high or low as indicated. It then takes all the level 2 items that were under the level 1 item that has come to the top and ranks them. It then ranks any level 3 items under the first level 2 items. It continues with this until it has no higher level to rank, and then goes to the next lower level item in the rank.
NOTE: Any given item always will stay under the first item above it with a lower number.
In the following example is the list of stubs that will be printed:
DOMESTIC (NET)
CHRYSLER
FORD
GENERAL MOTORS
OTHER DOMESTIC
EUROPEAN (NET)
BRITISH (SUB-NET)
ALFA ROMEO
JAGUAR
RANGE ROVER
ROLLS-ROYCE
STERLING
OTHER BRITISH
GERMAN (SUB-NET)
BMW
MERCEDES
VOLKSWAGEN/PORSCHE/AUDI
OTHER GERMAN
OTHER EUROPEAN (SUB-NET)
FIAT
PEUGEOT
SAAB
VOLVO
YUGO
OTHER EUROPEAN
ASIAN (NET)
JAPANESE (SUB-NET)
HONDA
MAZDA
NISSAN
SUBARU
TOYOTA
OTHER JAPANESE
OTHER ASIAN (SUB-NET)
HYUNDAI
OTHER ASIAN
NONE
DON’T KNOW
To assign the rank levels for this stub, you must first find all the major net categories and any items not in a net and assign them level 1. In this case they are the nets DOMESTIC, EUROPEAN, and ASIAN, along with NONE and DON’T KNOW. In addition, NONE and DON’T KNOW should be forced low. Next you need to look at all the items between DOMESTIC and EUROPEAN. CHRYSLER, FORD, GENERAL MOTORS, and OTHER DOMESTIC are all the same level and should be assigned level 2 with OTHER DOMESTIC being forced low. Next, look at all the items between EUROPEAN and ASIAN. Notice these items are not all at the same level so you must find all the sub-nets and items which do not belong to any sub-net and assign them level 2. OTHER EUROPEAN should be forced low. Continue on with this for the rest of the stub. See the stub below to see the rank level that was assigned for each stub item.
Often in conjunction with nets and sub-nets you want to underline the label of the nets and sub-nets. You can do this by using the UNDERLINE keyword on those stubs. You also will probably want to use the LINES_LEFT option which says skip to a new page if you do not have this many lines left on the page. This will keep a net line from printing at the bottom of the page and having all the items under it print at the top of the next page. However, setting this too high will cause a lot of blank space at the bottom of some pages.
Indenting is done automatically for levels 2 and higher. Use the EDIT option STUB_RANK_INDENT= to change the default of two characters of indention per rank level greater than 1.
NOTE: The following set of commands define a standard front end for the next set of examples.
>PRINT_FILE LISTS
~INPUT LISTS
~SET AUTOMATIC_TABLES,DROP_LOCAL_EDIT,DROP_BASE, BEGIN_TABLE_NAME=T701
~DEFINE
STUB= STUBTOP1:
TOTAL
[SUPPRESS] NO ANSWER }
TABLE_SET= {BAN2:
EDIT=: COLUMN_WIDTH=8,STUB_WIDTH=30,-COLUMN_TNA }
STUB_PREFACE= STUBTOP1
BANNER=:
| GENDER
| <———->
| TOTAL MALE FEMALE
| —– —- ——}
COLUMN=: TOTAL WITH [5^1/2]
}
~EXECUTE
TABLE_SET= BAN2
These commands are exclusive to this example.
~DEFINE
TABLE_SET= {TAB701:
HEADER=: RANKING LARGE BRAND LIST WITH NETS AND SUB-NETS}
TITLE=: AUTOMOBILE MANUFACTURERS}
LOCAL_EDIT=: RANK_LEVEL=1 }
STUB=:
[RANK_LEVEL=1,UNDERLINE,LINES_LEFT=17] DOMESTIC (NET)
[KEEP_RANK=2] CHRYSLER
FORD
GENERAL MOTORS
[RANK_LEVEL=2L] OTHER DOMESTIC
[RANK_LEVEL=1,UNDERLINE,LINES_LEFT=26] EUROPEAN (NET)
[RANK_LEVEL=2,UNDERLINE,LINES_LEFT=23] BRITISH (SUB-NET)
[KEEP_RANK=3] ALFA ROMEO
JAGUAR
RANGE ROVER
ROLLS-ROYCE
STERLING
[RANK_LEVEL=3L] OTHER BRITISH
[RANK_LEVEL=2,UNDERLINE,LINES_LEFT=17] GERMAN (SUB-NET)
[KEEP_RANK=3] BMW
MERCEDES
VOLKSWAGEN/PORSCHE/AUDI
[RANK_LEVEL=3L] OTHER GERMAN
[RANK_LEVEL=2L,UNDERLINE,LINES_LEFT=23] OTHER EUROPEAN (SUB-NET)
[KEEP_RANK=3] FIAT
PEUGEOT
SAAB
VOLVO
YUGO
[RANK_LEVEL=3L] OTHER EUROPEAN
[RANK_LEVEL=1,UNDERLINE,LINES_LEFT=26] ASIAN (NET)
[RANK_LEVEL=2,UNDERLINE,LINES_LEFT=23] JAPANESE (SUB-NET)
[KEEP_RANK=3] HONDA
MAZDA
NISSAN
SUBARU
TOYOTA
[RANK_LEVEL=3L] OTHER JAPANESE
[RANK_LEVEL=2L,UNDERLINE,LINES_LEFT=11] OTHER ASIAN (SUB-NET)
[RANK_LEVEL=3] HYUNDAI
[RANK_LEVEL=3L] OTHER ASIAN
[RANK_LEVEL=1L,LINES_LEFT=8] NONE
[RANK_LEVEL=1L] DON’T KNOW }
ROW=: [11.3^1-4/1//4/5.20/5-10/5//10/11.14/11//14/15-20/15//20/21-28/21-26/&
21//26/27,28/27/28/29/30]
}
~EXECUTE
TABLE_SET= TAB701
Only the first and last part of the table are printed here.
RANKING LARGE BRAND LIST WITH NETS AND SUB-NETS
TABLE 701
AUTOMOBILE MANUFACTURERS
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 200 109 91
100.0% 100.0% 100.0%
ASIAN (NET) 88 42 46
———– 44.0% 38.5% 50.5%
JAPANESE (SUB-NET) 86 42 44
—————— 43.0% 38.5% 48.4%
NISSAN 23 11 12
11.5% 10.1% 13.2%
HONDA 21 11 10
10.5% 10.1% 11.0%
TOYOTA 13 7 6
6.5% 6.4% 6.6%
SUBARU 9 4 5
4.5% 3.7% 5.5%
MAZDA 8 5 3
4.0% 4.6% 3.3%
OTHER JAPANESE 23 9 14
11.5% 8.3% 15.4%
OTHER ASIAN (SUB-NET) 5 2 3
——————— 2.5% 1.8% 3.3%
HYUNDAI 5 2 3
2.5% 1.8% 3.3%
OTHER ASIAN – – –
– – – – – – – – – – – – – – – – – – – – –
DOMESTIC (NET) 46 25 21
————– 23.0% 22.9% 23.1%
CHRYSLER 20 13 7
10.0% 11.9% 7.7%
GENERAL MOTORS 20 10 10
10.0% 9.2% 11.0%
FORD 12 5 7
6.0% 4.6% 7.7%
OTHER DOMESTIC – – –
NONE 37 20 17
18.5% 18.3% 18.7%
DON’T KNOW 22 15 7
11.0% 13.8% 7.7%
6.7.3 Suppressing Blank Rows in a Large List
To suppress a row with no mentions in it, you need only to do is use the option MINIMUM_FREQUENCY=1 on the EDIT or LOCAL_EDIT statement to then suppress all rows which do not have at least one respondent in them. If you have rows that you want to print even though the frequency is zero, like an OTHER category or DON’T KNOW category (that you always want to show was calculated on the table), then you will want to use the option -MINIMUM_FREQUENCY on all those stubs. Also be aware that if you have weighted data, then you may not want to set MINIMUM_FREQUENCY=1, but rather to some other value like either 0.5 or 0.01 depending upon what you want to do with a row that has less than 0.5 as the frequency. This frequency would round to zero, but there is at least one valid respondent in that category. A value of 0.5 would suppress this row, while 0.01 would print it with a dash.
This example uses comment labels and stub indention to mark the different groupings although no net categories have been defined.
~DEFINE
TABLE_SET= TAB702:
HEADER=: SUPPRESSING ZERO ROWS IN A LARGE BRAND LIST WITH COMMENTS }
LOCAL_EDIT=: MINIMUM_FREQUENCY=1 }
STUB=:
[COMMENT,UNDERLINE] DOMESTIC
[STUB_INDENT=2] CHRYSLER
[STUB_INDENT=2] FORD
[STUB_INDENT=2] GENERAL MOTORS
[STUB_INDENT=2,-MINIMUM_FREQUENCY] OTHER DOMESTIC
[COMMENT,UNDERLINE] EUROPEAN
[COMMENT,STUB_INDENT=2,UNDERLINE] BRITISH
[STUB_INDENT=4] ALFA ROMEO
[STUB_INDENT=4] JAGUAR
[STUB_INDENT=4] RANGE ROVER
[STUB_INDENT=4] ROLLS-ROYCE
[STUB_INDENT=4] STERLING
[STUB_INDENT=4,-MINIMUM_FREQUENCY] OTHER BRITISH
[COMMENT,STUB_INDENT=2,UNDERLINE,LINES_LEFT=16] GERMAN
[STUB_INDENT=4] BMW
[STUB_INDENT=4] MERCEDES
[STUB_INDENT=4] VOLKSWAGEN/PORSCHE/AUDI
[STUB_INDENT=4,-MINIMUM_FREQUENCY] OTHER GERMAN
[COMMENT,STUB_INDENT=2,UNDERLINE,LINES_LEFT=22] OTHER EUROPEAN
[STUB_INDENT=4] FIAT
[STUB_INDENT=4] PEUGEOT
[STUB_INDENT=4] SAAB
[STUB_INDENT=4] VOLVO
[STUB_INDENT=4] YUGO
[STUB_INDENT=4,-MINIMUM_FREQUENCY] OTHER EUROPEAN
[COMMENT,UNDERLINE,LINES_LEFT=25] ASIAN
[COMMENT,STUB_INDENT=2,UNDERLINE,LINES_LEFT=22] JAPANESE
[STUB_INDENT=4] HONDA
[STUB_INDENT=4] MAZDA
[STUB_INDENT=4] NISSAN
[STUB_INDENT=4] SUBARU
[STUB_INDENT=4] TOYOTA
[STUB_INDENT=4,-MINIMUM_FREQUENCY] OTHER JAPANESE
[COMMENT,STUB_INDENT=2,UNDERLINE,LINES_LEFT=10] OTHER ASIAN
[STUB_INDENT=4] HYUNDAI
[STUB_INDENT=4,-MINIMUM_FREQUENCY] OTHER ASIAN
[-MINIMUM_FREQUENCY,LINES_LEFT=8] NONE
[-MINIMUM_FREQUENCY] DON’T KNOW }
ROW=: [11.3^1//30]
}
~EXECUTE
TABLE_SET= TAB702
Only the first and last part of the table are printed here.
SUPPRESSING ZERO ROWS IN A LARGE BRAND LIST WITH COMMENTS
TABLE 702
AUTOMOBILE MANUFACTURERS
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 200 109 91
100.0% 100.0% 100.0%
DOMESTIC
——–
CHRYSLER 20 13 7
10.0% 11.9% 7.7%
FORD 12 5 7
6.0% 4.6% 7.7%
GENERAL MOTORS 20 10 10
10.0% 9.2% 11.0%
OTHER DOMESTIC – – –
EUROPEAN
——–
BRITISH
——-
JAGUAR 1 1 –
0.5% 0.9%
RANGE ROVER 4 2 2
2.0% 1.8% 2.2%
STERLING 5 3 2
2.5% 2.8% 2.2%
OTHER BRITISH 4 2 2
2.0% 1.8% 2.2%
GERMAN
——
BMW 9 5 4
4.5% 4.6% 4.4%
MERCEDES 11 6 5
5.5% 5.5% 5.5%
VOLKSWAGEN/PORSCHE/AUDI 13 9 4
6.5% 8.3% 4.4%
OTHER GERMAN 2 1 1
1.0% 0.9% 1.1%
– – – – – – – – – – –
NONE 37 20 17
18.5% 18.3% 18.7%
DON’T KNOW 22 15 7
11.0% 13.8% 7.7%
6.7.4 Collapsing Low Mentions into another Category
Sometimes when you have a long list you not only want to suppress blank rows, but you may want to suppress rows that contain less than a certain frequency or percentage value. If you suppress these rows however, you are actually removing numbers from the table and will probably want to print them in what is called a collapsed category at the bottom of the table. This allows you to take long lists and shorten them so that they are easier to read because they only have the top mentions and are not cluttered up with lots of rows with only a couple of mentions in each.
Whenever a row is suppressed due to the MINIMUM_FREQUENCY or MINIMUM_PERCENT option, the system sums all the numbers from that row in a system row called SUPPRESSED. You can then use the PRINT_ROW option to print that row at the bottom of the table.
In the example below all rows with less than 5 percent of the total are being suppressed along with all the Other categories to produce one global Other category. MINIMUM_PERCENT=5 is set on the EDIT and then it is set to 100 on the Other categories to make sure they are suppressed. It is also set to -MINIMUM_PERCENT on the DON’T KNOW and REFUSED to make sure they do print even if they do not have many mentions.
NOTE: This table is also ranked to highlight the fact that no row with under 5 percent mentions is printed.
~DEFINE
TABLE_SET= TAB703:
HEADER=: COLLAPSING LOW MENTIONS INTO AN ALL OTHER CATEGORY ON A LARGE BRAND LIST}
LOCAL_EDIT=: MINIMUM_PERCENT=5,RANK_LEVEL=1}
STUB=:
CHRYSLER
FORD
GENERAL MOTORS
[MINIMUM_PERCENT=100] OTHER DOMESTIC
ALFA ROMEO
JAGUAR
RANGE ROVER
ROLLS-ROYCE
STERLING
[MINIMUM_PERCENT=100] OTHER BRITISH
BMW
MERCEDES
VOLKSWAGEN/PORSCHE/AUDI
[MINIMUM_PERCENT=100] OTHER GERMAN
FIAT
PEUGEOT
SAAB
VOLVO
YUGO
[MINIMUM_PERCENT=100] OTHER EUROPEAN
HONDA
MAZDA
NISSAN
SUBARU
TOYOTA
[MINIMUM_PERCENT=100] OTHER JAPANESE
HYUNDAI
[MINIMUM_PERCENT=100] OTHER ASIAN
[PRINT_ROW=SUPPRESS,RANK_LEVEL=1L,-MINIMUM_PERCENT] ALL OTHER COMPANIES
[RANK_LEVEL=1L,-MINIMUM_PERCENT ] NONE
[RANK_LEVEL=1L,-MINIMUM_PERCENT ] DON’T KNOW }
ROW=: [11.3^1//30]
}
~EXECUTE
TABLE_SET= TAB703
Here is the table that is printed.
COLLAPSING LOW MENTIONS INTO AN ALL OTHER CATEGORY ON A LARGE BRAND LIST
TABLE 703
AUTOMOBILE MANUFACTURERS
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 200 109 91
100.0% 100.0% 100.0%
NISSAN 23 11 12
11.5% 10.1% 13.2%
HONDA 21 11 10
10.5% 10.1% 11.0%
CHRYSLER 20 13 7
10.0% 11.9% 7.7%
GENERAL MOTORS 20 10 10
10.0% 9.2% 11.0%
VOLVO 16 8 8
8.0% 7.3% 8.8%
VOLKSWAGEN/PORSCHE/AUDI 13 9 4
6.5% 8.3% 4.4%
TOYOTA 13 7 6
6.5% 6.4% 6.6%
FORD 12 5 7
6.0% 4.6% 7.7%
MERCEDES 11 6 5
5.5% 5.5% 5.5%
ALL OTHER COMPANIES 95 49 46
47.5% 45.0% 50.5%
NONE 37 20 17
18.5% 18.3% 18.7%
DON’T KNOW 22 15 7
11.0% 13.8% 7.7%
6.7.5 Printing Subtotal Rows
Five subtotal rows are maintained by the system, which can help you create summary rows, or other types of rows on the printed table. You can store the information of any row into a given subtotal by using either the stub option SUBTOTAL# or KEEP_SUBTOTAL#. The # can be a number 1 to 5 or blank (the default) which will use subtotal 1. SUBTOTAL# will only add this row into the SUBTOTAL, while KEEP_SUBTOTAL will continue to add rows into the subtotal until another subtotal stub option is used. A given row can be added into multiple subtotal rows. To print a subtotal you need to use the PRINT_ROW option and set it equal to either SUBTOTAL#_CLEAR or SUBTOTAL#_NO_CLEAR. If you set PRINT_ROW equal to SUBTOTAL#_CLEAR it prints and clears the subtotal value while SUBTOTAL#_NO_CLEAR will print it and not clear it. The default is to clear any subtotal when you print it.
The following is an example of using two different subtotal options to create summary rows at the bottom of the table.
~DEFINE
TABLE_SET= TAB704:
HEADER=: USING SUBTOTALING OPTIONS TO PRODUCE NET CATEGORIES }
TITLE=: RATING OF BRAND X }
STUB=:
[SUBTOTAL1] VERY GOOD
[SUBTOTAL1] GOOD
FAIR
[SUBTOTAL2] POOR
[SUBTOTAL2] VERY POOR
DON’T KNOW
[PRINT_ROW=SUBTOTAL1] SUBTOTAL GOOD
[PRINT_ROW=SUBTOTAL2] SUBTOTAL POOR }
ROW=: [21^5//1/X] }
~EXECUTE
TABLE_SET= TAB704
Here is the table that is printed.
USING SUBTOTALING OPTIONS TO PRODUCE NET CATEGORIES
TABLE 704
RATING OF BRAND X
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 200 109 91
100.0% 100.0% 100.0%
VERY GOOD 33 21 12
16.5% 19.3% 13.2%
GOOD 57 27 30
28.5% 24.8% 33.0%
FAIR 53 36 17
26.5% 33.0% 18.7%
POOR 32 14 18
16.0% 12.8% 19.8%
VERY POOR 11 5 6
5.5% 4.6% 6.6%
DON’T KNOW 14 6 8
7.0% 5.5% 8.8%
SUBTOTAL GOOD 90 48 42
45.0% 44.0% 46.2%
SUBTOTAL POOR 43 19 24
21.5% 17.4% 26.4%
Another possible use of the subtotalling feature is to use it to print a row twice on a given table. This can be useful for printing multiple vertical percentages for each row in the table. The example below demonstrates printing a vertical percentage both of the total row and those who rated the brand.
~DEFINE
TABSET= TAB705:
HEADER=: USING SUBTOTALING OPTIONS TO PRINT MULTIPLE VERTICAL PERCENTAGES }
TITLE=: RATING OF BRAND X }
STUB=:
[SUBTOTAL1, VERTICAL_PERCENT=T] THOSE WHO RATED BRAND X
[PRINT_ROW=SUBTOTAL1, SKIP_LINES=0, -FREQUENCY, VERTICAL_PERCENT=1]
[SUBTOTAL1, VERTICAL_PERCENT=T] VERY GOOD
[PRINT_ROW=SUBTOTAL1, SKIP_LINES=0, -FREQUENCY, VERTICAL_PERCENT=1]
[SUBTOTAL1, VERTICAL_PERCENT=T] GOOD
[PRINT_ROW=SUBTOTAL1, SKIP_LINES=0, -FREQUENCY, VERTICAL_PERCENT=1]
[SUBTOTAL1, VERTICAL_PERCENT=T] FAIR
[PRINT_ROW=SUBTOTAL1, SKIP_LINES=0, -FREQUENCY, VERTICAL_PERCENT=1]
[SUBTOTAL1, VERTICAL_PERCENT=T] POOR
[PRINT_ROW=SUBTOTAL1, SKIP_LINES=0, -FREQUENCY, VERTICAL_PERCENT=1]
[SUBTOTAL1, VERTICAL_PERCENT=T] VERY POOR
[PRINT_ROW=SUBTOTAL1, SKIP_LINES=0, -FREQUENCY, VERTICAL_PERCENT=1]
[SUBTOTAL1, VERTICAL_PERCENT=T] DON’T KNOW }
T5=:\N
FIRST PERCENTAGE IS OFF OF TOTAL RESPONDENTS
SECOND PERCENTAGE IS OFF THOSE WHO RATED THE BRAND }
ROW=: [21^1-5/5//1/X]
}
~EXECUTE
TABSET= TAB705
Here is the table that is printed.
USING SUBTOTALING OPTIONS TO PRINT MULTIPLE VERTICAL PERCENTAGES
TABLE 705
RATING OF BRAND X
GENDER
<———->
TOTAL MALE FEMALE
—– —- ——
TOTAL 200 109 91
100.0% 100.0% 100.0%
THOSE WHO RATED BRAND X 186 103 83
93.0% 94.5% 91.2%
100.0% 100.0% 100.0%
VERY GOOD 33 21 12
16.5% 19.3% 13.2%
17.7% 20.4% 14.5%
GOOD 57 27 30
28.5% 24.8% 33.0%
30.6% 26.2% 36.1%
FAIR 53 36 17
26.5% 33.0% 18.7%
28.5% 35.0% 20.5%
POOR 32 14 18
16.0% 12.8% 19.8%
17.2% 13.6% 21.7%
VERY POOR 11 5 6
5.5% 4.6% 6.6%
5.9% 4.9% 7.2%
DON’T KNOW 14 6 8
7.0% 5.5% 8.8%
FIRST PERCENTAGE IS OFF OF TOTAL RESPONDENTS
SECOND PERCENTAGE IS OFF THOSE WHO RATED THE BRAND
6.8 MASTER-TRAILER PROCESSING
When you collect general information in a master questionnaire, collect additional information in trailer questionnaires, and then generate tables from those related questionnaires, it is called Master-Trailer processing. In the following example, household information has been collected in the master questionnaire, and then information about each person and the trips they take out of the house has been collected in the trailer questionnaire. Using Master-Trailer processing, you can generate tables based on households, individuals, or a combination of information from both groups.
This example uses two data files: one for household information and one for individual trip destinations during a test week. Household information includes the home address, which is the origin of all trips. The destinations of up to four persons in the household, a maximum of three trip destinations per person has been recorded in the trailer questionnaire. The number weekday and weekend trips to each destination has also been recorded.
This example uses the spec file trips3.spx that creates three tables. Information about the household can be reported and combined with destination data using both data files as both files have a Household ID# as part of the Case ID#. The data files are simple and concise and contain information about only those destinations actually visited.
The data files for the trips example are organized as follows:
The hhdata.tr (household data file) is 80 columns long.
Item Location
—- ——–
Household ID# 1.5
Origin of Trip 50.4
The indata.tr (individual destination data file) is 240 columns long.
Item Location
—- ——–
Household ID# 1.5
Person # 6
Trip # 7
Destination 130.4
Number of Weekday Trips 231.2
Number of Weekend Trips 233.2
Important keywords in this example include:
• ~INPUT $ studyname=<name>
The phantom ~INPUT statement uses a “studyname” so that the phantom file can assume the CASE_WIDTH, WORK_LENGTH, TOTAL_LENGTH, TEXT_WIDTH, TEXT_START, and CASE_START from the input file with that “studyname”.
• STUDYNAME![data description]
This refers to a location or variable in a specific input file associated with the studyname. There is also STUDYNAME^[data description] for identifying locations or variables in a specific dbfile.
• READ_PROCEDURE=<name> ON studyname
This executes the specified procedure on the input file associated with the studyname.
• WHILE MATCHING “indata” indata![1.5$]
This command lets you do hierarchical jobs (such as master-trailer processing) on sets of files where the secondary files are sorted in order by the relevant match fields. Here is how WHILE MATCHING is used in the following example:
- Start with the primary file (hhdata).
- Do a choosefile on the secondary file (indata), which is the file associated with the studyname “indata”.
- Starting with the first case look for the matching field indata![1.5$] to match hhdata!case_id. The matching field can be any expressions resulting in a string (for example, studyname![1.5$] JOIN studyname![8$]).
- If the matching field is less, then read forward in the secondary file. If it is greater, then quit the loop. This only works if the file given by studyname is sorted by the matching field.
- Now execute the interior of the WHILE …. ENDWHILE for every case that passes any SELECT= on the ~INPUT statement and also has a matching field with the same CASE_ID in the primary file.
- Do a choosefile back to the primary file and go on.
~COMMENT trips3.spx
~DEFINE PROC={proc1:
WHILE MATCHING “indata” indata![1.5$]
COPY inwork![130.4] = indata![130.4] ”destinations
COPY inwork![231.2] = indata![231.2] ”total weekday trips
COPY inwork![233.2] = indata![233.2] ”total weekend trips
DO_TABLES ENDWHILE
}
trips: CFUNC(-34,&
inwork![130.4#707/708/726/727/728/729/730/746/747/748/780/783/784])
TABSET= {global:
FOOTER={: =Page #page_number#}
HEADER={: =2015 County Transit Survey
December 2015\n}
GLOBAL_EDIT={: -COLTNA, CALL_TABLE=””, -TCON, PDEC=0, PUTCHAR=-z–
SUPPRESS_ROWS_BASE=1 }
}
TABSET={ban1:
EDIT={: CWIDTH=5, SWIDTH=26}
BANNER={:
| Origins
| =================================================
|Total 707 708 725 726 727 729 730 746 780 783
|—– —- —- —- —- —- —- —- —- —- —-
}
COL=: NET([50.4#707/708/725/726/727/729/730/746/780/783])
}
TABSET= {qwkday:
LOCAL_EDIT={: MINFREQ=1, -ROWTNA, -VPER, SKIP=0 }
TITLE={: Trips (one-way) from Glenview Estates Area\n }
TITLE_4={: Total weekday trips
}
STUB={:
Total trips
Estates
[comment,underline,skiplines=1] Destinations within Glenview
[stub_indent=4] 707
[stub_indent=4] 708
[stub_indent=4] 726
[stub_indent=4] 727
[stub_indent=4] 728
[stub_indent=4] 729
[stub_indent=4] 730
[stub_indent=4] 746
[stub_indent=4] 747
[stub_indent=4] 748
[stub_indent=4] 780
[stub_indent=4] 783
[stub_indent=4] 784
Outside Glenview Estates
}
ROW=: trips * inwork![231.2]
}
TABSET= {qwkend=qwkday:
TITLE_4={: Total weekend trips }
ROW=: trips * inwork![233.2]
}
TABSET= {qtotal=qwkday:
TITLE_4={: Total trips }
ROW=: trips * (inwork![231.2] ++ inwork![233.2])
}
~INPUT hhdata STUDY_NAME=hhdata NUMBER_INPUT_BUFFERS=3
~INPUT indata STUDY_NAME=indata NEW_BUFFER
~INPUT $ STUDY_NAME=inwork NEW_BUFFER
>PRINTFILE trips3 PAGE_WIDTH=80
~EXC
TABSET=global
READ_PROC=proc1 on hhdata
TABSET=ban1
TABSET=qwkday TAB=*
TABSET=qwkend TAB=*
TABSET=qtotal TAB=*
~END
2015 County Transit Survey
December 2015
Trips (one-way) from Glenview Estates Area
Total weekday trips
Origins
================================================
Total 707 708 725 726 727 729 730 746 780 783
—– —- —- —- —- —- —- —- —- —- —-
Total trips 561 47 92 18 16 28 33 133 30 159 5
Destinations within
Glenview Estates
——————-
707 17 – – – – – – – – 17 –
708 3 – – – – – – 3 – – –
726 5 – – – – – – – – 5 –
727 7 – 7 – – – – – – – –
729 15 – 10 – – – – 3 – 2 –
746 5 – – – – – – 5 – – –
747 12 – 8 – – – – – – 4 –
Outside Glenview Estates 497 4 7 67 18 16 28 33 122 30 131 5
Page 1
2015 County Transit Survey
December 2015
Trips (one-way) from Glenview Estates Area
Total weekend trips
Origins
================================================
Total 707 708 725 726 727 729 730 746 780 783
—– —- —- —- —- —- —- —- —- —- —-
Total trips 70 9 20 2 2 4 2 10 5 16 –
Destinations within
Glenview Estates
——————-
707 3 – – – – – – – – 3 –
726 1 – – – – – – – – 1 –
727 4 – 4 – – – – – – – –
729 4 – 3 – – – – – – 1 –
746 2 – – – – – – – 2 – –
747 1 – 1 – – – – – – – –
780 2 – 2 – – – – – – – –
Outside Glenview Estates 53 9 10 2 2 4 2 10 3 11 –
Page 2
2015 County Transit Survey
December 2015
Trips (one-way) from Glenview Estates Area
Total trips
Origins
=================================================
Total 707 708 725 726 727 729 730 746 780 783
—– —- —- —- —- —- —- —- —- —- —-
Total trips 631 56 112 20 18 32 35 143 35 175 5
Destinations within
Glenview Estates
——————-
707 20 – – – – – – – – 20 –
708 3 – – – – – – 3 – – –
726 6 – – – – – – – – 6 –
727 11 – 11 – – – – – – – –
729 19 – 13 – – – – 3 – 3 –
746 7 – – – – – – 5 2 – –
747 13 – 9 – – – – – – 4 –
780 2 – 2 – – – – – – – –
Outside Glenview Estates 550 56 77 20 18 32 35 132 33 142 5
Page 3