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

Also published in: Proceedings of the Nineteenth Annual SAS users Group International ConferenceOut-of-print
By:  and 


  • The Publications Warehouse does not have links to digital versions of this publication at this time
  • Download citation as: RIS | Dublin Core


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 type Book chapter
Publication Subtype Book Chapter
Title Find the fish: using PROC SQL to build a relational database
ISBN 1555442188
Year Published 1995
Language English
Publisher SAS Institute
Publisher location Cary, NC
Contributing office(s) Great Lakes Science Center
Description p. 238-243
Larger Work Type Book
Larger Work Subtype Other Government Series
Larger Work Title Client/server computing with the SAS system: tips and techniques
First page 238
Last page 243
Google Analytic Metrics Metrics page
Additional publication details