Skip to main content
U.S. flag

An official website of the United States government

Find the fish: using PROC SQL to build a relational database

January 1, 1995

Reliable estimates of abundance and survival, gained through mark-recapture studies, are necessary to better understand how to manage and restore lake trout populations in the Great Lakes. Working with a 24-year data set from a mark-recapture study conducted in Lake Superior, we attempted to disclose information on tag shedding by examining recaptures of double-tagged fish. The data set consisted of 64,288 observations on fish which had been marked with one or more tags; a subset of these fish had been marked with two tags at initial capture. Although DATA and PROC statements could be used to obtain some of the information we sought, these statements could not be used to extract a complete set of results from the double-tagging experiments. We therefore used SQL processing to create three tables representing the same information but in a fully normalized relational structure. In addition, we created indices to efficiently examine complex relationships among the individual capture records. This approach allowed us to obtain all the information necessary to estimate tag retention through subsequent modeling. We believe that our success with SQL was due in large part to its ability to simultaneosly scan the same table more than once and to permit consideration of other tables in sub-queries.

Publication Year 1995
Title Find the fish: using PROC SQL to build a relational database
Authors Mary C. Fabrizio, Scott N. Nelson
Publication Type Book Chapter
Publication Subtype Book Chapter
Index ID 81444
Record Source USGS Publications Warehouse
USGS Organization Great Lakes Science Center