topic Re: Determine Source Table For Column in SAS Procedures

Determine Source Table For Column

mdavidson — Wed, 05 Jan 2011 15:10:28 GMT

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.

Re: Determine Source Table For Column

Peter_C — Wed, 05 Jan 2011 18:24:46 GMT

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

Re: Determine Source Table For Column

polingjw — Wed, 05 Jan 2011 20:49:01 GMT

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.

Re: Determine Source Table For Column

LAP — Thu, 06 Jan 2011 17:08:49 GMT

Message was edited by: LAP Message was edited by: LAP