Expanding Use of Pivot tables in EXCEL for Chi2 analysis

Download YRBS2003.dat. This is a plain text file. Since this is almost 3.5 MB, you won't be able to put it on a floppy disk. The file can be zipped withWinzip to make it smaller if you want.

Videotutorials on this exercise.

YRBSS
CDC Youth Risk Behavior Surveillance  System (YRBSS) database http://www.cdc.gov/nccdphp/dash/yrbs/
CDC Home  http://www.cdc.gov
Import the YRBSS data into EXCEL change file type to allfiles so you can see the yrbs2001.dat.
When you double click on the yrbs2001.dat file, the import wizard will open. Parse the data as fixed width.
Refer to the video and the screencaptures for guidance on where to put the breaks.
All of the datafields are one character except for fields:
Positions 113-215 contain derived data that can safely be left out, because it can be regenerated from the primary data later if you want.
Saving the database for later exploration:
Save the file as a file type EXCEL the extension .xls will automatically be added. It's not enough to just put the extension. xls on it. Note the size is about 9.5 MB.
You can save this file to your network drive, flash memory or to someplace on the C: drive where you can compress it with  Winzip to fit on the network drive or on a floppy disk.

Using Winzip (see the video on this):
You will get an error message if you try to zip an open EXCEL file, thus close the file in EXCEL before zipping it..
In windows explorer, right click the file, go to winzip and add the file to an archive --it offers the option of a zip file with the same name. This is good, but be careful not to do this twice.
Winzip will create the zip file which is about a megabyte in size. You can then use Windows Explorer to drag the file to other places.
You can save this on a floppy disk by using Explorer to copy it. Don't try to put it on a floppy disk by dragging it out of Winzip. Dragging out of Winzip to a disk location is how you extract the original file, and dragging out of Winzip to A: would result in attempting to write an 8MB file to the 1.44MB floppy disk. This is called a "blivet". It doesn't work.

Regenerating the database:
Later, if you want to study other relationships in these data, you can regeenerate the database from the zip file.  Open Windows Explorer to show the zip file. right click it to get the menu including Winzip Options or double-left click the file, and the Winzip Window will open showing the contents of the archive. Winzip will reveal data about the archive.
drag the file back out of Winzip to the location where you want to put the extracted database file. Extract the file over to a hard drive so there is more room.
You can then double click the .xls file to open it in  EXCEL and conduct new crosstabs.



Other things you might want to look at:
You can "reuse" your pivot table by changing the fields that are used in it.

This is easy to do as you can simply click on the pivot table to get the field list. Drag fields out of the table to remove, or into the table to add.  Explore other crosstab queries that might be interesting. Right click on the pivottable to find a menu of other options such as the field settings to change the field summary functions:

 
 

You can also get at all this by either Rightclick/Wizard/ layout or clicking Wizard/layout on the pivot toolbar.

Other summary Functions:
You can also do simple queries to find averages for one characteristic as a function of another. For example, you might want to find average weight as a function of smoking. To change the summary function in the pivottable data area, right click and choose field settings or use the pivottable tool bar. If necessary go to View/toolbars/pivot and click pivot tool bar.

last modified March 3, 2005Return to Bud's home page at U M St Louis
comments or questions to rbanis (at) jinx.umsl.edu