Help using Base SAS procedures

Determine Source Table For Column

Reply
Contributor
Posts: 53

Determine Source Table For Column

I'm querying several tables from Oracle and am wondering if there is a procedure out there that will tell me which Oracle tables my extracted fields are coming from. When reading the source code in SAS I can obviously see the names of the Oracle tables that are being used to extract my data, but I am wondering if there is a way to determine this programmatically.

If you're wondering what all of this is for -- I'm trying to write a procedure that will essentially create a report inventory so I will be able to determine which Oracle tables/columns are being used on which reports.
Valued Guide
Posts: 2,175

Re: Determine Source Table For Column

most data bases have their own internal dictionary tables.
In SAS it is called dictionary.columns or sashelp.vcolumn for the metadata about columns; dictionary.members and dictionary.tables for metadata about tables and dictionary.indexes, dictionary..... many other things.
Although your SAS session (once connected to the data base with a libname statement) will be able to provide most of what you need in dictionary.columns, you may find a more useful collection of metadata (and faster) in your database's own system tables.

good luck
peterC
Regular Contributor
Posts: 171

Re: Determine Source Table For Column

If you do want to use the metadata in the Oracle system tables, as Peter suggested, you can view this data from within SAS by assigning a libname to the database with “SYS” as the schema, provided that your DBA has given you the appropriate permissions to view the system tables.
Frequent Contributor
Frequent Contributor
Posts: 76

Re: Determine Source Table For Column

Message was edited by: LAP Message was edited by: LAP
Ask a Question
Discussion stats
  • 3 replies
  • 288 views
  • 0 likes
  • 4 in conversation