Technical Documentation ßßßßßßß ßß ßß ßßßßßß ßßßßßß ßßß ßßßßß ßßßßßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßßßßßß ßßß ßß ßßßßßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßßßßßßß ßß ßß ßß ßßßßßßß ßß ßß ßß ßß ßß ßß ßß ßßßßß ßß a public-domain data extraction utility designed to work with Census Bureau files in dBase III+ format (release 1.5f) To purchase CD-ROMs with Census The EXTRACT program is distributed Bureau statistics usable with on Economic Census and other CD-ROMs. this program, contact-- Software and auxiliary file Customer Services updates are available through the Bureau of the Census Census Bureau electronic Washington, D.C. 20233 bulletin board: 301/457-2310 301/457-4100 voice: 301/457-1242 TABLE OF CONTENTS Description of the Program . . . . . 1 Advanced Topics. . . . . . . . . . 24 Auxiliary files and functions . 24 Getting Started. . . . . . . . . . . 1 Setting up an EXTRACT menu. . . 25 File manipulation . . . . . . . 26 Stepping Through the Program . . . . 4 How to select records with Specify drives on your machine. . 4 conditional clauses . . . . . 32 Choose a catalog. . . . . . . . . 5 Displaying secondary files. . . 33 Main help screen. . . . . . . . . 6 How to get EXTRACT to work with Select a database . . . . . . . . 6 other dBase files . . . . . . 33 Main menu . . . . . . . . . . . . 7 1) Select Items . . . . . . . 8 Notes on Use of EXTRACT with-- 2) Select Records . . . . . 10 1987 Economic Censuses. . . . . 35 3) Add Labels . . . . . . . 15 1987 Census of Agriculture. . . 36 4) Manipulate Files . . . . 16 U.S. Exports and Imports. . . . 38 5) Format Options . . . . . 17 County Business Patterns. . . . 38 6) Display to Screen. . . . 18 County and City Data Book, 1988 39 7) Print. . . . . . . . . . 21 USA Counties, 1992. . . . . . . 39 8) Extract Data to a File . 21 1990 Census: STF 1A, 3A, etc. . 39 9) Return to File Selection Menu . . . . . . . . . 23 Getting Assistance . . . . . . . . 43 10) Advanced Options . . . . 23 INDEX . . . . . . . . . . . . . . 44 November, 1992 (minor revisions March 1993 and January 1995) ##### # # ##### #### # ### ##### # # # # # # # # # # # #### # # #### # # # # # # # # # # ##### # # # ##### # # # # # # # ### # DESCRIPTION OF THE PROGRAM EXTRACT selects, displays and extracts data from dBase III+ files without using dBase III+TM. While the program is primarily designed to operate on files issued from the Census Bureau's economic and agriculture censuses on CD-ROM, it can work with any dBase III+ for which appropriate "catalog" and "data diction- ary" files have been constructed (see Advanced Topics, below). EXTRACT prompts the user through the selection of a file, the selection of data items and records, the addition of text labels to displays and printouts, and the extraction of data to a new file. File output may be saved to a hard disk or floppy in any of three formats so that data may be imported into other programs, such as spreadsheets, statistical packages, graphics software and a wide variety of other packages. While many packages, like Lotus 1-2-3TM, can convert dBase III+ files using their own utilities, they may not be able to deal with files as large as those distributed by the Census Bureau, nor may they be able to incorporate descrip- tive information from external data dictionaries. EXTRACT pulls all of these elements together for the user. Help screens can provide definitions of concepts. The program includes limited computational capabilities, although users planning to load EXTRACT output into a spreadsheet or statistical package may prefer to defer computations to the applications software. A general note on program speed: Most of the files this program has been designed to work on are very large, and CD-ROM readers are relatively slow devices, much slower than hard disks. Patience is called for when working with large files. +------------------------------------------------------------------------------+ | Tutorials are available. This document is structured as a reference manual, | | systematically describing each feature of the program. If you would prefer | | to learn the software through a series of exercises with particular CD-ROMs, | | read the EXTUTOR text files distributed with the EXTRACT program. EXTUTOR1 | | and EXTUTOR2 illustrate economic census files; EXTUTOR4 works through the | | use of 1990 census CD-ROMs, focussing on STF 1A; EXTUTOR5 deals with EEO CDs.| +------------------------------------------------------------------------------+ GETTING STARTED Hardware and Software Requirements EXTRACT works best on a computer equipped with a hard disk. During data extrac- tion, the system may need to create temporary files, and thus works best when there is extra disk space available. 1 EXTRACT requires about 450 kb of system memory (RAM) under DOS 3.x, up to 520 kb under DOS 5. A 512- or 640-kb machine should be fine as long as memory- resident programs and CD-ROM or network drivers do not substantially reduce available memory. (DOS 5.0 users should load DOS and CD-ROM drivers "high".) If you are using Census Bureau CD-ROMs issued in 1989 or later, your system must also have MicroSoft Extensions 2.0 or later. One way to check is to look for the file MSCDEX.EXE on your computer and make sure it has a date of 1988 or later. Installing Files on a Hard Disk To install EXTRACT from a diskette onto a hard disk, copy the EXTRACT program and related files to any desired directory on your hard disk, and create a separate subdirectory for work space. For example, at the C> prompt, type MD \EXTRACT CD \EXTRACT MD WORK Insert the EXTRACT program diskette in the A: drive and type A:EXT15x (x varies with the current version) EXT15x is a program that creates uncompressed copies of EXTRACT.EXE (version 1.5x), related files, and EXTRACT.DOC, an ASCII text version of this documenta- tion, and stores them on your current (default) drive and directory. If you obtained this program on a CD-ROM, the program INSTALL.BAT will copy EXTRACT and related files to the drive and directory you specify. The syntax is [CD]:\EXTRACT\INSTALL [CD]: [DRIVE]:\[DIRECTORY] where [CD] is the drive letter for your CD-ROM and [DRIVE]:\[DIRECTORY] is the drive letter and directory on your hard disk where you want EXTRACT and related files to reside. Steps for all users. Check whether you have a "CONFIG.SYS" file in the root directory (type DIR C:\CONFIG.SYS). If you do not, create one at the C> prompt by typing COPY CON CONFIG.SYS FILES = 20 BUFFERS = 20 then press the key followed by . If you already have a config.sys file, make sure that it contains lines which say FILES = 20 and BUFFERS = 20 (or higher number), or change it with a text editor. If you are using a CD-ROM that does not contain EXTRACT-compatible auxiliary files (e.g., 1988 County and City Data Book, Imports, Exports, 1990 census discs) you must install files from a separate Auxiliary Files diskette. Put the disk it in the A: drive, display a directory (DIR A:) and type out any file with a title including "READ.ME", for example "FTD_READ.ME". That file will contain instructions on loading the files into appropriate directories on your hard disk. EXTRACT requires about 450 kilobytes of random access memory (RAM) under DOS 3.x, or up to 520 kb under DOS 5. If the program aborts with a "run error", 2 you do not have enough memory available to complete the requested task. To determine the amount of RAM available, run the DOS CHKDSK program, and examine the last line of output. If you have insufficient memory available, you may need to remove terminate-and-stay-resident programs (TSRs) or network drivers and reboot. If you have more than 640 kilobytes of RAM and use a memory manager such as QEMM(tm), you will need to enter the following command at the DOS prompt before running EXTRACT. (Saving parameters, as discussed below, automatically adds this line to batch files.) SET CLIPPER=E000 If you do not have access to a CD-ROM, you may try out the program with test data and auxiliary files contained in TESTDATA.EXE and TEST_AUX.EXE, available separately. When uncompressed, these files from the 1987 Census of Retail Trade for Arizona require about 665 kb on your hard disk. See instructions in the corresponding TST_READ.ME file. If you have had to create or change your config.sys file, you will need to reboot before proceeding further. Starting the Program Make the directory with the EXTRACT files the default directory (e.g., by typing CD \EXTRACT), then type EXTRACT (If nothing happens, type EXTRCT14 to uncompress the files before proceeding.) Once the program is running, using EXTRACT should be reasonably self-ex- planatory, since most options are presented as items on a menu. On-line help is available at most points during the program by pressing the key. 3 STEPPING THROUGH THE PROGRAM Specify Drives on Your Machine Since EXTRACT is designed to work in a variety of different environments, with data residing on a floppy disk, hard disk, or CD-ROM, the program normally asks first for the location of the files it requires. If parameters have already been saved (see below), this screen is skipped. +------------------------------------------------------------------------------+ | SPECIFY DRIVES ON YOUR MACHINE | | | | | | Enter DRIVE letter for CD-ROM (blank if none), then press | | : l: | | | | Enter DRIVE and DIRECTORY for WORKSPACE on your hard disk | | e.g., 'C:\EXTRACT\WORK' | | : c:\extract\work | | Enter DRIVE and DIRECTORY for AUXILIARY FILES | | : c:\extract\cbpauxil | | | | Is this information correct? y | | es, o, ave, uit | | | | | | | | Enter DRIVE and DIRECTORY information in the following format: | | :\[] -- then press | +------------------------------------------------------------------------------+ If you are using a CD-ROM, specify the drive letter. Many CD-ROMs are desig- nated the L: drive; if so, simply type L: and press . Next, the system asks you to specify a drive and directory for work space, e.g. C:\EXTRACT\WORK. If the CD-ROM does not include catalog (*.CTG) and data dictionary (*.DCT) files for the data, the system will ask for their location; for economic census, agriculture census, and recent County Business Patterns discs, this third prompt does not appear. Since EXTRACT will not find the files it needs unless drives are correctly specified, the system asks "Is this information correct?" before proceeding. Type "Y" or "y" for yes. If the response is "N" or "n", the system will ask each of the questions over again. Entering "S" or "s" will ave the drive and directory information to a batch file. Typing "Q" at this point will quit the program. Saving Parameters When the ave option is exercised, the system prompts you to name a batch file, e.g., EXCBP for County Business Patterns. Typing that name (EXCBP) rather than EXTRACT in the future will skip this drive selection screen. You should have a separate name for each different setting you need. Typing EXMENU will list each option you have created. (See also the discussion of command-line parameters on page 23.) Selecting a Master Catalog EXTRACT relies on a "master catalog", usually named MASTER.CTG, to direct file selection activities. Some sets of auxiliary files have more than one master catalog. For example the 1990 census auxiliaries include a MSTRST1A.CTG for STF 1A CD-ROMs, MSTRST1B.CTG for STF 1B, etc. If 4 applicable, a small window will open in the lower left of the screen and you will be prompted to select the appropriate master catalog file. If you are using a CD-ROM, EXTRACT will check for the file MSCDEX.EXE in your root directory. If it finds an out-of-date version, or if it fails to find the file at all, EXTRACT will display a message to that effect, but you may be able to ignore the message if the program then works correctly, since the operative MSCDEX.EXE may be in a subdirectory on your hard disk. Choose a Catalog Files are grouped into "catalogs" or groups of similar files. Highlight the appropriate one with the up or down arrow keys and select with . +------------------------------------------------------------------------------+ | CHOOSE A CATALOG | | | |1. Position cursor by using , , < >, or < > | |2. Press to select a catalog. | | | |CATALOG DESCRIPTION | |==============================================================================| |RC87A1__ | 1987 RETAIL TRADE: Detailed Stat's for State, Places,Counties,MSAs| |RC87A2__ | 1987 Retail Trade: State Bridge Tables (old/new SIC) + ratios | |RC87A3__ | 1987 Retail Trade: Summary Statistics for State, Counties & Places| |RC87A4__ | 1987 Retail Trade: Rankings for Counties and Places | |RC87N1__ | 1987 Retail Trade: Employers and Nonemployers--U.S. by SIC | |RC87N2__ | 1987 Retail Trade: Nonemployer Statistics--States & MSAs by SIC | |RC87N3__ | 1987 Retail Trade: Nonemployer Statistics--States, Counties,Places| |RC87S1__ | 1987 Retail Trade: Establishment and Firm Size--U.S. | |RC87L___ | 1987 Retail Trade: Merchandise Line Sales--U.S., States, MSAs | |WC87A1__ | 1987 WHOLESALE TRADE: State, Detailed Stat's--Places,Counties,MSAs| |WC87A2__ | 1987 Wholesale Trade: Detailed State-Total,Ratios,Bridge (old SIC)| |WC87A3__ | 1987 Wholesale Trade: Summary Statistics for Counties and Places | |WC87A4__ | 1987 Wholesale Trade: Rankings for Counties and Places | |WC87S1__ | 1987 Wholesale Trade: Establishment and Firm Size--U.S. | | | |To estrict entire session to files including a particular State, press R. | +------------------------------------------------------------------------------+ If, based on prior experience, you expect the following file-selection menu to include a long list of states, you may wish to use the estrict option, which prompts you to enter the 2-character postal abbreviation for the desired state. Unless respecified, all subsequent file selections will be limited to only those files that include data for the specified state. If, at this point, you discover that you have not correctly specified drives (or if you have changed CD-ROMs while the program is running), you may press to return the previous drive-specification screen. You may return to this menu to choose a new file at any time from the main menu, using option 9, "Return to file selection menu". 5 Main Help Screen The program automatically displays a general help screen which is customized to the catalog of files you have selected. You may wish to print it out with , and keep in handy. This same screen can be brought up any time you are at the main menu, by pressing for help. (Elsewhere in the program, the key will call up help appropriate to the screen you are in.) Select a Database Select any one database file. This screen is skipped if the session has been restricted to a particular state and there is only one file in the catalog that qualifies. If, at this point, you wish to return to the previous screen to select a dif- ferent catalog, you may do so by pressing . +------------------------------------------------------------------------------+ | SELECT A DATABASE | | | |1. Position cursor by using , , < >, or < > | |2. Press to select a database. | | | |FILE DESCRIPTION | |==============================================================================| |RC87A1US | UNITED STATES by kind of business, and State and MSA totals | |RC87A1XS | U.S. and States by kind of business | |RC87A1MM | MSAs, CMSAs, and PMSAs by kind of business | |RC87A1AL | Alabama | |RC87A1AK | Alaska | |RC87A1AZ | Arizona | |RC87A1AR | Arkansas | |RC87A1CA | California | |RC87A1CO | Colorado | |RC87A1CT | Connecticut | |RC87A1DE | Delaware | |RC87A1DC | District of Columbia | |RC87A1FL | Florida | |RC87A1GA | Georgia | |RC87A1HI | Hawaii | |RC87A1ID | Idaho | +------------------------------------------------------------------------------+ After you have selected a file, the message "Reading in data dictionary" alerts you to the fact that the system is setting up the auxiliary files for the selected file. Depending on the number of items in the file and the speed of your hardware, this step can take up to a minute. 6 Main Menu ============================================================================== | | | MAIN MENU | | | | | | | | 1. Select ITEMS 6. Display to Screen | | | | 2. Select RECORDS 7. Print | | | | 3. Add LABELS 8. EXTRACT DATA to a file | | | | 4. Manipulate Files 9. Return to FILE SELECTION menu | | | | 5. Format options 10. Advanced options | | | | | | ( Q to QUIT ) ( D for Definitions ) ( for Help ) | | | | | | +----------+| | Enter option number: | RC87A1MN || | +----------+| ============================================================================== The options on the main menu may be used in any order. Most sessions involve-- Option 6 a quick display to screen followed by several steps to refine the display, including-- Option 1 selecting just the items (columns) you want, Option 2 selecting records (rows) Option 3 adding text labels to each row. Redisplaying to screen (option 6) may be done between each step to confirm that the selections are appropriate or fit in the desired space. Each option may be repeated as necessary to fine tune your report. Finally, results can be saved for future use by-- Option 7 printing data out Option 8 copying selected data to a file on a floppy or hard disk for use with other software. If you do not understand the difference between selecting items and selecting records with regard to the particular file you are using, refer to the main help screen, which can be called up by pressing . Other options: D If the message ( D for Definitions ) appears as it does above, then the current file has concept definitions, table locators, and sometimes also methodological statements accessible from the main menu. Press D to display a list of all of the topics 7 available, and select one for display using the cursor keys and . BW Black/White. If you find that the colors EXTRACT uses do not display well on your monitor, you may type BW as an option, and the display will revert to black and white (monochrome). (To eliminate color automatically on future sessions, see the discussion of command line parameters on page 23.) SH Show file selection criteria. The notation is rather cryptic, citing codes rather than recognizable category names, but may be worth looking at if record selection does not work as intended. Type SH to turn this feature on, or again to turn it off. Specific Options 1) Select Items +------------------------------------------------------------------------------+ | SELECT ITEMS | | (Press H for HELP) | |Enter an X next to each item to include in display. | | | |Use , , < >, or < > to scroll through the list. | |TO EXIT: Press the key. | | | |X FIELD NAME DESCRIPTION | |==============================================================================| | | --title--- | 1987 RETAIL TRADE: General Statistics RC87A1 | | | ST | FIPS State Code | | | MSA | MSA or CMSA Code (9999=not in MSA, 0000 in New England) | | | PMSA | PMSA Code (9999=not in PMSA) | | | COUNTY | FIPS County Code | | | PLACE | Census Place Code | | | PLACEFIP | FIPS Place Code | | | RECTYPE | Record Type (02=State,04=MSA,05=non-MSA,06=County,07=Place)| |x | RTL87KB | Retail Kind-of-Business Code (001=Total Retail Trade)| | | ---------- | (for text, incl. SIC code, Add Labels) | |x | ESTAB | Number of establishments, 1987 | | | SALESF | Flag for SALES (0=data, 1=disclosure)| |x | SALES | Sales ($1,000), 1987 | | | PAYROLLF | Flag for PAYROLL (0=data, 1=disclosure, 2=not available)| | | |Options:ump,ocate,ord srch,efinition,

review,ser-defined item| +------------------------------------------------------------------------------+ This screen lists all of the items on any one record in the file. Enter an X next to each field you want to select into your output. If you enter an X but the highlighted area stays blank, you are on a comment line. Move from item to item with the up and down arrow keys, or move whole screenfuls at a time with and . To unselect an item previously selected, press the space bar to blank out the X. When finished, press to return to the main menu. For files with a long list of items, the following special keys may be useful: 8 J Jump to a particular field name. After you type J, the system prompts for a specific field name to jump to. Partial entries are acceptable. The system will jump to the next field name that begins with the characters you specify. L Locate a particular description. Typing L brings up a prompt for a charac- ter string. The system will then scan for the next text description starting with those letters. W Word search. Like ocate, typing W brings up a prompt for a character string. EXTRACT then presents a list of only those items where that word or character string is included in the description. Several other keys also offer special functions: D Definition. Typing D will bring up a narrative definition of the current item corresponding to the text that appears in appendixes to printed reports or documentation. Definitions are available only for those items with a dot < > to the right of the field name. If no dots are shown, definitions have not been prepared for this file. P Preview. Pressing P brings a box to the top of the screen that previews the way column headings and one data line will display in the normal columnar screen display (option 6 from the main menu). The primary advantage to previewing rather than the regular display is that previewing is nearly instantaneous, while the regular data display may take some time to find all of the records to fill a screen. Previewing allows you to see how many items fit on the screen at once, to truncate the width of any item (see the discussion of the W option on page 18), or R to eturn to the item selection menu to delete previously selected items or add new ones. U User-defined items. Typing U displays the box illustrated below for specifying a user-defined item. You may specify a ratio or percent (nume- rator, denominator and scaling factor) or a freeform expression (e.g., the sum of several items or a string function on a character variable). You may define up to 10 user-defined items. In data displays, user-defined items are displayed at the right, after all other selected items. User-defined may be printed out or extracted to a file, but they cannot be used as record selection criteria. +-User Defined Item------------------------------------------------------------+ | Numerator / Denominator * Scaling Factor| |Ratio: sales*1000 estab 1 | | --OR-- | |Freeform expression: | | Field Name Description Length: 8 Decimal places: | | SalesEst Sales per establishment ($), 1987 | +------------------------------------------------------------------------------+ Ratios: Typical applications include the computation of percents (make the scaling factor 100) or population per square mile (scaling factor is 2590 to convert thousandths of a square kilometer to square miles). In the example of sales per establishment below, SALES*1000 is entered rather than just SALES to compensate for the fact that sales are reported in thousands of dollars. 9 Field names must be specified exactly as they appear in the data dictionary or in the column headings, except that upper and lower case do not matter. Items added as labels from another file (see below) may be included in a computation if correctly specified, e.g., COUNTY->POP87, as can previously specified user-defined items. Freeform expressions: These may be dBase-compatible expressions either numeric or character in type. To enter one, you must skip past the numerator, denominator, and scaling factor fields (press three times). The sum of a series of consecutive fields may be entered in the form E50_99:E1000P, which, in the Location of Manufacturing Plants files, is equivalent to E50_99 + E100_249 + E250_499 + E500_999 + E1000P, or total establishments with 50 or more employees. This procedure should not be used if there are intervening flags or other variables. Examples of character-type free-form expressions include ST+COUNTY, con- catenating the 2-character ST code with the 3-character COUNTY code, or SUBSTR(GEOTEXT,1,10) if you want to copy to an extract file only the first 10 characters of GEOTEXT. Other notes: To be accepted, field names entered must be spelled correctly, but upper and lower case are not significant. The "Field Name" of a previously computed user-defined item may be used in lieu of repeating the same terms in a subsequent user-defined item. 2) Select Records +------------------------------------------------------------------------------+ |SELECT RECORDS | | Mark with S the variable(s) to restrict. | | ( you will be prompted for values to select ) | | | |TO EXIT: Press the key. | |Use , , < > , or < > to scroll through the list. | | | |S FIELD NAME DESCRIPTION | |==============================================================================| | | --title--- | 1987 RETAIL TRADE: General Statistics RC87A1 | | | ST * | FIPS State Code | |S | MSA * | MSA or CMSA Code (9999=not in MSA, 0000 in New England) | | | PMSA * | PMSA Code (9999=not in PMSA) | | | COUNTY * | FIPS County Code | | | PLACE * | Census Place Code | | | PLACEFIP - | FIPS Place Code | | | RECTYPE * | Record Type (02=State,04=MSA,05=non-MSA,06=County,07=Place)| | | RTL87KB * | Retail Kind-of-Business Code (001=Total Retail Trade)| | | ---------- | (for text, incl. SIC code, Add Labels) | | | ESTAB | Number of establishments, 1987 | | | SALESF | Flag for SALES (0=data, 1=disclosure)| |s | SALES | Sales ($1,000), 1987 | | | PAYROLLF | Flag for PAYROLL (0=data, 1=disclosure, 2=not available)| | | |* Marked variables are indexed, - denotes others with value selection menus | +------------------------------------------------------------------------------+ 10 To select records (i.e., to restrict the types of rows displayed), you may select on the values of any of the fields in the database. Most frequently, this involves selecting on a geographic code (e.g., ZIP code, county) or other identifier (e.g. a kind-of-business code). You may also select based on values of data fields, e.g. records with a value greater than 100 in the ESTAB field. The screen presents you with the same list of variables as before under item selection, but this time you enter an S next to the one or two variables on which you wish to be prompted for specific selection criteria, which the program will do after you press . (You may move among variables using the cursor keys, or with ump, ocate, and ord Search, as described under Select Items above.) You may select on either one or two variables at the same time. To select on two variables, for example, records showing at least $10,000,000 in sales within the Minneapolis-St. Paul MSA, enter an S next to both MSA and SALES. The program will prompt for criteria for each variable in turn. The record selection dialog continues in two different ways depending on whether or not a menu of possible values is available. Field names with either an asterisk (*) or dash (-) next to them generally have menus available. Selecting codes from a list. If you are selecting based on a code representing geographic areas, kinds of business, commodities, or other clas- sifications, you will be prompted with the names of each category. The number of entries you may select is limited to 17, or fewer if you are selecting based on more than one variable. +------------------------------------------------------------------------------+ |SELECT RECORDS (Screen 2) | | | |Enter an X next to each value you want to include. | | | |Use PgUp, PgDn, and ARROW KEYS to scroll through the list. | |TO EXIT: Press the key. | | | | X MSA DESCRIPTION | |==============================================================================| | | 5040 | Midland, TX MSA | | | 5082 | Milwaukee-Racine, WI CMSA | | | 5082 | Milwaukee, WI PMSA | | | 5082 | Racine, WI PMSA | | x | 5120 | Minneapolis-St. Paul, MN-WI MSA | | | 5120 | MN part, Minneapolis-St. Paul, MN-WI MSA | | | 5120 | WI part, Minneapolis-St. Paul, MN-WI MSA | | | 5160 | Mobile, AL MSA | | | 5170 | Modesto, CA MSA | | | 5200 | Monroe, LA MSA | | | 5240 | Montgomery, AL MSA | | | 5280 | Muncie, IN MSA | | | |To select based on range of codes, type R. Other options: D, J, W, or H| +------------------------------------------------------------------------------+ Scrolling through the list is accomplished with the up and down arrow keys, and . You may go directly to the bottom of the list with or back to the top with . 11 Several other keys offer special functions: D Definition. Typing D will bring up a narrative definition of the current category, if available. J Jump to a particular code. After you type J, the system prompts for a specific code to jump to. If, in the above example, you had known that the code you wanted was somewhere around 5000, jumping to that code would have saved a lot of scrolling through the list. L Locate a particular description. Typing L brings up a prompt for a charac- ter string. The system will then scan for the first description starting with those letters. For example, in the illustration above "Mo" would bring Mobile to the top of the display. R Range specification. If you want to specify a long series of contiguous codes, you may elect to enter them as a range of codes, described below. W Word search. After you type W, the system prompts for a word or any other string of characters. EXTRACT then presents a list of only those codes where that word or character string is included in the description. One application is in assembling a list of all metropolitan areas in a state. Entering WI as the search word would bring up a list of all descriptions including those characters, including all Wisconsin metro areas and also some other titles with "Wi" or "wi" in them, like Wichita. At this point you may simply select those you want and ignore those where "wi" does not appear as a state abbreviation. Executing a word search can be very time consuming with long code lists, like foreign trade commodity codes. Selecting a range of values. If you are selecting records based on a data value (e.g., only records showing sales more than $10,000,000), on a code without names, such as a ZIP Code, or have exited from the code selection screen above with an "R", you will be prompted to enter the lowest and highest values you wish to select. ================================================================================ | SELECT RECORDS--by specifying a range | | | | Enter MINIMUM value for SALES : 10000 | | | | Enter MAXIMUM value for SALES : | ================================================================================ This example selects all values greater than 10,000 ($10,000,000 since sales figures are expressed in thousands of dollars); 10,000 is entered as the minimum value, and nothing is specified for the maximum value. Selecting all values less than or equal to a given amount can be accomplished by leaving the minimum value blank and entering the amount as the maximum. To select only a single value, e.g. an ZIP Code of 22012, enter that value as both the minimum and maximum. Picking the best record-selection variables. In the first record-selection screen, illustrated on page 9, some field names have an asterisk (*) next to them. These variables are featured in dBase "index files" that can help software locate desired records very quickly. When selecting records in large 12 files it is highly desirable to include one of the asterisked variables in search criteria. For example, in the illustration above, there are two codes for places: PLACE and PLACEFIP. Only PLACE has an asterisk, and this is the variable you should use if you want to search for a particular place in this file. Many Census Bureau files feature data for multiple levels of geography, e.g., states, metropolitan areas, counties and places. These levels are differen- tiated in economic census files by the variable RECTYPE, and in 1990 census files by the variable SUMLEV. When selecting data for all counties, the novice user may be tempted to use the variable COUNTY and then proceed to check off as many values for the county code as possible. This will select all records with those county codes, but that may include not only records for counties, but also for places and other units of geography within the marked counties. To dif- ferentiate counties from other geographic levels, the user should select counties as a type of record (RECTYPE value "06") or summary level (SUMLEV value "050"). One useful rule of thumb is that if you use the word "all" in describ- ing your query (e.g., all metropolitan areas, all places within a particular county, all block groups within a selected tract), then you probably need to use RECTYPE or SUMLEV, either alone or in combination with another code. Where selection criteria are not related to available indexes, e.g., selecting records based on the volume of sales or number of employees, the program will operate more slowly than otherwise, examining every record in the entire data base for "hits", a time-consuming process. Scanning every record of a database on CD-ROM can take up to 5 minutes per megabyte. Just be patient. "Speed up retrieval" prompt. In certain record selection scenarios, the system may prompt you to select a value within an additional variable. In selecting records for the Minneapolis-St. Paul MSA, for example, the system responds by prompting you to also select a single value for record type, as illustrated below. This is because the index is sorted by MSA within record type (which keeps MSA total records separate from those county and place records that also include MSA codes.) +------------------------------------------------------------------------------+ | +--------------------------------------------------------------------------+ | | | YOU MAY SPEED UP RETRIEVAL . . . | | | | by entering an X next to ONE code for RECTYPE, then pressing | | | +--------------------------------------------------------------------------+ | | Press or H for Help. Press to skip. | |Use , , < >, or < > to scroll through the list. | | | | X RECTYPE DESCRIPTION | |==============================================================================| | | 02 | State | | x | 04 | Metropolitan Statistical Area | | | 05 | Nonmetropolitan part of State | | | 06 | County | | | 07 | Place | +------------------------------------------------------------------------------+ In many cases, the selection you make here does not in fact reduce the scope of your query. The system merely uses the index to find an appropriate place to start in the database, then discards the index. Thus, if you are not sure which code to pick, at least make sure that you pick one that is valid in combination with the selection criteria you have already given. For example, if you were to 13 check code 02 for State in this example, no records would be found because state records never have MSA codes other than 0000. Other notes on record selection. The program works best when selection criteria isolate adjacent records. For example, a request for a single county or list of counties with adjacent codes can be retrieved faster than a list with codes that are not adjacent. If the desired records are widely separated in a large file, e.g., MSA data for Abilene and Wichita Falls, it will probably be faster to make two separate queries than to check off both codes in a single query. Even if you want the results in a single output, you can make two separate retrievals, save them to DBF files, and then merge them "vertically" into a single data set, as discussed under "Advanced Topics", below. If only one value is selected, the description for that code will appear as a second-level heading, without the need to add labels for that code. For specialized inquiries, it is possible to enter a dBase style conditional expression instead of working through menus for record selection. This option is discussed under "Advanced topics" at the end of the document. Except for those conditional expressions, the items that you use in the record selection process must be in the current file. If you want to select records based on a user-defined item (e.g., population per square mile) or on an item or label merged from another file, you must first create an extract file including those items in .DBF format, load that file, and then make your selections. You may return and reselect records at any time, and the new criteria will simply supersede the old. To cancel previous selection criteria, bring up the first record selection screen and press . If, after bringing up that screen, you decide that you do want to einstate the previous selection criteria, press before pressing . einstating previous criteria also can be used to specify a third and fourth selection variable, as discussed under "Advanced Topics". (If you have already gone on to Select Records Screen 2, it is too late to einstate previous criteria.) 14 3) Add Labels This option can be used to add titles or names for code variables, such as geographic names, kind-of-business codes, etc., to displays or extract files. (If you have not done so already, the system will prompt you to select items first before adding labels.) In some cases there may be no text labels avail- able. ==ADD LABELS================================================================= | | | Use < > or < > to position cursor on a code for which you want labels | | Hit key to select | | Hit key for no labels | | | | CODE Code field for which labels are available | | ==========================================================================| | ST | FIPS State Code | | MSA | MSA or CMSA Code (9999=not in MSA, 0000 in New England) | | PMSA | PMSA Code (9999=not in PMSA) | | COUNTY | FIPS County Code | | PLACE | Census Place Code | | PLACEFIP | FIPS Place Code | | RECTYPE | Record Type (02=State,04=MSA,05=non-MSA,06=County,07=Place) | | RTL87KB | Retail Kind-of-Business Code (001=Total Retail Trade) | ============================================================================= Move the cursor down to highlight the desired label variable, then press . The system then presents you with a Screen 2, listing the different variations of the label available, generally differentiated by the width to be occupied. Shorter labels are usually preferred in columnar reports due to space con- straints. Longer labels are generally more descriptive, and should generally be used with the rowwise report (see Format Options, below). To select one, highlight it and press . ==ADD LABELS================================================================= | | | ==ADD LABELS (Screen 2)===================================================== | | Select one of these variations of the label. | | | Use < > or < > to position cursor on desired label. | | | Hit key to select. | | | | | | CODE Description of available labels | | | ==========================================================================| | | TEXT | SIC code(s) and description, abbreviated to 60 characters | | | TEXT10 | SIC code(s), abbreviated to 10 characters | | | TEXT20 | Description, abbreviated to 20 characters | | | TEXT40 | SIC code(s) and description, abbreviated to 40 characters | | | TEXT5 | Unique mnemonic based on sic code | ==| | | | | | ============================================================================= Press A to show all fields (not just labels). Press M for multiple labels. 15 Special options for Add Labels Screen 2: A Show All fields. Some label files have more to offer than just text labels. For example, a file with state or county names may also have population estimates. A file of commodity titles may have cross-links to other coding systems. Screen 2 normally displays only those options beginning with the letters "TEXT". Typing A will cause all fields to display. M Multiple labels. If you want to add more than one field from this set of labels, for example, both the state name and population, you must type M before selecting the first label in Screen 2. Thereafter, you may select any label by highlighting it with the up or down arrow and pressing . Unlike the normal mode, however, the Add Labels Screen 2 will remain, with a ">" next to the selected label. You may continue selecting labels, up to 10 from any one source. J Jump to a particular field name. | These three functions operate here L Locate a particular description. | as do under Select Items. See page 7. W Word search. | It is also possible to select labels from two different sources, e.g., for both kind of business and county, at the same time. This is not always advisable, though, since two sets of labels may take up too much space on the screen, and because keeping track of two sets of labels slows down most data retrieval operations. A second set of labels is frequently unnecessary. If records have been selected based on a single code value, e.g., a particular kind-of-business, the description of that value will appear automatically on screen displays and printouts as a second-level heading. If you are merging data horizontally (see "Advanced Topics" below) you are limited to using labels from only one source. To select a second set of labels, select "Add Labels" a second time from the main menu. At the first screen, a blinking message at the top will prompt you to type "S" in order to save the first set of labels, before you select the second set. If you do not type "S" when prompted, the new selection will replace the earlier labels. Long labels may be shortened with the idth option discussed under "Display to Screen", and also available in the Preview mode of the Select Items screen. 4) Manipulate Files The Manipulate Files menu offers a number of ways to resequence output, combine it with data from other files, or reduce the data by generating totals. 1. Select an existing index 2. Create a new index 3. Merge files horizontally (add items to existing records) 4. Merge files vertically (add more records to existing file) 5. Create totals or subtotals Use of these options requires somewhat greater understanding of database concepts than do most of the other features of EXTRACT. Therefore, their discussion is deferred to "Advanced Topics" near the end of this document. 16 5) Format Options +------------------------------------------------------------------------------+ | FORMAT OPTIONS | | Type of report | | | |1 Select columnar output X | |2 Select row-wise output | | | |3 Heading (1st level) 1987 RETAIL TRADE: General Statistics RC87A1 | |4 Heading (2nd level) Minneapolis-St. Paul, MN-WI MSA | | | | Configure output for printer Current values | | -------------- | |5 Page width 80 | |6 Number of data lines per page 60 | |7 Top margin 0 | |8 Left margin 0 | |9 Print one page at a time NO | | | | Press to return to main menu | | | | | | Enter option number : 3 | | | | Enter heading 1: 1987 RETAIL TRADE: General Statistics (J.Doe 9/1/91) | +------------------------------------------------------------------------------+ There are two basic types of format options. The selection of columnar or rowwise output and the specification of headings affects all subsequent output to the screen, printer or extract file. Page length, page width, margins and print mode are settings which affect only printer output. Columnar and rowwise output are discussed under "Display to Screen" below. Certain headings are provided as defaults. The data dictionary supplies the default first-level heading; selecting records with a single code will introduce the label for that code as a second-level heading. Either default may be overridden here. Entering either "3" or "4" will bring up that label for editing. You may insert or delete words, or you may start fresh by typing Y to clear the prompt area. The example above illustrates how the heading can be used to document not only the data but also your name and date, to help you keep your printouts straight. Note that the second-level heading will be overwritten by the program every time you reselect records. To change the bottom margin change the number of data lines per page. Page width can be set for any amount, for instance, 136 for compressed print or wide carriage printers. This option affects only the number of characters generated per line, not the font or spacing on your printer. EXTRACT does not change printer settings, e.g., compressed print, but if you have a small utility program that does so, you may call it from the Advanced Options menu (see page 23). If the number of data items you specify requires more than the specified page width, printed output will wrap to the next line (unlike output to the screen where the columns that do not fit simply do not show up until you cursor to the right or left). For a neater printed display, you will have to return to the item selection screen and delete some of the items you intended to display, or reduce the width of certain columns (see "Display to Screen" below.) 17 6) Display to Screen Note, on execution speed. If you are working with very large files on CD-ROM, and if you are selecting records based on criteria not related to an available index file, the program may take a number of minutes to set up the data display. The program does not return control to you until a screenful of data is displayed, and if only a few records meet the criteria you set, the program may continue looking to the end of the file for more. Columnar display. In columnar reports, the normal presentation, information is displayed in columns (for each data item) and rows (for each record). While only a limited number of columns and rows can be displayed at any one time, you may think of the display as a window into a larger worksheet, since you may cursor to the right to see additional items, or down to see more records. The initial display of data from some files may seem unfriendly if no text labels are present. In the illustration below, a number of code fields appear first (on the left side) in each record (row), and some of the data items we are interested in, e.g., sales, are off the screen. The illustration on the next page shows the same data set after we have selected items, selected records, added labels, and added a heading, as discussed elsewhere in this documentation. +------------------------------------------------------------------------------+ |Use ARROW keys to scroll up, down, and sideways. Press ESC when finished| | | | 1987 RETAIL TRADE: General Statistics RC87A1 | | ST MSA PMSA COUNTY PLACE PLACEFIP RECTYPE RTL87KB ESTAB | |==============================================================================| | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 001 | 27005 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 007 | 1876 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 008 | 908 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 009 | 736 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 010 | 172 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 011 | 697 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 012 | 203 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 013 | 68 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 014 | 591 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 016 | 152 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 023 | 152 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 028 | 32 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 029 | 87 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 030 | 33 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 031 | 147 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 032 | 292 | | 27 | 0000 | 0000 | 000 | 0000 | 00000 | 02 | 036 | 3022 | | | |FIPS State Code | +------------------------------------------------------------------------------+ Above the double line are the variable names assigned in the data base. To see a more complete description of the data item, move the cursor (highlighted box) to the desired column, and the appropriate description will appear at the bottom of the screen. 18 +------------------------------------------------------------------------------+ |Use ARROW keys to scroll up, down, and sideways. Press ESC when finished| | | | 1987 RETAIL TRADE: General Statistics (J.Doe 9/1/91) | | Minneapolis-St. Paul, MN-WI MSA | |RTL87KB->TEXT40 RTL ESTAB SALES SalesEst| |==============================================================================| | Retail Trade | 001 | 13311 | 16762012 | 1259260| |52 Bldg materials & garden supply | 007 | 687 | 1038660 | 1511878| |521,3 Bldg materials & supply stores | 008 | 290 | 796066 | 2745055| |521 Lumber & bldg material dlrs | 009 | 205 | 735569 | 3588141| |523 Paint, glass, & wallpaper st | 010 | 85 | 60497 | 711729| |525 Hardware stores | 011 | 257 | 137758 | 536023| |526 Rtl nurseries, lawn/garden sup | 012 | 105 | 88549 | 843324| |527 Mobile home dealers | 013 | 35 | 16287 | 465343| |53 General merchandise stores | 014 | 226 | 2374716 | 10507593| |531 Dept. stores (incl. leased dp) | 016 | 80 | 2188192 | 27352400| |531 Dept. stores (exc. leased dep) | 023 | 80 | 2074585 | 25932313| |531 pt. National chain | 030 | 20 | 528734 | 26436700| |533 Variety stores | 031 | 39 | 36078 | 925077| |539 Misc. general merchandise strs | 032 | 107 | 264053 | 2467785| |54 Food stores | 036 | 1462 | 2862076 | 1957644| |541 Grocery stores | 037 | 929 | 2714757 | 2922236| |542 Meat & fish (seafood) markets | 042 | 72 | 33429 | 464292| | | |SIC code(s) and description, abbreviated to 40 characters | +------------------------------------------------------------------------------+ To move down through a long report, use the down arrow or . Avoid holding the key down continuously, allowing the screen to refresh at least the first line before pressing again, or the system may go farther than you want and take a very long time before filling the screen with data again. To jump quickly to the top of the file (or to the first selected record), press simultaneously. To jump quickly to the bottom of the file (or to the last selected record), press . When a full screen of data is displayed, you may press to return to the main menu. If the computer is still searching for the next record, it will ignore the until the next qualifying record or the end of file is reached. Special options: D Definition. Typing D will bring up a narrative definition of the item (column) to which the cursor is pointing, if available. If the cursor is pointing to a label, then a definition associated with that label will appear instead, if available. M Mark a record. Each time you display data, the program starts at the top of the file, or at the first record that meets the selection criteria. A new starting place can be "marked" by typing M while the cursor is highlighting the desired record. Subsequent displays, prints, or extracts will then start with the marked record. To cancel the mark, either mark another record (e.g. at the top of the file) or select records again. S Show contents of narrow column. If you have used the W option (below) to make a column narrow, you may highlight the cell you want and type S to how its entire contents. 19 T Toggle to rowwise display. See discussion below. W Width of column. To make a column more narrow, highlight that column with the cursor, then press W and enter the number of characters you want to allocate. The width option is most frequently used to show only the first part of a long label (e.g., the first 10 or 12 characters of the county name is usually sufficient). It can also be used to narrow data columns. If the width you specify is less than the number of characters in the column heading, the heading will be truncated, but the full description will still be shown at the bottom of the screen. Caution--if you specify a width less than required for the largest value in a numeric field, you will see only the rightmost digits, with no indication that the value displayed is misleading, until you type S to how full contents. (Strategy--since the screen refreshes after every width specification, it is faster to use the width option in the Preview mode within the Select Items screen--Option 1 from the main menu.) Rowwise display. Rowwise displays are obtained by using the oggle option from a columnar display or selected through the format options (#5 at the main menu). They present each data item together with its complete description. This can be handy when displaying a long list of items for a small number of records, as in a profile for a single area. While the illustration below shows only 5 lines per record, reflecting the items selected for the columnar display above, the screen can accommodate up to 22 variables at a time. +------------------------------------------------------------------------------+ |1987 RETAIL TRADE: General Statistics (J.Doe--Minneapolis-St. Paul, MN-WI MSA| | | | Retail Trade | |001 Retail Kind-of-Business Code (001=Total Retail Trade| | 13311 Number of establishments, 1987 | | 16762012 Sales ($1,000), 1987 | | 1259260.16 Sales per establishment ($), 1987 | | | |52 Bldg materials & garden supply | |007 Retail Kind-of-Business Code (001=Total Retail Trade| | 687 Number of establishments, 1987 | | 1038660 Sales ($1,000), 1987 | | 1511877.73 Sales per establishment ($), 1987 | | | |521,3 Bldg materials & supply stores | |008 Retail Kind-of-Business Code (001=Total Retail Trade| | 290 Number of establishments, 1987 | | 796066 Sales ($1,000), 1987 | | 2745055.17 Sales per Establishment ($), 1987 | | | | to quit, or < > to continue, or < > to back up. | +------------------------------------------------------------------------------+ Since rowwise format yields less data per screen or per page than columnar reports, columnar format is more commonly used. The two formats are used together, as in using the columnar format to highlight the record you want to look at, then oggling to rowwise display for the labelled display of up to 22 items from that record. Typing T at the rowwise screen then oggles you back to columnar format. 20 When a full screen of data is displayed, you may hit to return to the main menu. If the computer is still searching for the next record, it will ignore the , , or until the next qualifying record or the end of file is reached. 7) Print This option sends a report to the printer, including the selected items for the selected records. Column headings are the same as in the display to the screen, but, in addition, each printout includes a footer at the end which lists each item and its description (up to 60 characters). Rowwise reports may also be printed. Headings, margin settings, and the number of lines per page can be set via Format Options (option 5 at the main menu). See the discussion on page 15. Printing may be aborted at any time by pressing . To save print output to a file, for example, so that you can use a printer connected to another computer, see below. 8) Extract Data to a File There are four options for type of file to extract, as well as a "dry run" option: 1> .DBF Database format. This creates a dBase III+ format file for the data and a data dictionary file, and will add the file to your "MY_FILES" catalog. Thus the resulting file can be used for further data manipulation in EXTRACT as well as in dBase or compatible programs. 2> .PRN Comma-delimited format. This format produces ASCII text records in which the text variables are enclosed in quotes, numeric fields are recorded without leading zeroes or blanks, and all variables are separated from one another by commas. A carriage return terminates each record. This format is commonly used for importing data into spreadsheets, statistical and graphics packages, and database software not compatible with dBase III+. If this option is selected, a prompt asks whether to add headers and footers. This is desirable for import into spreadsheets, but not for database or some graphics packages, which may have problems with alphabetic information (e.g., the header labels) in fields reserved for numeric information. [To import a .PRN file into Lotus 1-2-3, load that program and type "/FIN" (for ile mport umbers) and the name of the file. If the .PRN file has been stored in 1-2-3's default subdirectory, 1-2-3 will display it in a list at the top so that you may select it by cursor rather than by typing in the filename.] 3> .SDF Fixed-format text. This format is a straight ASCII text file format, with a carriage return at the end of each record. The column widths can be determined by examining the original data dictionary in dBase. 21 4> .TXT Print file. This is another type of ASCII file, but with page breaks and column headings. It allows you to transport output to another computer for printing. It is also the only way to save rowwise output to a file, since each of the three options above treat the data in columnar fashion. 5> Dry run to estimate file size. This option counts the number of qualifying records and generates an estimate of the number of kilobytes of storage space required to accommodate the output file and any temporary work files. One useful application is where you intend to save the output to a 360-kb floppy disk. If the estimated size of the data file, not counting work space, exceeds 360 kb, you have the opportunity to to the main menu and reduce the number of items or records before attempting the extraction. (If you do not know how much space is available on your disk, you may display a directory from the Advanced Options menu--see page 23.) The fact that the "dry run" option counts the number of records may answer some queries by itself, such as how many areas have more than 1 million inhabitants. +------------------------------------------------------------------------------+ | EXTRACT DATA TO A FILE | | | | Select format for output file: to exit | | | | 1 .DBF Database format | | 2 .PRN Comma-delimited (for import to most other software) | | 3 .SDF Fixed-format text | | 4 .TXT Print file | | 5 ---- Dry run to estimate file size | | | | Enter selection : 1 | | | | | | Enter drive, directory and output file name in the following format: | | drive:\directory\filename (no extension) | | : c:\work\mpls1 | | | | Enter description of file for your MY_FILES catalog | | : Minneapolis-St.Paul MSA by kind-of-business (9/1/91) | | | +------------------------------------------------------------------------------+ After you select the file type, the system prompts for the location of the output in terms of drive, directory and file name. You should not specify a file extension, which will be added automatically by the system. If you do not specify a drive and directory, the file will be stored in your "work space" directory, as discussed on page 4. If you have specified option 1 (DBF), the system will prompt you for a descrip- tion to be put in your MY_FILES catalog, should you want to use that output file in EXTRACT. The default description is taken from the headings previously specified. You may accept it (with ), change it by cursoring to the right to add or delete information, or start fresh by hitting Y. After completion of an extraction to .DBF, the system asks whether to return to the original file for further manipulation or to load the newly created file. 22 9) Return to file selection menu This option returns you to the opening menu to select another type of file from the master catalog or another file within the same group. If, during earlier file selection, you restricted your file selections to only those for a par- ticular state, that restriction will continue in force. To access a .dbf file you created in Option 8, select the MY_FILES catalog (always at the bottom of the master list of catalogs). To access data on a different CD-ROM or other device not specified in your MY_FILES catalog (see below), press at the select-a-catalog screen to bring up the drive selection screen. Do not change CD-ROMs while the program is running without going through this step. 10) Advanced options. +------------------------------------------------------------------------------+ | ADVANCED OPTIONS | | | |1. Save format options to a parameter file. | |2. Display secondary file | | | | DATA DICTIONARY (.dct) | |3. Create (or replace) data dictionary for specific file | |4. Edit descriptions or label references in an existing dictionary | | | | DATA FILE CATALOG (.ctg) | |5. Add files to MY_FILES catalog | |6. Edit descriptions | | | | OTHER | |7. Clean out Selex.dbf and SeleDct.dbf | |8. Edit index file catalog (.cti) descriptions | |9. Disk directory or other DOS functions | | | | Press to return to Main Menu | | Enter option number: __ | +------------------------------------------------------------------------------+ Saving format options. Specify option 1 if you have changed any of the following variables from their defaults and wish future runs with the EXTRACT program to start with their current values: Margins Page width Lines per page Editing your MY_FILES catalog. Option 6 allows you to delete out-of-date listings from your MY_FILES catalog, to change the descriptions, or to change the drive location of particular files. You will be prompted to confirm the location of the catalog, including the drive and directory you normally specify for "workspace", e.g, "C:\EXTRACT\WORK\MY_FILES.CTG". To determine whether any of the files in the list are no longer present in the directory where originally stored, cursor to the far right. The last column will indicate those files now "ABSENT". To delete an obsolete file, highlight the appropriate record and press U. To edit the description, cursor to the right until you see the description, then type over the old description and press . To tell 23 EXTRACT that you are changing the location of a file, highlight the PATH field and edit the drive designation. If you are copying a .dbf file to a floppy disk for use on another computer, copy your MY_FILES catalog to the floppy as well, then go back into EXTRACT and edit the path field in the MY_FILES.CTG on the floppy. It is also possible to add files to your MY_FILES catalog. This process is described under "Advanced Topics" below. DOS functions. Advanced option 9 allows you to display directories, perform other DOS functions, or even run small programs without leaving EXTRACT. Common applications include determining whether there is enough disk space available to accommodate a file to be extracted, deleting obsolete files to make room for new extracts, or running a small program to change your printer configuration. ADVANCED TOPICS Auxiliary Files and Their Functions: Data Dictionary Files. .DCT. Data dictionary files describe each of the variables on the corresponding data file(s). Each data dictionary file has either the same filename as the corresponding .DBF file except that D is the last character of the filename, or has a shorter filename so that it can serve as data dictionary for a number of files that all start with the same characters in the filename. Data dictionaries are in dBase III+ format even though they do not have the .DBF extension. Some data dictionaries include both a .DCT file and a matching .DBT file that carries definitions which are referenced as memo fields from the .DCT files. Label Files. .DBF. Label files carry text descriptors associated with the individual values of particular variables (for example, the names of particular kinds of business or geographic areas). These files may also be referred to as stub files, since they carry the text information normally found in the stub (i.e. lefthand column) of printed tables. Memo fields may be used to provide longer definitions of values, in which case there is a .DBT file of the same name. Each label file must also have an associated index file. The file SELEX.DBF must be present in order for code labels to be used in the item selection process. Catalog Files. .CTG and .CTI. Catalog files list the files of a particular type that are available, along with a brief description of each one. The master catalog "master.ctg" lists the available catalogs, or types of files, available on the device. Files with the extension .CTI catalog index files. While .CTG files are compatible with dBase catalog files (.CAT), a different extension is used so that users are not tempted to use them in dBase without recopying them. (DBase attempts to write new information to a catalog in use, not appropriate for CD-ROM.) Program and Parameter Files. EXTRACT.EXE is a program compiled by Clip- perTM. The first time it is used it gets certain parameters from "extstart.prm", but as soon as the user exercises the option to save format options, a new file "extract.prm" is created. Help Screen Files. .TXT. As soon as a catalog is selected, the EXTRACT program displays a catalog-specific help screen, if present. That same screen can be brought up at any time from the main menu by typing "H". These 24 help screen files have the same name as the corresponding catalog files, e.g., RC87A1__.txt. Setting Up an EXTRACT Menu Drive designations may be entered at the command line. This is particularly useful where multiple Census Bureau CD-ROMs are in use, at least one of which relies on auxiliary files stored on a hard disk. Command-line parameters allow batch files or menuing systems to deliver the user right to the menu of avail- able catalogs, thereby avoiding having to teach each user about drive designa- tions on the system. The command line takes the following form EXTRACT [ Example: EXTRACT cb l: c:\ccdbaux\ c:\test\ master.ctg ccdintro.txt If no arguments are present, the system will operate as usual, prompting for drive and directory designations. The individual arguments are as follows 1. C indicates that the data drive is a cd-rom; n indicates no cd-rom. A, together with no additional arguments, puts the user immediately into the advanced menu, discussed below. B, when added to the c, n, or a, puts the display into monochrome (black and white) mode. 2. 3. This is normally "l:\auxil\" on CD-ROMs, such as the economic census discs, that have auxiliary files built in. 4. [] 5. [] Optional. Any entry, which may include a full path designa- tion, overrides the normal default to a file named "master.ctg" within the auxiliary drive designated above. 6. [] Optional. Used so that a customized screen can be presented in lieu of EXTRACT's opening screen. Using the ave option at the opening Drive Selection screen generates a one- line batch file saving the first five parameters, assigning a name you specify. EXTRACT also builds the file EXMENU.bat, listing each of the batch files you have created, but without any description. With your text editor, you can add appropriate annotations to each line of the file EXMENU.bat, illustrated below. echo off echo . echo EXTRACT MENU echo . echo EX EXTRACT with Economic or Agriculture Census discs echo EXCBP EXTRACT with County Business Patterns echo EXEXPORT EXTRACT with U.S. Exports of Merchandise echo EXIMPORT EXTRACT with U.S. Imports of Merchandise echo EXCCDB EXTRACT with County and City Data Book, 1988 25 echo EXUSACO EXTRACT with U.S.A. Counties, 1992 echo EXTRACT with 1990 Census CD-ROMs: echo EXPL94 PL94-171 echo EX1A STF 1A echo EX1B STF 1B (Block statistics) echo EX1C STF 1C echo EX3A STF 3A echo EX3B STF 3B (ZIP Code data) echo EX3C STF 3C echo EXEEO EEO file echo on Below are listings of the corresponding batch files, created with your text editor or simply by using ave. This illustration assumes that the CD-ROM drive is L:, that the subdirectory C:\EXTRACT\WORK exists for use as the program's work space, and that the subdirectories CBPAUXIL, FTDAUXIL, CCDBAUX, and 1990AUX within C:\EXTRACT\ contain the appropriate auxiliary files. Note that the fifth argument is added to specify the name of the master catalog for foreign trade and 1990 census CD-ROMs, since each of those groups use the same auxiliary directory. Filename Contents EX.BAT EXTRACT c l: l:\auxil\ c:\extract\work EXCBP.BAT EXTRACT c l: c:\extract\cbpauxil\ c:\extract\work EXEXPORT.BAT EXTRACT c l: c:\extract\ftdauxil\ c:\extract\work mstrexpo.ctg EXIMPORT.BAT EXTRACT c l: c:\extract\ftdauxil\ c:\extract\work mstrimpo.ctg EXCCDB.BAT EXTRACT c l: c:\extract\ccdbaux\ c:\extract\work EXUSACO.BAT EXTRACT c l: c:\extract\usacoaux\ c:\extract\work EXPL94.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrpl94.ctg EX1A.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrst1a.ctg EX1B.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrst1b.ctg EX1C.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrst1c.ctg EX3A.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrst3a.ctg EX3B.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrst3b.ctg EX3C.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstrst3c.ctg EXEEO.BAT EXTRACT c l: c:\extract\1990aux\ c:\extract\work mstreeo.ctg 26 File Manipulation +------------------------------------------------------------------------------+ | MANIPULATE FILES | | | | 1. Select an existing index | | | | 2. Create a new index | | | | 3. Merge files horizontally (add items to existing records) | | | | 4. Merge files vertically (add more records to existing file) | | | | 5. Create totals or subtotals | | | | Press to return to Main Menu | | | | | | Enter option number: _ | +------------------------------------------------------------------------------+ Option 1. Select an existing index. Some files have one or more index files which can be used to resequence the output. Index files can also speed certain kinds of searches, and the "Select Records" option may pick an index for you, if a relevant one is available. Therefore, most users will not find it necessary to use this option. To explicitly select an index, highlight your selection and press . To return the file to its original sort, press . +------------------------------------------------------------------------------+ |SELECT INDEX (H for HELP) | | | |1. Position cursor by using , , < >, or < > | |2. Press to select an index, to UNSELECT. | | | | ndx_name key_descr | |==============================================================================| | RC87C1MN | First record for each county | | RC87K1MN | By kind-of-business code, by rectype ... | | RC87M1MN | First record for each MSA | | RC87N1MN | First record for each PMSA | | RC87P1MN | First record for each place | | | +------------------------------------------------------------------------------+ Option 2. Create a new index. If none of the existing indexes yield the desired sequencing, you may create a new index. The system prompts you to enter the key expression for the index, and, to help you with the format, shows what the key expressions for existing indexes look like. The indexing expression can be as simple as "-sales" to rank all records by sales (the leading minus sign specifies descending sequence). To rank areas by sales within each kind of business, the indexing expression is more compli- cated, since alphabetic information and numeric information can only be used in the same key after the numeric information has been converted to string format. The key expression shown in the illustration above subtracts sales from a large 27 number to make the descending sequence appear like ascending sequence to the computer, converts that expression to a 10-character string, and combines it with the kind-of-business code. Since the spelling of variable names must be exact, you may need to back to the main menu and review variable names on the Select Items screen before proceeding. +------------------------------------------------------------------------------+ | INDEX FILE DESCRIPTION INDEX KEY | |==============================================================================| | RC87C1MN | First record for each county | RECTYPE+ST+COUNTY | | RC87K1MN | By kind-of-business code, by rectype ... | RTL87KB+RECTYPE+ST+ | | RC87M1MN | First record for each MSA | RECTYPE+MSA+PMSA | | RC87N1MN | First record for each PMSA | RECTYPE+PMSA | | RC87P1MN | First record for each place | RECTYPE+ST+PLACE | | | |If the file you are working with is relatively large, and the number of | |records you want to access through the index is relatively small, it is | |likely more efficient to extract a file (.DBF) before indexing. | | | | Enter KEY expression for new index: _______________________________ | +------------------------------------------------------------------------------+ After you enter an indexing expression, the system prompts you for a name for the index file and a description to enter into your MY_FILE.cti catalog of index files. In the future you will be able to access this index from the "Select an existing index" option. As the screen warns, indexes for large files can be large themselves. All records in the file are indexed and any previous record selection is ignored. Thus it may be more practical to extract the desired data to a .DBF file and index the result than to index an entire file on a CD-ROM. Options 3 and 4. Merging files. Two ways of merging files are available. The following illustrates the difference between vertical and horizontal merger. Horizontal merger: XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXXX--original file--XXXX XXXXXXX XXXX--merged file--XXX XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXX XXXXXXX XXXXXX Vertical merger: XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXXXXXXXXX--original file--XXXX XXXXXXX XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX XXXXXXXXXXXXXXX--merged file--XXXXX XXXXXXX XXXXXXXXXXXXXXXXXXXXXXXX XXX XXXXXX XXXXXXX When you merge files horizontally, you add to your current display items from another source that has compatible identifiers for each record. Adding labels is a form of horizontal merger in which the user interface is simplified by the fact that the necessary relationships are pre-specified in the data dictionary. This option allows you to merge files of your own choosing, such as employer and 28 nonemployer data, data for different time periods, population and economic data, etc. When you merge files vertically, you append records to the end of the original file from a second file. Vertical merger, for instance, allows you to create a single file that contains counties from multiple states when the original files on CD-ROM are state-specific. Option 3, Merging files horizontally. Horizontal merger takes you through four screens, three of which are already familiar. The first two screens-- "Select a Catalog" and "Select a Data File"-- specify the file to be merged into the current display. The third screen asks you on what basis the two files are to be merged. The simplest files to link are those that have the same number and sequence of records (see option 1, below). Examples include the various files within STF 1A or within the 1988 County and City Data Book. (Using the Add Labels option from the main menu may accomplish the same thing with fewer steps.) If the number of records in the current file and new file are not the same, the system prompts you for an expression that gives the record number in the new file, for example, LOGRECNU (logical record number) on STF 1A files. +------------------------------------------------------------------------------+ | How do records in RC87N2XS relate to records in RC87A1MN? | | | | 1. By way of the record number (e.g., STF 1) | | | | 2. By way of an index I need to create now | | | | 3. By way of one of the indexes listed below | | | | Enter option: 3 | | | | INDEX FILE DESCRIPTION | |==============================================================================| | RC87N2K | By kind of business by State, MSA & PMSA | | RC87N2S | First record for each State | | RC87N2M | First record for each MSA or PMSA | | | | | +------------------------------------------------------------------------------+ The illustration above shows the linkage of statistics about employers from the RC87A1MN file with statistics about nonemployers from the RC87N2XS file. The two files do not have exactly the same number of records, but they do have the same kind-of-business, state, MSA and PMSA codes, so they can be linked by way of the first index. Indexes with a description "First record for..." should normally be avoided. Entering option 3 puts the cursor on the first index, which is selected with . That brings up the following prompt: Specify relation for RC87A1MN RTL87KB + ST + MSA + PMSA Pressing here will accept the suggested relationship, which reflects the key expression of the selected index. Changes are appropriate if the current file uses different names for the variables shown, or if corresponding variables are not available. If, for example, the link were being made from a previously extracted file for Minneapolis-St. Paul MSA that carried the RTL87KB code but 29 did not include state or MSA codes, the relationship could be changed to RTL87KB + "00" + "5120" where 00 is the default state code on MSA records and 5120 is the code for the Minneapolis-St. Paul MSA. If the linking expression is not expressed correctly, the fields merged subsequently will contain only blanks and zeroes. The fourth screen in the horizontal merge sequence prompts you to select items from the new file. The results of this merge are illustrated below. Data from the merged file appear at the right, and the column headings show their source by the "RC87N2XS->" prefix. Note that some of the data in the righthand columns are shown as zero. This is because these source records had no match in the RC87N2XS file, nonemployer data being available in less detail than employer data. After a horizontal merger, it is possible to combine the contents of both old and new items in a user-defined item, for example, a freeform expression SALES + RC87N2XS->SALES would yield total sales for employer and nonemployer establishments. The syntax for specifying the merged items in such an expres- sion must be copied exactly from the headings shown. +------------------------------------------------------------------------------+ |Use ARROW keys to scroll up, down, and sideways. Press ESC when finished| | | | 1987 RETAIL TRADE: General Statistics RC87A1 | | Minneapolis-St. Paul, MN-WI MSA | |RTL87KB->TEXT40 ESTAB SALES RC87N2XS->ESTAB RC87N2XS->SALES| |==============================================================================| | Retail Trade | 13311 | 16762012 | 7346 | 309301| |52 Bldg materials | 687 | 1038660 | 295 | 12053| |521,3 Bldg material | 290 | 796066 | 150 | 5686| |521 Lumber & bl | 205 | 735569 | 0 | 0| |523 Paint, glas | 85 | 60497 | 0 | 0| |525 Hardware stor | 257 | 137758 | 53 | 4188| |526 Rtl nurseries | 105 | 88549 | 72 | 2009| |527 Mobile home d | 35 | 16287 | 20 | 170| |53 General merchan | 226 | 2374716 | 119 | 2747| |531 Dept. stores | 80 | 2188192 | 0 | 0| |531 Dept. stores | 80 | 2074585 | 0 | 0| |531 pt. Conventiona | 20 | 0 | 0 | 0| |531 pt. Discount or | 40 | 0 | 0 | 0| |531 pt. National ch | 20 | 528734 | 0 | 0| |533 Variety store | 39 | 36078 | 57 | 1482| |539 Misc. general | 107 | 264053 | 62 | 1265| +------------------------------------------------------------------------------+ EXTRACT does not give you control over the sequence of items in the display. Labels always appear first, followed by items from the current file, followed by merged items, followed by user-defined items. If you subsequently create an extract .DBF file or .PRN file with headings, the system will prompt you to supply a different name and description for the duplicate fields. For example, you could change the field name to "ESTABnon" and the description to "Nonemployers--Number of establishments, 1987". The prompt below would appear at the bottom of the Extract Data to a File screen, and be followed by a similar prompt for the duplicate SALES field. 30 | Duplicate field name detected | | In original file: | | ESTAB Number of establishments, 1987 | | | | RC87N2XS->ESTAB | | ESTAB_____ Number of establishments, 1987_____________________________ | +------------------------------------------------------------------------------+ Option 4. Merge files vertically. Merging files vertically involves appending the records of a second file to the bottom of the current file. Unlike horizontal merger, this involves an immediate write to disk. Therefore the program allows you to initiate a vertical merger only when the current file is in your MY_FILES catalog. The file being merged in may be from any catalog, but it is highly likely that it will also be from your MY_FILES catalog since the entire file is read in. The structure of the resulting file is dictated by the current, or original, file. Only those data fields that appear in both files with the same name will be added in. If there were no commonality between field names in the two files, vertical merger would add only blank records. Where field names are entirely different, you may need to study horizontal merger further. The dialog to accomplish vertical file merger is fairly short. You are presented with the "Select a Catalog" screen followed by the "Select a Data File" screen, following which the merger is accomplished. The resulting file has the same name as it did before. If you wish to change its description in your MY_FILES catalog, do so with option 6 from within the Advanced Options screen (option 10 from the main menu). Some files may be merged either horizontally or vertically. For example, the nonemployer data merged in the horizontal example above could have also been merged in vertically, since the data field names are compatible in both sources. Another example where a similar choice is available is data from corresponding files for different periods of time, such as 1987 and 1982 manufactures data. In this case you will need to anticipate the need to distinguish the two sources from one another in the resulting file. This can be accomplished when creating the original file by adding a user-defined item named YEAR and giving it a freeform value of "87". The records merged in from the 1982 file will be blank in the new YEAR field. Option 5. Create totals or subtotals. Totalling allows you to create a new data base where all records with a common identifier, or totalling key, have been summed together. This may be an essential function in reducing the size of certain very large files, such as imports or exports files where there are no intermediate-level summaries that can be selected. This function is less frequently needed in economic census and 1990 census files where intermediate- level totals are already included. If your intention is to extract a file for loading into a spreadsheet, you may well find that it is easier to defer totalling to the spreadsheet unless, of course, you are dealing with a file too large before totalling to be loaded into memory. After you have entered a key expression, the system prompts for a name for the resulting file, and a description for your MY_FILES catalog. When the totalling is complete, the system will prompt whether you want to return to the original file for further work, or whether to immediately load the file you have just created. 31 +------------------------------------------------------------------------------+ |This function creates a new database consisting of totals or subtotals from | |the current database. The totals file will include all of the items (columns)| |on this file--regardless of any subset you may have specified in SELECT ITEMS,| |and regardless of any LABELS you may have added. Labels can be added to the | |totals file, but only after this process is complete. | | | |The prompt below asks you to specify an item or expression as the KEY. As | |the program reads in new records from the file, it generates a total every | |time the value of KEY changes. | | | |The output file will contain sums for every numeric item selected (up to a | |maximum of 24). Since the program will bomb if a field is not wide enough | |to accommodate the total generated, SELECT ITEMS to exclude flags and other | |numeric fields not wide enough. For codes and other alphabetic items, the | |totals file will present the first value encountered within the total. | | | |Totalling can consume a lot of time and disk space. If the number of output | |records will be large, and if you are interested in only a small subset of | |items, it may be advantageous to extract a file first and then perform the | |totalling on that. | | | | Enter KEY on which to total: | | | |MSA = 5120 to 5120 | +------------------------------------------------------------------------------+ You will get the results you want only if the records to be totalled are consecutive. If not, you probably need to first create a new index (option 2 on the Manipulate Files menu--the indexing key is likely the same as your planned totalling key). How to Select Records with Conditional Clauses While record selection by way of the 2-level menus works well for most uses, those familiar with dBase may occasionally prefer to specify conditional clauses directly. This is possible by typing C when the first record selection menu is presented, whereupon the prompt "Enter conditional expression:" appears at the bottom of the screen. What you enter at this point becomes the argument for the dBase-style expression SET FILTER TO. Sample entries include -- SALES >= 10000 MSA = "5120" .and. RECTYPE = "04" RECTYPE = "02" .or. RTL87KB = "001" IIF(ESTAB>0,SALES/ESTAB,0) >= 10000 Note that when entering desired values for code fields stored as character variables, they must be enclosed in quotes. While the first two examples could be entered easily by way of the menu system, the last two examples illustrate conditions that can only be specified in this way. The Select Records menu system assumes that conditions set for two variables are additive, i.e., they would be connected by a Boolean ".and.". The third example shows the use of an ".or." condition, in this case selecting all state level records (RECTYPE = "02") but only those records at other levels that are for trade area totals (RTL87KB = "001"). The last example illustrates the selection of all records with more than $10,000,000 (10000) in sales per establishment. Note, however, that it was 32 necessary to imbed that SALES/ESTAB fraction within an "immediate if" (IIF) to make sure that the program does not attempt to divide by zero when it encounters a record where the number of establishments is zero (e.g., not available). One of the major pitfalls of directly entering conditional expressions is that an error in punctuation or logic may lead to abrupt program termination. Record selection by way of conditional expressions does not take advantage of indexes that may speed many of the code searches using the normal menu approach. You may be able to get around this problem by selecting records in two "passes". To select records in the Minneapolis-St. Paul MSA with at least a certain ratio of sales per establishment, select the MSA on the first pass through select records, then select records again, this time entering R to einstate the previous selection criteria before typing C to specify the onditional expression. Note that the program expects you to enter the conditional clause without the words FOR or WHILE that you might use in dBase. Displaying Secondary Files The Advanced Options menu option 2--"Display secondary file"--allows you to look at another data file without losing your place in the main file selected. This is a "no-frills" display, with no item descriptions from a data dictionary, no ability to alter column width, and no ability to select items or records. This screen does allow rudimentary editing of the data file, including record insertion and deletion, assuming the file is not on a CD-ROM. Following are a few examples of ways you may use this option. Checking a previously created extract file. In the middle of setting up a data extraction, you may wish to refer to a file you previously created, for example, to include the same statistics. Checking a code list. You can generally view the labels corresponding to particular codes by using the Add Labels function or by going through record selection. If either of those approaches would disrupt the way you have your main file set up, you may display the label file through this option. Deleting selected records. 1990 census STF CD-ROMs have multiple records for a particular area within certain summary levels, each defined by a dif- ferent GEOCOMP code. In order to get rid of the extras, the easiest way may be to create an extract file (DBF format), load it with this option, point to the second in each group of apparently duplicate records, then delete it by pressing U, and repeat as necessary. If you previously created any indexes for this file from the Manipulate Files menu, you will need to redo them if you have deleted or inserted records. Editing particular fields. You may change the data in a field, for example, to make your own annotation to an area name, by pressing to start the edit mode, typing over the existing contents, and pressing again to complete the edit. This can be tedious if more than a few records are involved. How to Get EXTRACT to Work with Other dBase Files While EXTRACT was primarily designed to work with files as issued by the Census Bureau, it is possible to add other dBase III-compatible files to your MY_FILES 33 catalog and to create data dictionaries for them, as a prelude to being able to merge these data with data you have derived from census files. From the Advanced Options menu, Option 5 confirms the name and location of your MY_FILES catalog file, then prompts you to dd to or eplace your existing catalog. Replacing wipes out any previous entries in the MY_FILES catalog. The next prompt is to "Enter full filename or mask", to which you should respond with a filename in the form "c:\dbase\mydata.dbf" or "c:\census\*.dbf". Both "?" and "*" can be used in DOS wildcard expressions. EXTRACT adds each file name that fits that directory mask to the catalog, and then displays the catalog entries to the screen. Cursor to the right until you see the "title" field, then enter whatever descriptive statement will be useful to you in the future. If you have accidentally added files that do not belong in the catalog, delete them with U. If the system cannot find a data dictionary for each file added to the catalog (it looks in the default directory and in the same directory as the data file for a data dictionary that may have been created previously), EXTRACT will create one, asking you to specify a directory location for the .DCT file. Unless you specify otherwise, it will put it in the same directory as the data. In order to create a data dictionary, the system will ask for the full name of an existing dictionary files that it can use as a pattern--i.e., a .DCT file that already exists in the \AUXIL subdirectory on the CD-ROM or in an auxiliary directory installed on your hard disk. If you specify a .DCT file with any field names that are the same as field names in new .DBF file, the program will copy their descriptions to the new dictionary, saving you the trouble. The program then presents you with the field names and spaces to fill in descriptions. You must press after you have edited any field in order to move on to the next field. After you enter all of the 60-character descrip- tions and press , the program will prompt you for 40- and 20-character descriptions, which you may skip if you wish. The final set of prompts is for entries to fields called LABELFILE, LABELNDX, LABELKEY, and ALTNAME, which, if applicable, the program will have already picked up from the pattern .dct(s). If the data file you are adding to the catalog is not one you are familiar with, you may find that you are not confident of the descriptive titles you are entering for files and variables. Rest assured that you can return to the Advanced Options menu at any time to further edit the titles in your MY_- FILES.CTG (Option 6) or in the data dictionaries you have created (Option 4). These procedures do not attempt to create label files for databases created outside of EXTRACT. If you want to do so in dBase III, adapt the structure of one of the label files on CD-ROM insofar as is possible. Miscellaneous Notes If you find that you are not able to move up or down or side to side on screen displays, check to make sure that your NumLock key has not been set on. The program can be aborted at any time by pressing C, and answering No to the question displayed at the top of the screen. Aside from , this may be the only way to regain control of the computer if the system is doing a search that is taking so long that you lose interest. 34 NOTES ON THE USE OF EXTRACT WITH SELECTED CENSUS BUREAU CD-ROMS 1987 Economic Census Discs, Volumes 1 and 2 Auxiliary files needed by EXTRACT are on each disc. Each successive disc within each volume supersedes the previous disc. For example, Disc 1d includes all files from Disc 1a, 1b, and 1c, plus many more; Disc 1d also corrects a few minor errors in Disc 1c. Supplementary files have been issued on the Census electronic bulletin board (BBS) (see Getting Assistance, below) that address errors on CD-ROMs 1d and 2b. Most data files contain no text labels, but there are state, county, and place names built into the "A3" and "A4" files for retail, wholesale, services, and transportation, and the "A1" files for manufactures. For other files, simply "add labels". Most data dictionaries and label files have definitions available that can be displayed from the main data display screen or during item or record selection. Some of the Manufactures Industry Series files have separate footnote files for footnotes that are specific to particular data cells. To display them in a manner that actually mimics footnotes, select the footnote files with the "Add labels" command, use the idth option in the Display or Preview mode to limit them to a single character in width, then press S to how the entire footnote when the one-character column is anything other than blank. Many of the auxiliary files on the Volume 1 discs are useful data files in their own right. For instance, the STCOUNTY.dbf shows not only county names and codes, but also population estimates for the last three economic census years-- 1987, 1982, and 1977. Record selection Many of the files in Volume 1 feature data for multiple levels of geography, e.g., state, metropolitan areas, counties and places. Selecting records for a particular county will bring up all records with that county code, which includes places within that county as well as the county total. Thus, to bring up only county total records for a particular county, select on both RECTYPE, checking off code "06" for counties, and COUNTY. To view data for all counties, select only on RECTYPE. Record selection by place code brings up a code list that includes counties as well as places, county records having a place code of "0000". Simply until you come to the place records, which will have nonzero codes. After specifying record selection, EXTRACT may prompt you to enter a value within one or more additional variables in order to speed the search. Most commonly the request is for a single value for record type, since many of the indexes which can speed the search involve record type. When selecting records for a particular county within the RC87A3, WC87A3, and SC87A3 files, you will be prompted to select single values within record type, state, and place--due to a quirk in the structure of the available index. Since you probably do not want to select only a single record type or place, press to when prompted to check off one of their values. 35 1987 Census of Agriculture--State and County Data Auxiliary files needed by EXTRACT are on this disc. On the other hand, the Agriculture Specialty Publications CD-ROM consists primarily of files in Lotus 1-2-3(tm) and flat ASCII formats, and is not accessible with EXTRACT. The "definition" function is not available on the Agriculture State/county disc, since narrative definitions were not built into the dictionary and label files. Note that the ZIP code data from the census of agriculture are available on Economic Census CD-ROM 2b, along with ZIP code data from the censuses of manufactures, retail trade, and service industries. Adding labels There are no text labels in the county files. To describe a particular data record in these files you need to identify both the county and the item number. EXTRACT gives you the capability to add up to two sets of labels at a time, e.g., both county labels and item labels, but that will leave you without enough space for data in the columnar display unless text columns are truncated with the idth function. Two sets of labels also slows down all data retrieval operations. The most common inquiries, though, look for a number of items for a given county or all counties for a given item. As long as one of the selection criteria has only one value, the description of that value will appear on screen displays and printouts as a second-level heading. Thus, you need only one set of labels to describe the data. Selecting records Because of the structure of the data files, selecting data items, like the number of acres of wheat harvested, is a matter of selecting records (not selecting "items", as with economic census files). This process is made more difficult because item codes in both the county files and the state files are unrelated to any coding scheme with which users may already be familiar (such as SIC codes or commodity codes). The best aid to finding the data items you want is to find them first in one of the 1987 Census of Agriculture geographic area series reports, then note their table number and relative position within the table. The first two digits of each item code in state and county files are the table number. (Otherwise the item codes in state and county files are unrelated.) Record selection in county files. For the county files, selecting records is aided by a menu of all items available. The ump function allows you to specify a particular table number, e.g., 18, whereupon the menu will jump to all items with a 5-digit code beginning with 18. From that point you browse through as many screens as are necessary to find, and select with X's, all of the items you want. Note that there is a maximum of 17 items that can be checked off at any one time. If you need more, you will need to revert to selection in terms of a range of codes. If you don't know the table number, the ord search and ocate options can come in handy. ord search prompts for any string of characters, and, after a few minutes, the system will present you with a list of all of the labels in which that string of characters appears. The system will find the desired 36 string of characters anywhere on a line, upper or lower case, but it may also pick up that string as part of another word. For example, searching for Apples will also bring up lines for Pineapples. Select with X each of the codes you want. Somewhat faster in operation is the ocate option, which will also accept a string of characters, but will look for them only at the beginning of a line. ocate is faster than ord search because ocate stops looking once it has found the first occurrence and displays the full list starting at that point. In any record selection which involves either the item number or the county code, the record selection process will automatically use that state's index file to find the first eligible record. That index is sequenced item code by county, so that, until that index is turned off, all displays will be sequenced item by county; for example, the count of farms would be shown for all counties, then land area in acres for all counties, and average acres per farm for all counties. If you want those three items together for each county, you can type "N" for natural sequence when the data are displayed to screen sorted by item, or you can turn off the index before going in to the data display screen, as discussed below. Selecting multiple items for multiple (or all) counties can take a great deal of time. The preferred way to query the file in EXTRACT is either to select a single item to display for multiple counties, or to select multiple items to display for a single county (turning off the index before displaying). If you want to display a number of counties for two or three items or a number of items for two or three counties, it is much faster to structure them as two or three separate queries, perhaps printing out the results for future reference, than to do it all at once. To turn off the item-by-county index prior to data display, select Manipulate Files (option 4) from the main menu, then Select an Existing Index (option 1) from the Manipulate Files menu, and finally press to escape from the index selection screen. Record selection in the state file. For the state file, selecting records is made more awkward because there is no separate reference list of data items that can be brought up for record selection. Thus, selecting based on the ITEM_ST code can be done only by specifying a range. To look at all codes corresponding to table 18, enter 18 as the minimum value and 19 as the maximum value. Once you have displayed the data to the screen and scrolled through the many records to find the data you want, make note of the applicable ITEM_ST codes for future reference. Another factor affecting the selection of data from the state file is the fact that each data item is defined by not one but two 60-character labels. In general, TEXT1 defines the row stub in the corresponding printed table while TEXT2 defines what would have been the column header. There are two ways to see both sets of labels at once. Switch to rowwise display, which will show only one record at a time but will give both full 60-character labels. Use the "W" option in the normal columnar display to specify a narrower column idth. Simply highlight any value in the column to be narrowed, type "W", and the system will prompt for the number of characters of the column to display. By truncating the text1 and text2 fields to fewer than 37 34 characters each, both can be displayed on the screen at the same time along with at least one data value. Note that if you really need to see the last part of a text field you have truncated, merely highlight the desired entry and press "S" for how. That will display the entire text across subsequent lines, until you move the cursor. U.S. Exports and Imports of Merchandise The CD-ROMs in these two monthly series do not include EXTRACT-compatible auxiliary files, which must instead be obtained in compressed form on Economic Census CD-ROMs 2B or 1E, on diskette from the Center for Electronic Data Analysis (see address under Getting Assistance, above). Two levels of installation are available, a compact installation occupying 130 kb on your hard disk, and a preferred installation, with somewhat more legible and useful commodity menus, requiring 3.4 megabytes more. While the master catalog includes both export and import data, no one CD-ROM has both, and you must insert the appropriate disc for the query you make. Each import and export database already includes text labels, or at least mnemonics, for the commodity, country, and district, as applicable. The "add labels" function is still useful, particularly in attaching alternate codes to each commodity. (SIC, SITC, End Use and Agriculture codes are available as labels.) Each disk contains summary files of moderate size, and an enormous detailed data set of 200 to 500 megabytes. Each database is indexed, but data retrievals from the largest data sets can be fairly time consuming. Record selection involving a single value (e.g., imports from Iraq or exports of a particular commodity) can be accomplished reasonably quickly. Record selection based on widely separated codes (e.g., imports from Venezuela (code 3070) and Nigeria (code 7530) can be extraordinarily time consuming (hours!), but if the requests are made separately, one code value at a time, they can be accomplished in a few minutes. County Business Patterns The 1986-87 and 1987-88 CD-ROMs do not include EXTRACT-compatible auxiliary files, and the 1988-89 CD-ROM is missing one critical auxiliary file. Fortunately, the 1988-89 CD includes auxiliary files in compressed form (\EXTRACT\CBPAUXIL.EXE) which, once installed on a hard disk (about 930 kb), can service all three of the CDs issued as of this writing. (A substitute master catalog for the 1988-89 disk can be obtained from the BBS which requires less hard disk space if earlier CDs are not being used.) Narrative definitions are available for key data items, but descriptive informa- tion for SIC categories are limited to the 56-character title. Note that the SICs recognized for the 1986 and 1987 CBP are on the 1972/77 basis. About 1/4 of all SICs were redefined for the 1987 Economic Censuses. The 1988 and 1989 CBP data are reported on the new basis, consistent with the 1987 Economic Censuses. 38 County and City Data Book, 1988 EXTRACT-compatible auxiliary files may be obtained in compressed form on Economic Census CD-ROMs 2b or 1e, on the Census Bureau electronic bulletin board, or on a diskette from the Center for Electronic Data Analysis. When installed on your hard disk, the auxiliary files require about 635 kilobytes. Each file in this data set includes data for all states, all counties or all cities nationwide, but only a limited number of items are included in each file. Thus, for example, age statistics might be in one file while race statistics are in another. This is quite unlike the structure of economic and agriculture census files which more often segment the data by state rather than by subject matter. Within the places file PLF01, the system cannot successfully select records based on county (the STCO variable), due to a quirk in index structure. It is, however, possible to select on state code. USA Counties, 1992 EXTRACT-compatible auxiliary files may be obtained in compressed form from the census Bureau electronic bulletin board, or on a diskette from the Center for Electronic Data Analysis. When installed on your hard disk, the auxiliary files require about 1.1 megabytes. Due to certain conventions unique to the USA Counties disc, EXTRACT 1.4a (November 1992) or later is required to work with this CD-ROM. Each file in the data set includes data for all states and counties nationwide. The 2080 statistics and their flags are segmented across 35 separate files, and you may wish to consult the master list of data items in the documentation or in the file CO_DDF.dbf (in the AUXIL catalog) to determine which items are in which files. The Add Labels function is an easy way to link into a single display items from two or three files. The COSTATPF.dbf data base consolidates 51 of the most frequently used items into a single source, and, in fact, is the only file that contains the 1990 census population count. The variables most often used for record selection are ST, to select the state total and all counties in a state; METRO, to select all counties in a metro area (there are not MSA summaries); and SUMLEV, to select all state totals. When selecting a specific county or group of counties, select on both STate and COUnty. Initially the county menu will start with Alabama counties, even if another state has been selected, but you may ump to the appropriate state if you recall the appropriate state code from the previous step. You may select up to 14 individual counties at this point, but they must all be in the same state. If you need counties from multiple states, select all counties within those states with the ST code. 1990 Census CD-ROMs: STFs 1A, 1B, 1C, 3A, 3B, and 3C; PL94-171; EEO Files Earlier versions of the auxiliary files that EXTRACT needs to work with STF 1A, STF 1B, and PL94-171 discs were included in compressed form on Economic Census CD-ROM 2b. Revised versions reducing hard disk requirements somewhat, as well as auxiliary files for STF 1C and STF 3A, may be downloaded from the Census bulletin board, or may be purchased on diskette from the Institute for Electronic Data Analysis at the University of Tennessee. One compressed file 39 STFAUX.EXE (309k) contains a set of auxiliary files needed for all of these CDs, supplemented by other files needed in addition to service particular 1990 census CDs [STFAUX1A.EXE (61k), STFAUX1B.EXE (11k), STFAUX1C.EXE (50k), STFAUX3A.EXE (225k), and PL94AUX3.EXE (12k) are available at this writing]. When uncompressed, auxiliary files for all of these CDs require about 3.1 megabytes of hard disk space. Auxiliary files unique to STF 3B, STF 3C, and the EEO files will be issued later in 1992. While EXTRACT 1.2 and 1.3 worked with the original auxiliary files for STF 1A, STF 1B, and PL94-171, EXTRACT 1.4 or later is required to deal with the new ver- sion of the auxiliary files designed to save hard disk space. (The new approach saves more than 1.1 megabytes in an installation including STFs 1A and 3A.) Comparison of EXTRACT and GO If you simply want to look up numbers, one area at a time, the GO software distributed on each of the 1990 census CD-ROMs is much easier and more efficient than EXTRACT. The program prompts you to pick a summary level, then a specific area, and finally a particular table. One of the tables is a "general profile" presenting 89 key items selected or derived from the 982 population and housing data fields carried in the 10 STF 1A databases. If you want to see the more detailed data, you may look at any one table at a time. That table, or the general profile, may be printed to your printer or to a file, one area at a time. Because this GO software was created specifically to work with the STF 1A discs, it insulates the user from much of the complexity of the STF 1A files--data stored in multiple files, with cryptic variable names, and with complex geo- graphic hierarchies. As such, GO is highly appropriate for novice users. EXTRACT, on the other hand, requires you to deal with more of the complexity of the data set, but gives you much more power and flexibility in working with the data, including the following capabilities: - To deal effectively with classes of geographic areas, such as all places within a county or all tracts with 1 or more person of Hispanic origin. - To work with data from more than one table at a time. - To view narrative concept definitions so you understand what you are working with. - To create "user-defined items", such as population per square mile, percent white, or a count of persons under 15. - To merge these data with data from other sources, such as the economic censuses. - To copy to a file whatever subset of data you select, so that you can import the data into other applications, such as Lotus 1-2-3(tm) or Harvard Graphics(tm). - To work these data using a tool you have learned how to use with other Census Bureau data sets. Dealing with the 10 STF 1 files and 34 STF 3 files Since there are over a thousand geographic, population and housing fields in STF 1, and no dBase file can contain more than 128 fields, STF 1A and STF 1C are structured as a series of ten parallel files for each State. Even larger, STF 3 files consist of 34 parallel files. When working with STF 1A, EXTRACT prompts 40 you to select one of the ten as your starting point in the "Select a Catalog" screen: File Matrixes Subjects covered STF1A0 P1 - P10 Geographic Identifiers, Sex, Race, Hispanic Origin STF1A1 P11 - P12(p2) Age, Total and White STF1A2 P12(p3-p5) Age, Black and American Indian, Eskimo, Aleut (male) STF1A3 P12(p6-p8) Age, American Indian .(female) and Asian or Pacific STF1A4 P12(p9)-P13(p1) Age, Other Race and Hispanic Origin (male) STF1A5 P13(p2) - P19 Age, Hispanic Origin (female), Household Rel. & Type STF1A6 P20 - P35 Household Relationship & Type (cont.), Imputations STF1A7 P36, H1 - H20 Housing: Vacancy, Tenure, Age of Hhr, Rooms, Persons STF1A8 H21 - H40 Persons Per Room, Value, Rent, Duration of Vacancy STF1A9 H41 - H55 Units in Structure, Housing Imputations Since this is rather limited as a subject locator, you will want to keep handy a copy of the subject locator (pages 3-1 to 3-6) or the table outlines (pages 5-1 to 5-11) in the printed STF 1 or STF 3 CD-ROM documentation, or to print out the corresponding ASCII text files SUB_LOC.ASC and TBL_OUT.ASC in the \DOCUMENT subdirectory of the CD-ROM. If you expect to return to the file selection menu several times during a particular session, it will be worthwhile to type R to estrict the session to data for a particular state before selecting the catalog. You can combine into your display data from one or two more of these subject areas using "Add Labels" discussed below. Displaying data to the screen From the main menu, which you reach after specifying drives and selecting the file you want, your first step is likely to be option 6--Display to screen. If you selected STF1A0 or STF300, you are confronted with columns of codes and no data. The data are there, but you must cursor to the right past more than 60 codes to find them. Thus, it is helpful to select only certain columns to display, which is accomplished through option 1--Select Items--at the main menu. Selecting items When using a STF1A0 file, the Select Items menu extends through 11 screens (move down with ). The first four screens are all codes, mostly in alphabetic, not logical or hierarchical order. Area name (ANPSADPI) appears on the fifth screen, 10 lines before the first of the population statistics. The first of the subject items, P0010001, is on the sixth screen. The ump feature allows you to enter "ANPSADPI" or "P001" to jump right to particular items if you know their mnemonics. After selecting items it is generally useful to type P to

review how the data will appear in columns across the screen. Initially, the area name (ANPSADPI) takes up 66 characters, leaving room for only one or two other items on the screen. Cursor to the right until you get to that column, then press W for idth, and specify a narrower column, e.g. 12 characters. Selecting records 41 SUMLEV or summary level is the most important of the geographic codes for record selection on all of the 1990 census CD-ROMs, since it identifies the type of geography. To select records for all counties, for example, select SUMLEV and the value "050--Counties" on the summary level menu--even though your first inclination might be to select based on CNTY, the county code. The CNTY variable comes into play in selecting data for a single county. To display all tracts or BNAs within a particular county, select on both SUMLEV and CNTY (put 2 S's on the first menu) and specify a SUMLEV of "140" for TRACT's and BNA's. Understanding summary levels and the sequencing of records is so important that you will want to keep handy the summary level sequence chart on page 6-1 of the STF 1 or STF 3 documentation (or print out the first part of \DOCUMENT\SUM_LEV.ASC on the CD-ROM). You also need to keep straight which codes are available on which summary levels. For example, if you want to analyze tracts/BNAs by place, you need to specify a SUMLEV of "080" instead of "140", because SUMLEV 140 tract/BNA records do not include place codes, while SUMLEV 080 tract/BNA records do not. Similarly, if you want to list places within a county, you need to work with SUMLEV 155, not SUMLEV 160, because the latter do not have county codes. To determine which codes are available on which summary levels, see STF 1 documentation pages 2-3 to 2-15 or print out \DOCUMENT\HOWTOUSE.ASC. The best variables for selecting records are highlighted on the first record selection screen with an asterisk (*)--these codes can be selected from a menu and also have indexes to speed your search. Of these, SUMLEV, CNTY (county), and PLACEFP (place) are easiest to use. COUSUBFP (county subdivision) and TRACTBNA (census tracts/BNAs) lists are sequenced by county, so it is advisable to select records in two stages--first, using only CNTY, and then again from the main menu with COUSUBFP or TRACTBNA. Any variable in the data set can be used as a selection variable, including population (e.g., all block groups with 1 or more Hispanic residents) or even latitude and longitude. Unfortunately, since the files are so large (from 8 to 286 megabytes per state), and it can take 5 minutes per megabyte for scanning through unindexed records on a CD-ROM, it is advisable to make such selections only within a particular county, place, or tract/BNA. Adding labels For STFs 1A and 3A, the area name field (ANPSADPI) appears only on the first file in each series (STF1A0 or STF300). When working with other files, you may add labels associated with the county, county subdivision, place, or the lowest- level name (ANPSADPI). To add ANPSADPI, use the Add Labels options at the main menu, and select "--STF1A0" or "--STF300" at the first Add Labels menu, and ANPSADPI at the second menu. (This is not an issue for STF 1B, STF 1C, or PL94- 171 CDs, where all files contain ANPSADPI.) The Add Labels feature can be used to link data items from other files on the same CD-ROM as well as area names. At the second Add Labels menu, you may type A to select from ll data items, not just labels, and M to select ultiple items from that screen. For example, you could select not only the area name from STF1A0, but also the total population (P0010001). Labels can be specified from up to two sources, allowing you to view data from as many as three files at once. Adding labels does, however, slow down data display. Displaying definitions 42 One of EXTRACT's unique features is its ability to display definitions of geographic and subject concepts. Typing D at the Select Items menu or in the columnar display screen will bring up a concept efinition keyed to the variable currently highlighted. You may also explore the definitions of related variables by typing I to bring up an ndex of all of the topics for which narrative discussions are available. A table locator for STF 1A and a 3-part table locator for STF 3 are also available from the this index. You also may access definitions from the main menu (type D ). Notes particular to P.L. 94-171 CD-ROMs Most data displays for subcounty areas should include the voting district identifier entitled "Special Area Code 3" (SAC3), since voting districts split many place, tract/BNA, and block group summaries, and govern their sequence in any case. When selecting records, the speediest access to data is provided by selecting on SUMLEV, PLACECE, CNTY, or CNTY and SAC3 together; and by specifying only one code at a time within the second-level record selection menus. Note that some codes are not available at all on the P.L. 94-171 files (noted in the menus) and others are available only within certain record types (see Figure 2 in the P.L. 94-171 documentation). GETTING ASSISTANCE EXTRACT Help Lines: (301) 457-4151 457-1210 This program was designed to promote the effective use of data on Census Bureau CD-ROMs. If, after reading this documentation, you have questions about the operation of the program, or have found what appears to you to be an error or "bug" in the program or documentation, you may call Paul Zeisset or Bob Marske of the Census Bureau's Economic Planning Staff at (301) 457-4151. Written correspondence may be directed to the Economic Planning Staff, Bureau of the Census, Washington, D.C. 20233. If you think you have encountered a program bug, first see if you can reproduce the problem. If you call the help line, you may need to be able to tell the person at the other end of the line the precise sequence of steps that brought you to the problem. If the program has terminated abruptly, i.e., "bombed", then EXTRACT leaves a 1-line message at the top of the screen that may be the key to determining the problem. Write the message down precisely (or send it to your printer with ). If you access EXTRACT through a menuing system that takes you back to the menu without giving you time to record any error message, try to run EXTRACT outside the menuing system (i.e., from a C> prompt) or have your menu designer insert a "pause" command after running EXTRACT. That way you can record any error message before calling for help. Among the most common problems that cause program termination is insufficient random access memory (usually a "run error"). EXTRACT requires about 450 kb of RAM, and network drivers or terminate-and-stay-resident programs may have to be removed while you are using EXTRACT. Another problem (usually indicated by a message citing an ".NDX" file) results from the use of EXTRACT under an extra memory manager like QEMM(tm). Type "SET CLIPPER=E000" before running EXTRACT if you encounter that problem. An error message starting "Proc ADDLABVALU" generally indicates that you have attempted to extract a file with a user- defined item not wide enough to accommodate the largest value. 43 It helps to be at your computer when you call the EXTRACT help line. Electronic Bulletin Board System (BBS). You may view the latest bulletins on EXTRACT use, read comments other users have made, and also leave your own questions, comments, or suggestions to other users on data retrieval strategies on the Census Bureau electronic bulletin board under the "CD-ROM" conference. The latest version of EXTRACT and auxiliary files for particular CD-ROMs are available for downloading without charge on the BBS. That board may be reached on (301) 457-2310 (computer). The first time you call, you are asked to complete an on-line registration form. (For voice assistance in logging into the bulletin board, call (301) 457-1242.) In addition to the above mentioned sources, EXTRACT and auxiliary files are included on both 1987 and 1992 Economic Census CD-ROMs. 44 INDEX TO EXTRACT DOCUMENTATION 1987 Economic Census CD-ROMs 35 Economic census CD-ROMs 35 1990 Census CD-ROMs 39 Editing an extracted data file 33 Aborting EXTRACT with C 34 your MY_FILES catalog 23 Adding labels 15 EEO files 39 1990 census CD-ROMs 42 Electronic bulletin board 43 Agriculture census CD 36 Error messages 43 Advanced Options 23 Run error 2 Advanced topics 24 Exports and Imports CD-ROMs 38 Agriculture census CD-ROMs 36 EXTRACT menu, setting up 25 Auxiliary files EXTRACT.PRM 24 defined 24 Extracting data to a file 21 for 1990 census STF CD-ROMs 39 File manipulation 26 Bulletin board for software updates 43 File selection menu first time through 5 Catalog files, description 24 second time through 23 Catalog selection 5 Footnote files Color, problems with display 8 in census of manufactures 35 Column width 20 Foreign trade CD-ROMs 38 Columnar display 18 Format Options 17 Selecting from Format Options 17 Comma-delimited extract files 21 GO software 39 Command-line parameters 25 Config.sys 2 Headings 17 Counting number of records selected Headings, 2nd level 14,17 ("Dry Run") 22 Help screen files 24 County Business Patterns CD-ROMs 38 Help screens 6 County and City Data Book CD-ROM 39 Help via electronic bulletin board 43 Cursor movement--to top or Help via telephone 43 bottom of file 19 Horizontal file merging 28 Data dictionary functions 24 Indexes Data dictionary, creating a new one 34 creating a new index 27 DBF extract files 21 selecting an existing index 27 Definition display Installation from data display screen 19 of EXTRACT 2 from main menu 7 of auxiliary files from Select Items 9 --see "____READ.ME" files from Select Records 12 Institute for Electronic Data on 1990 census CD-ROMs 42 Analysis, Univ. of Tennessee 43 Deleting records from an extract file 33 Jump to a particular... from your MY_FILES catalog 23 code--Select Records 12 Diskettes with EXTRACT software field name--Select Items 9 and auxiliary files 43 field name--Add Labels 16 Display to screen 18 Drive specification 4 Dry run to estimate file size 22 45 Label files Saving format options 23 functions 24 Saving parameters 4,25 looking without linking 33 SDF extract files 21 Labels 15 Secondary files display 33 from more than one file 16 Select Items 8 illustration 19 on 1990 census CD-ROMs 41 multiple labels from one file 16 Select Records 10 Locate a particular description on 1990 census CD-ROMs 41 Add Labels 16 on agriculture census CD 36 Select Items 9 reinstating previous criteria 14 Select Records 12 with conditional clauses 32 Lotus 1-2-3 1 Sequence of items in display 30 importing data into 1-2-3 21 Showing full contents of truncated column 19 Main menu 7 record selection criteria 8 Manipulate files 26 Sorting (creating a new index) 27 Margins--printed output 17 Speed of execution 18 Marking a record 19 Speed-up-retrieval prompt 13 Master catalog 4 STFs 1A, 1B, 1C, 3A, 3B, and 3C 39 functions 24 Storage requirements as needed for 1988-89 CBP CD-ROM 38 of extract files 22 Menus, setting up your own to SUMLEV 13,41 access different CD-ROMs 25 Merging files 28 Toggling between columnar and MicroSoft Extensions 2 rowwise display 20 MY_FILES catalog Totals accessing extracted files 23 across multiple records 31 adding to it 33 of items in the same record 9 editing 23 Tutorials 1 new entries 22 MY_FILES.cti catalog of indexes 28 USA Counties CD-ROM 39 User-defined items 9 PL94-171 39,42 Page length--printed output 17 Vertical file merging 30 Percents and similar computations 9 Previewing columnar display 9 Width of column 20 Printer settings in Preview mode 9 changing fonts with software 21 Word search margins, headings 17 in Add Labels 16 Printing 21 in Select Items 9 to a file 22 in Select Records 12 PRN extract files 21 Work directory creation 2 RAM requirements 2 as default location for Ranges, selecting a range of codes 12 extracted files 22 Ranking 27 Record selection 10 economic census CDs 35 reinstating previous criteria 14 showing criteria in effect 8 strategies 12 RECTYPE 13 Restricting session to files for a State 5 Rowwise display 20 selecting from Format Options 17 Run error 2,43 46