Searching in which tables a column is used

[jozumhannes]

I have many tables and I am searching for a column. I have the name of the column but I dont know in which table it is. How can I find this table/ the tables?

[PaigeMiller]

Assuming you know the name of the SAS Library that the table is in (could be WORK library or could be some other library or database)

proc sql;
    create table want as select * from dictionary.columns where
        lowcase(name)="fredflintstone" and lowcase(libname)="libraryname";
quit;

Naturally, where it says fredflintstone, you put the name of the variable you are looking for. This creates a SAS data set named WANT with every occurrence of this variable in the library or database of interest. in the column called MEMNAME, that is the name of the data set or table that contains the variable.

[jozumhannes]

First of all, thank you for your fast reply. I tried this one but I could not get the tables. I just got other column names.

[PaigeMiller]

---

@jozumhannes wrote:
First of all, thank you for your fast reply. I tried this one but I could not get the tables. I just got other column names.

---

I don't understand what you mean. Please show me the LOG from your code (all of it, every single line) and the output.

[jozumhannes]

I need the tables where the columns are.

[PaigeMiller]

the variable name and the table name have to be in lower case, the way I programmed it

[jozumhannes]

I wrote it in lower case right now, but I am always getting the same output.
And can I use sasapptest as libname?

[ballardw]

You should show the log from running your code, copy the code and all notes, messages or warning from the log, open a text box on the forum with the </> icon above the message window and paste the text.

Since Library names are generally restricted to 8 characters I am suspicious of any library named sasapptest .

If the the libname is active in your current session you should be able to use it.

[PaigeMiller]

---

@jozumhannes wrote:
I wrote it in lower case right now, but I am always getting the same output.
And can I use sasapptest as libname?

---

It is never helpful to tell us it didn't work and then provide no other information. SHOW US THE LOG!

[Kurt_Bremser]

Library reference names are limited to 8 characters, so sasapptest is not valid. I suspect it is your server context.

If you do not want to restrict the search to a specific library, omit this particular part of the WHERE.

[Patrick]

Basically repeating the code already provided - but eventually in a form that's easier to use for you.

Just populate macro variable &column_to_search with the name of the variable you want to search.

Please note: This syntax will only search in tables that are assigned via a libref (libname statement).

%let column_to_search=age;
proc sql;
  select libname, memname, name
  from dictionary.columns
  where upcase(name)="%upcase(&column_to_search)"
  order by libname, memname
  ;
quit;

If above code doesn't return the result you're after then you need to SHOW us what you get and what you would like to get. 

[Tom]

So you want to see what datasets have a particular variable?

How many datasets do you have?  Where are they?  Do you have active libref's pointing to the directory where the datasets are stored?  Or perhaps have librefs pointing to the external databases that have "tables" you want to reference as if they were datasets?  If not then you will need to first get access to the datasets before you can check which variables they have.

Example:

Search for variables with the name BALANCE in the datasets in the physical directory named /myfiles .

libname mylib '/myfiles';
proc sql;
create table found as
  select * from dictionary.columns
  where libname='MYLIB'
     and upcase(name) = 'BALANCE'
;
quit;

[jozumhannes]

I found a column in DI Studio. But in the DI Studio, you can not see in which dataset this column is used. So, thats why, I want to find the dataset in which this column is used.

Just for my information: Is by variable meant column?

And @Tom: I tried your Code. I think you understood me. But this Code doesnt work, I am getting following in the Log:
*NOTE: Table WORK.FOUND created, with 0 rows and 18 columns.*

And another question would be: I have many libraries. I dont know which one to take. Is there a way in which SAS goes through all the libraries?

[Kurt_Bremser]

When searching for libraries and datasets in the DICTIONARY tables, their names must be all uppercase.

Variable (column) names can be mixed case, so it is always a good idea to use the UPCASE function when searching for a name where you can't be sure how it was written.

The DICTIONARY tables are pseudo-tables created dynamically every time you access them, by reading the header pages of the dataset files. If you can't make a restriction with regard to library or dataset name, SAS will read all datasets in all currently assigned libraries, which can (and will) take some time.

[jebjur]

Doing something similar to the following code should create a data set (WORK.LIST) with two variables, listing the data set containing the variable name you are searching for, and in what library the data set is located. You just need to substitute 'VARNAME' with the actual name of your variable.

proc sql;
create table list (keep= libname memname) as select *
from dictionary.columns where
upcase(name)="VARNAME";
quit;