Using Extract and Excel: An Example Population and Households by Block Group for Adams and Champaign Counties: I. This will be our finished product; an Excel table that the user can do all kinds of statistical things to, such as run a regression, etc. II. Steps taken to create the above table 1. From Workstation One, choose "Extract menu" from the Netscape homepage (or open the "Extract" group in Windows). Clicked on "Series 3A." As prompted on the screen, insert the chosen disc- in this case the 1990 Census Adams-Cook County 3A disc (CDROM.C3.282/2:3A-16)- in drive J: press a key to continue. 2. As prompted, press another key to continue. When prompted to Choose a Catalog, choose the second catalog listed- P01-P13 . This is the one that contains population and households. As prompted, press a key again. Then, when prompted to choose a state, choose Illinois. 3. At main menu, choose Select Items. Put an X by Census Tract/Block Numbering Area, Block Group, P 0030001 (Persons 100% Count) and P0050001, Households, Total. As can be seen, these choices account for 4 of the 5 columns in the finished table above; the exception is the county name, which was added later (if I had chosen County during this Select Items step, I would have been given only the county codes). Hit to accept these choices. 4. Chose Select Records. This chooses what rows I have. If I choose none, I would have data for every piece of every level of geography on the disc. I want, however, only data for block groups in Adams and Champaign Counties. So, put an S by County (what I want to limit by); hit to accept. Then you'll be prompted for counties: put an X by Adams and Champaign; hit to accept. Then asked for what level of geography I want within those counties. Put an X by 150, State-County-Census Tract/BNA- Block Groups; hit to accept. 5. Choose Display to Screen. Note that it would be helpful to have county names displayed for each entry. Hit to return to menu. 6. Choose Add Labels. Chose County. Chose Text. 7. Choose Display to Screen. It looks good. Hit to return to main menu. 8. Choose Extract Data to a file. Choose 2 -prn- for import to most other software (on the library file server we have three programs- Lotus 1-2-3, Excel, and dbase- which we can put this data into to manipulate. I barely know dbase [although it can be used directly with Census discs, bypassing Extract altogether]; 1-2-3 and Excel are almost the same, and since Excel is what Econ 173 uses for the modeling project- Excel can do regressions, it's the best program to be familiar with putting data from Extract into). When asked for file location put a floppy in A: and type a:\eric to call the file eric and put it on the floppy. When asked if you want headings say yes- this puts the header and footer definitions as seen above on the table (if you say no you will be asked if you want to create a separate format file. Saying yes creates a small separate file with the heading information in it. The advantage to putting the headings in a separate file is that it makes the data file you make a little cleaner and a little easier to use with Excel since it contains nothing but data). 9. Hit Q for quit. Take floppy to Workstation 3. Log in to the library file server. Open the "Microsoft Office (network)" box, then open Microsoft Excel. 10. From the File menu choose "Open." In the fdialog box, change Drives to a:. Change List Files of type from Microsoft Excel files to All Files; chose eric.prn; click on OK. 11. A box called Text Import Wizard appears. This converts eric.prn- which is just a text file with commas between each field- into a spreadsheet/ Excel file. At first box choose Delimited, then click on Next. In the next box choose Comma. Then click on Finish. Shazam, the result is the file seen above (I did a little minor cleaning up- adjusting width of boxes, cutting out most of the file in order to make the example table above). 12. Chose "Save As" from "File" menu and save as a Microsoft Excel Workbook (by changing the Save file of type); eric.xls. Eric Forte University of Illinois at Urbana-Champaign 5/96 Our Friends the CD-ROMs: Extract and Excel