Technical Documentation ßßßßßßß ßß ßß ßßßßßß ßßßßßß ßßß ßßßßß ßßßßßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßßßßßß ßßß ßß ßßßßßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßßßßßßß ßß ßß ßß ßßßßßßß ßß ßß ßß ßß ßß ßß ßß ßßßßß ßß a public-domain data extraction utility designed to work with Census Bureau files in dBase III+ format 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 Getting Started. . . . . . . . . . . 1 Stepping Through the Program . . . . 4 Specify drives on your machine. . 4 Choose a catalog. . . . . . . . . 5 Main help screen. . . . . . . . . 6 Select a database . . . . . . . . 6 Main menu . . . . . . . . . . . . 7 1) Select Items . . . . . . . 8 2) Select Records . . . . . 10 3) Add Labels . . . . . . . 15 4) Manipulate Files . . . . 16 5) Format Options . . . . . 17 6) Display to Screen. . . . 18 7) Print. . . . . . . . . . 21 8) Extract Data to a File . 21 9) Return to File Selection Menu . . . . . . . . . 23 10) Advanced Options . . . . 23 Advanced Topics. . . . . . . . . . 24 Auxiliary files and functions . 24 Setting up an EXTRACT menu. . . 25 File manipulation . . . . . . . 26 How to select records with conditional clauses . . . . . 32 Displaying secondary files. . . 33 How to get EXTRACT to work with other dBase files . . . . . . 33 Notes on Use of EXTRACT with-- Economic Census . . . . . . . . 35 Census of Agriculture . . . . . 36 U.S. Exports and Imports. . . . 38 County Business Patterns. . . . 38 County and City Data Book, 1988 39 USA Counties, 1992. . . . . . . 39 1990 Census: STF 1A, 3A, etc. . 39 Getting Assistance . . . . . . . . 43 INDEX . . . . . . . . . . . . . . 44 March, 1994 ßßßßßßß ßß ßß ßßßßßß ßßßßßß ßßß ßßßßß ßßßßßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßßßßßß ßßß ßß ßßßßßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßß ßßßßßßß ßß ßß ßß ßßßßßßß ßß ßß ßß ßß ßß ßß ßß ßßßßß ßß =>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 and EXTUTOR5 work ³ ³ through the use of 1990 census CD-ROMs, focussing on STF 1A and EEO CD-ROMs. ³ ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ =>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. EXTRACT requires up to 520 kb of system memory (RAM) under DOS 5 or 6. A 640-kb machine should be fine as long as memory-resident programs and CD-ROM or network drivers do not substantially reduce available memory. 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 a 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:EXT15A (or whatever version is present in a file EXT*.EXE) EXT15A is a program that creates uncompressed copies of EXTRACT.EXE (version 1.5a), 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 are using a CD-ROM that does not contain EXTRACT-compatible auxiliary files (e.g., 1988 County and City Data Book, 1990 census discs) you must install files from a separate Auxiliary Files diskette. Put the disk in the A: drive, display a directory (DIR A:) and type out any file with a title including "READ.ME", for example "STF_READ.ME". That file will contain instructions on loading the files into appropriate directories on your hard disk. If you obtained the EXTRACT software on a CD-ROM, the program INSTALL.EXE will create the directory \EXTRACT on your hard disk and will copy EXTRACT and related files into it or subdirectories within it. To start INSTALL, make the CD-ROM your default drive and type INSTALL (or \EXTRACT\INSTALL on some CDs). After copying the files required by all EXTRACT users, INSTALL will allow you to install onto your hard disk the auxiliary files needed for other CD-ROMs, and will create EXMENU.BAT to simiplify running the program in the future. 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. As noted above, EXTRACT requires up to 520 kilobytes of random access memory (RAM) under DOS 5 or 6. If the program aborts with a "run error", you do not have enough memory available to complete the requested task. To determine the amount of RAM available, run the DOS 'MEM' program. If MEM tells you that the largest executable program is less than 520 kb, you may need to remove or load into high memory terminate-and-stay-resident programs (TSRs) or network drivers. 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 EXT15A 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. =>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 as outlined below, or you started the program with EXMENU, 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, and prompts you with 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 under "Advanced Options" below.) 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 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". => 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. =>...(To continue, skip back one menu level and select "Main menu".)