topic How to find table name based on column in SAS DI or in SAS SMC in Administration and Deployment

How to find table name based on column in SAS DI or in SAS SMC

sathya66 — Wed, 24 Jan 2024 15:32:22 GMT

Hi All,

I know only the column name(PART_NUMBER) but not the table name. I have searched for the column name in SAS DI and I found it . when I try to see the properties but it is not let me see it. How can I find the table name based on column in SAS DI/SAS SMC.

Re: How to find table name based on column in SAS DI or in SAS SMC

SASKiwi — Wed, 24 Jan 2024 20:26:44 GMT

You can use the Dictionary Tables for this:

proc sql;
  create table want as
  select *
  from dictionary.columns
  where libname = 'SASHELP'
  and upcase(name) = 'AGE';
quit;

Re: How to find table name based on column in SAS DI or in SAS SMC

sathya66 — Tue, 30 Jan 2024 12:53:12 GMT

Thanks, I tried this way already but I am looking at GUI.

Re: How to find table name based on column in SAS DI or in SAS SMC

carl_sommer — Tue, 30 Jan 2024 17:33:00 GMT

Querying the DICTIONARY.COLUMNS table will only help find what has been physically allocated to your SAS session via libname assigments.

To query metadata, you can do this in SAS Managment Console through the following steps:

login to SAS Management Console as an identity that will be able to read the metadata you are looking for.
From the tabbed view in the left pane, select Search
Specify the column name and the operator (in my case I chose MAKE, with the Contains operator; note that the name is case-insensitive)
Clear all of the selected types, and then select Column
Select the Search button at the bottom of the pane

In the results in right pane, select one of the matches
Right mouse on this selected column and choose Properties
Click on the Authorization tab
Select the Advanced button
This will display the (inverted) hierarchy of the column (i.e., the table and the folder hierarchy)
Note that the library is not displayed

Carl Sommer - SAS Technical Support