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: Mary C. Fabrizio and Scott N. Nelson
Links
- The Publications Warehouse does not have links to digital versions of this publication at this time
- Download citation as: RIS | Dublin Core
Abstract
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.
Suggested Citation
Fabrizio, M.C., Nelson, S.N., 1995, Find the fish: using PROC SQL to build a relational database, chap. of Client/server computing with the SAS system: tips and techniques, p. 238-243.
| 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 |