topic Searching in which tables a column is used in SAS Enterprise Guide

Searching in which tables a column is used

jozumhannes — Fri, 17 Feb 2023 19:48:27 GMT

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?

Re: Searching in which tables a column is used

PaigeMiller — Fri, 17 Feb 2023 19:50:02 GMT

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.

Re: Searching in which tables a column is used

jozumhannes — Fri, 17 Feb 2023 19:58:32 GMT

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.

Re: Searching in which tables a column is used

PaigeMiller — Fri, 17 Feb 2023 20:01:30 GMT

@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.

Re: Searching in which tables a column is used

jozumhannes — Fri, 17 Feb 2023 20:12:44 GMT

I need the tables where the columns are.

Re: Searching in which tables a column is used

PaigeMiller — Fri, 17 Feb 2023 20:05:58 GMT

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

Re: Searching in which tables a column is used

jozumhannes — Fri, 17 Feb 2023 20:08:41 GMT

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

Re: Searching in which tables a column is used

ballardw — Fri, 17 Feb 2023 20:53:53 GMT

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.

Re: Searching in which tables a column is used

PaigeMiller — Fri, 17 Feb 2023 21:25:48 GMT

@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!

Re: Searching in which tables a column is used

Kurt_Bremser — Fri, 17 Feb 2023 22:45:21 GMT

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.

Re: Searching in which tables a column is used

Patrick — Sat, 18 Feb 2023 01:02:10 GMT

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.

Re: Searching in which tables a column is used

Tom — Sat, 18 Feb 2023 02:08:05 GMT

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;

Re: Searching in which tables a column is used

jozumhannes — Tue, 21 Feb 2023 09:26:22 GMT

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?

Re: Searching in which tables a column is used

Kurt_Bremser — Tue, 21 Feb 2023 09:55:00 GMT

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.

Re: Searching in which tables a column is used

jebjur — Wed, 22 Feb 2023 19:20:58 GMT

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;

Re: Searching in which tables a column is used

Reeza — Wed, 22 Feb 2023 19:27:35 GMT

Is it possibly this is an intermediate column that gets renamed/recoded later on and then dropped? Are all your intermediate tables stored in your jobs/processes?

Re: Searching in which tables a column is used

Tom — Wed, 22 Feb 2023 19:28:13 GMT

@jozumhannes wrote:

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?

Not sure about how DI studio works. If you want to check for variable names in datasets then you need access to the datasets. PROC CONTENTS works on either a single dataset or a single library of datasets. But the DICTIONARY.COLUMNS metadata table (which you also see as SASHELP.COLUMN view) has the variable names of all of the datasets in all of the currently active libraries. So as long as your SAS session has access to all of the libraries you wont to search you can use that.

proc sql;
create table want as
select libname,memname,name,type,length,format,informat,label
from dictionary.columns
where upcase(name)="MYVAR"
;
quit;

You will get 0 observations if no matches are found.

NOTE: A report or some data integration step might USE a variable, but a dataset just HAS variables.