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


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


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.

Additional publication details

Publication type:
Book chapter
Publication Subtype:
Book Chapter
Find the fish: using PROC SQL to build a relational database
Year Published:
SAS Institute
Publisher location:
Cary, NC
Contributing office(s):
Great Lakes Science Center
p. 238-243
Larger Work Type:
Larger Work Subtype:
Other Government Series
Larger Work Title:
Client/server computing with the SAS system: tips and techniques
First page:
Last page: